Backup software is written specifically for its purpose. As mentioned, you don't see a drive letter, it's a special class of device that must be written in a linear fashion. This requires all I/O (read/write ops) to be handled in that manner. For cheaper software, look at Backup Exec, CommVault, etc.. I've never used Amanda or Backula, but look into that as a free option if you're just repurposing old hardware.
The software will build a database of tapes it has seen (usually by scanning the barcode and/or reading RFID, but if there's no automatic ID hardware, it'll just create sequential IDs and it's up to you to label the media appropriately) and track what is on them, when it was backed up, etc. This lets you browse your history of backed up files and create restore jobs without having to load every tape and scan its contents. (Reading the TOC is not a fast operation....)
With a single tape drive, the software will fill the tape, eject it, then ask for the next one. You proceed in this manner until the job is complete, or you cancel it. With an autoloader, it will examine the database for tapes that have "expired" (past their required data retention time, based on a policy you create) and overwrite them as necessary. You can essentially remain ignorant of what's on any particular tape. The software will flag media errors and let you offline that tape, import new ones, etc., and carry on tracking where your volume sets (backup jobs) are at any given time. You can use the media browser to export tapes if you want to keep a volume set off-site or whatever.
Completely different paradigm to typical media, very much optimized for the task. Beware, it can be a fragile ecosystem. If you shuffle things around without keeping the software in the loop, it will get very angry with you.