Hi, > LTO is pretty much the only sensible choice these days as I understand
it.
That's really the case, for bulk storage of any type you need to be able to tier a lot of it offsite/offline. I'm responsible for a tape library with a robot arm and about 13 drives raging from LTO7 through to LTO9.
I've looked into it for backup but you need to store a LOT of data for it to make sense. The issue is that the drives are just super-expensive. You can get much older generation drives used for reasonable prices, but then the tapes have a very low capacity but they aren't that cheap, so your cost per TB is pretty high, and of course you have the inconvenience of long backup times and lots of tape changes.
The 7s are on their way out atm, so I'd expect to start seeing more pop up for sale secondhand. If you're doing tape though, 3:2:1 still applies, and you also (ideally) want two different manufacturer drives writing to two different manufacturer tapes to mitigate against issues like 'oh I got a firmware update and my tape drive started writing garbage'.
The newer generation drives are very reasonable in terms of cost per TB, but the drives themselves cost thousands of dollars. Unless you're archiving hundreds of TB it is cheaper to just buy lots of USB3 hard drives at $15/TB, and then you get the random IO performance as a bonus.
... but you have to swap out hundreds of USB drives which, especially on the cheap side, are likely to be significantly less robust than tape carts over time.
The main downside to HDD at smaller scales is that the drives themselves are more fragile, but that is mostly if you're dropping them - in terms of storage conditions tape needs better care than many appreciate for it to remain reliable.
It's really all downsides at the home scale. Your best bet is often ensuring that you have an offsite (s3 + restic can be decent) backup of your essential data and otherwise assuming that everything else could be lost at any time and just not caring about it. Anecdotally rsync (or s3...) to a cheap Hetzner VPS can be cost effective for smaller datasets / backups. My 100T server at home though? If it dies I lose everything non-critical on it! Cheers, Matt