SQL Server Performance Forum – Threads Archive

SQL Tape Backup – Help

[?] Hi there, (SQL 7.0) I try to run a full automated backup system on two databases. The backups would be made to tape using SQL’s backup command. Backups gets appended to the tape until the tape are full. Backups are done between 3am and 4am each day and there is no operator present during this time. If the backup fail (99.9%) due to a full tape I would like to rewind the tape, erase it and restart the backup. Should it fail again the operators will be notified and they can take the problem further. My goal is thus to have a backup of both databases when everybody sets in for work at 8am. Here is the command issued to run the first job. (3am) Step 1 – BACKUP Database VKB to TAPE = ‘\.TAPE0’ WITH DESCRIPTION = ‘Graanstelsel Backup’, MEDIADESCRIPTION = ‘Graanstelsel Backup’, NAME = ‘Graanstelsel Backup’, RETAINDAYS=7, NOFORMAT, NOINIT, NOSKIP , NOUNLOAD Step 2 – BACKUP Database VKB to TAPE = ‘\.TAPE0’ WITH DESCRIPTION = ‘Graanstelsel Backup’, MEDIADESCRIPTION = ‘Graanstelsel Backup’, NAME = ‘Graanstelsel Backup’, FORMAT, INIT, NOUNLOAD If step 1 fail it will go to step 2 then back to 1. If step 2 fail then the job fail. Success if step 1 report no failure. Here is the command issued to run the second job. (4am) Step 1 – BACKUP Database VKBFinansies to TAPE = ‘\.TAPE0’ WITH DESCRIPTION = ‘Finansies Backup’, MEDIADESCRIPTION = ‘Finansies Backup’, NOFORMAT, NOINIT, SKIP , UNLOAD Step 2 – BACKUP Database VKB to TAPE = ‘\.TAPE0’ WITH DESCRIPTION = ‘Graanstelsel Backup’, MEDIADESCRIPTION = ‘Graanstelsel Backup’, NAME = ‘Graanstelsel Backup’, FORMAT, INIT, NOUNLOAD Step 3 – BACKUP Database VKB to TAPE = ‘\.TAPE0’ WITH DESCRIPTION = ‘Graanstelsel Backup’, MEDIADESCRIPTION = ‘Graanstelsel Backup’, NAME = ‘Graanstelsel Backup’, RETAINDAYS=7, NOFORMAT, NOINIT, NOSKIP , NOUNLOAD If step 1 fail it will go to step 2 then back to 1. If step 2 fail then the job fail. Success if step 1 report no failure. I KNOW both jobs are circular in refference. If the tape are not full, the backup works like a charm BUT here is my problem. As soon as the tape are full it just gives the error below and the job fail Step 1 error End of tape has been reached. Remove tape ‘\.TAPE0’ and mount next tape for BACKUP DATABASE of database ‘VKB’. [SQLSTATE 42000] (Error 402<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Associated statement is not prepared [SQLSTATE HY007] (Error 0) Request for device ‘\.TAPE0’ timed out. [SQLSTATE 42000] (Error 3229) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. Step 2 error Mount tape for BACKUP DATABASE of database ‘VKB’. [SQLSTATE 42000] (Error 4027) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Request for device ‘\.TAPE0’ timed out. [SQLSTATE 42000] (Error 3229) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013) [V]It seems that the tape get ejected after the EOT has been reached in step 1 and thus step 2 is rendered useless in both jobs. I might be wrong. Is there a way to prevent the tape from being ejected? Is it perhaps the backup string that I send that has an error? Is there any way to work around this and is this the problem? Regards.

A scheduled SQL Server backup job will not wait for a tape to be mounted should a tape become full during a scheduled backup process. Instead, the scheduled backup job terminates and a message similar to the error defined. Scheduled batch jobs in SQL Server are not designed to wait for tape loads. The only way is to execute the backup commands using OSQL from a command prompt or execute the equivalent backup TSQL commands from the QA. During this process, it prompts the administrator to add a new tape when the current tape is full. After loading a new tape the current backup process continues. You can also use an autoload tape device to automatically load tapes when the current tape is full. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Have you considered buying a second Tape a spare? Also to prevent the tape from ejecting change your code in the step 1 to "NOUNLOAD" instead of UNLOAD. In your second job I noticed that you have a FORMAT statment in step 2 do you know that if step 2 executes you will wipe VKBFinansies? Cheers!

quote:Originally posted by cejar Have you considered buying a second Tape a spare? Also to prevent the tape from ejecting change your code in the step 1 to "NOUNLOAD" instead of UNLOAD. In your second job I noticed that you have a FORMAT statment in step 2 do you know that if step 2 executes you will wipe VKBFinansies? Cheers!

Hi I will change the "unload" to "Nounload". Yes, I know i will wipe the VKBFinansies backup. That is why, if you read the job order, Step 1 will goto 3 if step 1 was ok, else it wil goto 2 (wipe the tape) then goto 1 again

]]>