It is a question of failure mode. If your hard drive motor dies, if the heads fail, if the electronics fail, if the sealing fail (in some cases it can be repaired by specialists but that's not easy and much more expensive than a tape reader)... lots of things can go wrong that are not related with the media itself. The advantage of tape is that the device that reads it is separate from the mechanically simple tape. It comes at a cost but that's for hedging against a different risk.

LTO-9 drives are expensive, but the media is around $90 for an 18TB (pre-compression) tape. That’s a pretty good price if you make lots of backups for work. They also offer append-only mode, which is awesome for archives where you want it to be essentially impossible to delete a backup without physical access.

But the tape itself has a great reputation for being physically robust. There are fewer parts to break than in an HDD, too. If your tape drive dies, you can replace it and keep using the same media. That’d be like having hard drive platters you could swap into another HDD later on.

I believe it is cost per unit density that allows magnetic tape to be more suitable for archival storage than HDD, which surpasses it in I/O speeds. Magnetic tape storage is still usually in RAID-like configurations and while tape only survives for 15-30 years, data survival by migration is typically done well before medium degradation.