PRB: Scheduled SQL Server Backups Display "BackupTapeFile::WriteFileMark: WriteTapemark failure" Error (244513)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q244513

SYMPTOMS

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 following is written to the SQL Server errorlog:
BackupMedium::ReportIoError: write failure on backup device '\\.\TAPE2'.
Operating system error 21(The device is not ready.).
BackupTapeFile::WriteFileMark: WriteTapemark failure on backup device '\\.\TAPE1'. Operating system error 21(The device is not ready.).
The SQL Server Agent job also displays an error message similar to the following:
?Job 'Backup' : Step 1, 'PRDBACKUP' : Began Executing 10/1/99 11:57:23 AM
Msg 4028, Sev 16: End of tape has been reached. Remove tape '\\.\TAPE1' and mount next tape for BACKUP DATABASE of database 'idss'. [SQLSTATE 42000] Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007] Msg 4028, Sev 16: End of tape has been reached. Remove tape '\\.\TAPE2' and mount next tape for BACKUP DATABASE of database 'idss'. [SQLSTATE 42000]

CAUSE

Actions requiring human intervention cannot be handled automatically in SQL Server scheduled batch jobs. Scheduled batch jobs in SQL Server are not designed to wait for tape loads.

WORKAROUND

Execute the backup commands using OSQL from a command prompt or execute the equivalent backup Transact SQL commands from the Query Analyzer. The following Transact SQL command backs up the prd database to a multiple tape backup device using the Query Analyzer: 
BACKUP DATABASE(prd) TO TAPE = N'\\.\TAPE0',
                        TAPE = N'\\.\TAPE1',
                        TAPE = N'\\.\TAPE2',
                        TAPE = N'\\.\TAPE3
A message box displays, which 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.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbprb KB244513
©2003 Microsoft Corporation. All rights reserved.