Error when trying to backup to tape

[bigdaver]

Here's my error i get in the event viewer:

Event ID: 17055

18210 :
BackupTapeFile::ConfigureTape: SetTapeParameters (SET_TAPE_DRIVE_INFORMATION) failure on backup device '\\.\Tape0'. Operating system error 1(Incorrect function.).

I'm pulling out my hair trying to get a backup going. Any help would be much appreciated.
Thanx
Dave

 

[SQLBill]

Is the tape drive on the same machine as SQL Server? If not, does the MSSQLServer and SQLServerAgent services run as Domain Accounts? Do those accounts have access to the tape drive machine?

What command(s) are you running to do the backup?

-SQLBill

 

[bigdaver]

The tape drive is on the same computer as the MSSQL Server. I used the backup wizard to get the backup to come on automatically. After the wizard finished it said SQLServerAgent had to be on. I turned it from Automatic to Started. Do I have to be logged in as a backup user? Because I'm logged in as administrator.
Thanx

 

[SQLBill]

Admin should be good enough. AGAIN, what commands are you using to run the backup?

-SQLBill

 

[bigdaver]

I am not using any commands. I used the backup wizard in enterprise manager to make some scheduled tape backups. I set it up so it would do it all automatically (or so I thought lol).
Thanx

 

[SQLBill]

Check the Windows Event Viewer Logs and see if there are any related messages. Since this is an operating system error, you might see additional messages in those logs.

-SQLBill

 

[bigdaver]

Does this mean my backup worked?
18270 :
Database differential changes backed up: Database: mydatabasename, creation date(time): 2004/01/15(18:13:09), pages dumped: 13383, first LSN: 1994:17655:8, last LSN: 1994:17658:1, full backup LSN: 1993:6894:2, number of dump devices: 1, device information: (FILE=1, TYPE=TAPE, MEDIANAME='Diff 1': {'\\.\Tape0'}).

And if it worked, I selected for the tape to auto eject when finished and it didn't...

 

[bigdaver]

Oh yeah, there were these msgs too:

18225 :
Tape 'Diff 1' (Family ID: 0xd97276af, sequence 1) mounted on tape drive '\\.\Tape0'.


18274 :
Tape 'Diff 1' (Family ID: 0xd97276af, sequence 1) dismounted from tape drive '\\.\Tape0'.

Thanx for the help by the way

 

[JayKusch]

Yes ... it appears your differential backup completed successfully as the "backed up" message indicates. They other 2 messages that you posted are telling you that the tape was mounted and then dismounted as expected. I bet the order of the messages look something like this:

18225 :
Tape 'Diff 1' (Family ID: 0xd97276af, sequence 1) mounted on tape drive '\\.\Tape0'.

18270 :
Database differential changes backed up: Database: mydatabasename, creation date(time): 2004/01/15(18:13:09), pages dumped: 13383, first LSN: 1994:17655:8, last LSN: 1994:17658:1, full backup LSN: 1993:6894:2, number of dump devices: 1, device information: (FILE=1, TYPE=TAPE, MEDIANAME='Diff 1': {'\\.\Tape0'}).

18274 :
Tape 'Diff 1' (Family ID: 0xd97276af, sequence 1) dismounted from tape drive '\\.\Tape0'.

I would ADVISE your next step be Restoring the backups to make sure they are working as expected. There is nothing worse than having a serious DB issue, like a DB going into a SUSPECT or Non-Recoverable mode, thus leaving you no other choice but to restore from backups and your backups are not valid or imcomplete. Been there ... Done that ... NO FUN!!!

Thanks

J. Kusch

 

[SQLBill]

From the event number orders, it appears that SQL Server attempted to configure the tape but it wasn't available yet. Then the tape gets mounted, the backup occurs, and the tape is unmounted. SQL Server considers it a failure since it couldn't do what it needed to do at the beginning and it wants you to know it - which is why the tape didn't eject.

Now, what does your backup job do before the backup actually happens? Does it erase the tape?

-SQLBill

 

[bigdaver]

That makes sense. here's the command that gets run:

BACKUP DATABASE [mydatabaseserver] TO TAPE = N'\\.\Tape0' WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Diff 1', SKIP , STATS = 10, DESCRIPTION = N'Diff 1', FORMAT , MEDIANAME = N'Diff 1', MEDIADESCRIPTION = N'Diff 1'

I think I set it to erase first. I can't remember.
tnx