I’ve used both DAT (36GB native) and DLT (160GB native) at work with a server and had a few occasions where I had to restore files.
Based on my experience with these, the reliability of the tapes seem pretty good. We had one set of tapes that were typically overwritten once a week and another set that were overwritten every 5 weeks. When the backup ran, it also did a verification process. If this was successful, it would eject the tape, otherwise it threw an error on the screen. While there were occasional verification errors (e.g. files modified between the backup and verification stages), I very rarely saw any CRC read errors.
One of the main reasons tape is considered more reliable than other media is down to what happens when damage occurs. If a section of the tape becomes unreadable, there is a good chance that the rest of the tape is fine. In theory the same is true if a section of tape is physically damaged. With a hard disk, a head/motor failure or PCB failure in usually results in it totally failing, often without advanced warning. For optical media, a separation of the layers (e.g. with DVD), the reflective layer corroding and/or degradation of the recording layer can result in a total failure of the disc. So in theory, tape is less prone to instant or quick failure.
With how much storage media has come down in price in recent years, I don’t think it’s worth investing in tape storage unless you have 10’s of terabytes of data to store. Basically I would suggest getting a hard disk dock, a set of bare 2TB 3.5" hard disks (with high user review counts and low feedback about failures) and a hard disk storage case. Then store your data on at least two separate hard disks. Besides the cost of the tape drive, you’ll also need to factor in the cost of a SCSI card (if a SCSI drive) and the recording software, which both together could cost as much as the actual tape drive.
Personally I’ve stopped using optical media as my main backup as I’ve had plenty of discs fail over time, yet haven’t had a 3.5" hard disk fail since 2005 (if I recall right) despite having 7 to 10 in use around the house over that period. A server at work has 4 original 80GB 7200RPM hard disks that have been running around the clock since 2004.
Another suggestion would be to look at M-Disc, which uses a durable solid recording layer instead of an organic dye that degrades. These discs to cost more and do require a special optical drive to burn them, so may be quite expensive if you have a lot of data to backup. However, in theory they do have the advantage of not needing to periodically verify them.