There is NO perfect solution. Drive failure has gone down significantly, but it still happens, and if it happens to you, all the stats in the world mean nothing.
In my home computer experience, I have had TWO complete drive failures. Lightning hit twice. It is the old saying, "not IF, but WHEN."
Rotating hard drive:
A rotating hard drive that sits for YEARS unused may not spin up when plugged in years from now.
SSD
No motor to "freeze".
As I understand, similar to magnetic media, the charge that sets the 1/0 will degrade over time.
Technology changes. So you MUST keep up with the drive interface, or your backup drive may not be able to be easily connected to your new computer.
The generally accepted backup method is to have MULTIPLE backups, and keep them in a different physical location than your office.
- If you only have one backup drive, and it fails, you have no backup.
- Two backups in your house is useless, if you have a fire and both backups are destroyed with the computer.
Backup site
- The several major fires and disasters also show that you need significant physical separation between your home/office and your backup site.
- When a neighborhood burns, so does your neighbors house where you may have kept a backup.
- A technique that I used, was moving backups.
- Backup day 1, backups were stored in another company building a couple blocks away (a fire in my building would not take out the other building). This gave us quick access to last nights backup, if we needed it.
- Backup day 2, backups were sent off-site with a commercial backup storage company (this would cover a regional disaster).
- If you live in a flood plain, your backup site should NOT be in the flood plain where your house it.
When you backup to the cloud, you are putting your faith that the company won't go out of business. If the cloud backup company goes under, all your backup files will be SoL. So if you do cloud backup, you should also do a physical backup.
One method is daily cloud backup + monthly physical backup.
Note: If you have a photo business, your backup is CRITICAL to your business. So you need to put more attention to backups and backup strategy.
One thing that you did not mention, but is implicit in your title question "long term photo storage" is the software.
Over time, the new editing programs may/will not be able to read the OLD data files, especially camera specific file formats. So you also need to store a backup copy of the editing programs that you use.