SQL Server 2000 Tape Backup error
I have schedule backup job from SQL Server 2000 to Tape. Each Jobs consist of 4 steps which backup different database.
Sometimes I encounter problem. The Message is
Executed as user:dbo. The file on device '\\.\Tape0' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
There is no problem at the command, as it will run normally when i manually trigger the schedule job. Sometimes the error occur only after few days the job run.
[576 byte] By [
EngHim] at [2008-2-15]
I'll start with getting the obvious out of the way, and then we can go from there.
Are you sure that these failures don't coincide with a tape change? In other words, could it be the case that the job fails when a fresh, uninitialized tape is encountered?
Does each day's job use a fresh tape, or do you append several days together?
If you append, how do you make sure that the first backup on a new tape specifies WITH FORMAT?
Here is the detail of my schedule.
There is four schedule jobs.
1. Change Tape - which will format the tape
2. Daily Full Database Backup - Will use init and skip for the first step (overwrite), for the subsequence steps append mode is used. This schedule is run Daily once.
3. Differential Backup - This job will run once every 4 hourly
4. Transaction Log Backup - This job will run once every 4 hourly.
All the job will have different of 2 hour. So that there is no overlap of jobs run. except Change Tape which is not schedule. Currently I let the tape run without changing the tape. The schedule job run sucessfull, but sometimes error will come.
For your information, each job consist of 4 steps which each step will backup different database.
If you are getting that error, it means that SQL backup has attempted to use a tape that has not been formatted by SQL.
That means that there are 3 possibilities that I can think of:
- Your Change Tape job either failed or was not run when a tape was changed
- Someone used the drive for other purposes and did not replace the correct tape
- Your tape hardware has serious problems
I would look through the logs to verify that the Change Tape job was indeed run successfully each time that the tape was changed, especially before a backup failure.
Currently I did not change any tape yet. I just let the Full Backup, Differential Backup and Transaction Log Backup run only. The full backup, 1st database backup will have the command init, skip.
I am using Ultrium 2 Tape (200/400 Gb).
Nobody except me is using the tape for backup purpose only.
Sometimes the job will fail on different step. Sometimes it will pass for few days, then only start to fail.
Unfortunately, if the tape is not being changed, and your schedule is as above, the only other option is that either the tape drive or the media are faulty. I would try cleaning the heads of the tape drive and using a fresh tape to see if that changes anything. Also look in the system event log to see if there are any device errors for the tape drive.
