SQL 2000 Backup

Question

SQL 2000 Backup

davidm-939619

Grasshopper

Points: 16
More actions
November 13, 2008 at 9:36 am

#383378

I am having a problem with an SQL 2000 server backup job. This job dumps to
DLT tape devices. It has been hit or miss as to if the job runs successfully
or not. Please bear with me as I am an SQL idiot...
System Details.
Windows 2000 Server SP4
SQL 2000 Enterprise SP3 (8.00.876)
3 GB RAM
4 Procs.
I have pasted 2 errors from the application log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 10/26/2008
Time: 04:00:30
User: ****\40F2SmsSoaAccount
Computer: ***SQL
Description:
18210 :
BackupTapeFile::RequestDurable: WriteTapemark failure on backup device
'\\.\Tape1'. Operating system error 21(The device is not ready.).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 22 47 00 00 10 00 00 00 "G......
0008: 07 00 00 00 4e 00 44 00 ....N.D.
0010: 57 00 53 00 51 00 4c 00 W.S.Q.L.
0018: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 10/26/2008
Time: 03:57:49
User: ***\40F2SmsSoaAccount
Computer: ***SQL
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE ****_**_**
TO tapedump1, tapedump2, tapedump3
WITH NOUNLOAD, SKIP

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 740103 More actions · Answer 1

It's a problem with your tape drive, or the media. When you see "operating system error" it's something outside of SQL Server.

Backing up to tape is not recommended as the SQL Server backup process has no tolerance for delays or issues. It is trying to write the backup out there quickly. It's the same reason we do not recommend backing up to a network disk.

The best bet here is to back up to disk (same command, but backup TO DISK with a file name instead of a tape name) and then use NTBackup or your tape software to get the backup files copied to tape.

davidm-939619 Grasshopper Points: 16 More actions · Answer 2

Steve

Thank you for your quick response. I suspect the media itself may be the culprit given the age of the media. As for backing up to disk--I agree with that method--now I need to sell it to the IT Manager.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

davidm (11/13/2008)
As for backing up to disk--I agree with that method--now I need to sell it to the IT Manager.

Shouldn't be too hard. Point out to him that at the moment it's unlikely that you will be able to restore in the case of a disaster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

davidm-939619 Grasshopper Points: 16 More actions · Answer 4

Just to update all of you who have responded to this topic. Out of morbid curiosity I cleaned the tape drives before the backup jobs kicked off. The jobs ran without incident. :w00t: The Clean Tape Drive indicator lights were not on so this was a shot in the dark. I'm not sure what, if anything gets ruled out--bad hardware or bad media--although I still think the media is the culprit.

Thanks to everyone for your input.

davidm-939619 Grasshopper Points: 16 More actions · Answer 5

Here is an error from the Job History.

Executed as user: ****\40F2******. Request for device 'tapedump2' timed out. [SQLSTATE 42000] (Error 3229) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I get the same error on another device--tapedump1 as well. Is this an indicator of bad hardware or media? The media is 4 years old so I'm still of the opinion that the media is the issue.

Thanks