Backing up the SQL server 2000 database using tape drives

Dear sir/madam,

I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.

Thanking you

Regards

Lavanya Janardhanan

There are two things that you can try here:

The first is to create a logical backup device for your tape device.

From Enterprise Manager, open the Query Analyzer and use the following command:

USE master EXEC sp_addumpdevice 'tape', 'tapedump1', '\\.\tape0' 

Where tapedump1 is the logical name for your tape drive, and \\.\tape0 is the physical path to your tape drive. You should use values for these parameters as appropriate to your configuration. You should then be able to use the logical device as a destination for your backups.

Alternatively, you can specify the tape path explicitly in TSQL, again in Query Analyzer:

BACKUP DATABASE MyDatabase TO TAPE = '\\.\Tape0' WITH FORMAT, NAME = 'My Backup Name';

This technique can help to determine where the issue is with recognizing your tape device.

Kevin Farlee