The trick with burning optical media is the disks themselves can physically fail with time. I have a huge archive of various burned media from the early 00's and a number of them have developed literal holes in the material over the years. If these holes hit data tracks, the files on those tracks are lost. If you're burning to optical media, you should probably be checking them regularly for degradation.
In my experience hard drives, USB sticks fail and regular hard drives fail.
It has been many years since I have had any involvement but backup tapes
probably have issues as well, but the rapid production of new tapes
and new formats is an issue already
I dont have any data to evaluate the best choice is
SSD drives?
No matter what technology is picked, at some point to preserve the
data it needs to be migrated to whatever comes down the line.
Honestly, so far if you can afford the up-front investment for the space you need, and the ongoing power costs, a NAS with a RAID array (or similar redundancy scheme) that can tolerate more than one drive failure at a time is probably the best long term archival storage. Spinning rust disks in my experience rarely completely fail without warning so you can usually catch and replace failing media before data loss occurs. Additionally if you don't, I've found that recovering data from failed HDD is also usually "easier" and "cheaper" for most values of both compared to other media storage (admittedly with no experience with recovering tape media)
