I can't use a tape-drive with SQL Server, but it works ok with NT Backup. Why?

A. SQL 6.5 and below have compatibility problems with certain tape drives - usually because the tape driver advertises a block-size that is too big for SQL to handle. With SQL 7.0 this problem goes away because SQL uses the NT backup code/format and so any tape drive that works with NT backup *should* work with SQL 7.0 and above.

However, SQL Server will only work with drivers that support the "TAPE_DRIVE_REVERSE_POSITION" scsi command. It uses this to skip back a filemark when handling end of media or malformed media errors. NT Backup doesn't necessarily use this, so it is possible for a driver/drive that doesn't support this to work with NT backup, but not with SQL Server. This is the case with some QIC style (IDE attached) tape units. In this case you will need to find a driver that does support this command, or get a different tape unit.

You can diagnose the problem on SQL 6.5 and earlier using a tapetest.exe tool - these are available from Microsoft PSS and have also been uploaded to www.swynk.com/sql in their library area. They are gui based and their usage is obvious.

You can try upgrading the firmware on your tape unit - this sometimes helps. But usually it is the driver that needs changing. 

Check the vendors website - e.g. www.quantum.com has updated DLT drivers

Try applying the latest NT service pack (many drivers come with these). Specifically there is a bug with SQL 6.5 SP5a where it needs to driver from NT 4.0 SP4 or above for 4mm dat drives to work properly (otherwise it gets an error 87).

If you have licenses for a 3rd party backup utility that dumps in Microsoft standard format then try a driver from here instead of the NT supplied one. I find that BEI Ultrabac drivers work well with SQL Server (www.ultrabac.com). Earlier versions of Arcada/Seagate/Veritas Backup Exec didn't, but newer ones do.

The last one leads to another work-around - if you use a 3rd party backup tool and it has a SQL agent then the tape unit will definitely work, as SQL is writing to the named-pipes or VDI interface and it is the 3rd party backup software/drivers that do the physical writing to the tape device.