Microsoft KB Archive/253230

From BetaArchive Wiki
Knowledge Base

FIX: SEM: Appended Backup Database with Verify Always Checks Only FILE Number = 1

Article ID: 253230

Article Last Modified on 3/14/2006


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q253230

BUG #: 57450 (SQLBUG_70)


When you use SQL Server Enterprise Manager to backup and verify the backup of a database, the verification assessment of the backup is not reliable if the backup set is appended to either a previously existing file or device.

Furthermore, if you attempt to establish a Database Maintenance Plan through the Database Maintenance Wizard and you select Verify the Backup to a tape that contains a Microsoft Windows NT file backup that is File number 1, you will see this error message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 3143: [Microsoft][ODBC SQL Server Driver][SQL Server]
The data set on device '\\.\Tape0' is not a SQL Server backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore
operation terminating.

Also, if you put a SQL Server database backup on the tape first and you then attempt to perform a Windows NT backup, a Dr. Watson error (0xc000005) occurs.


Here are a few ways to work around this problem:

  • Run this line of code from Query Analyzer to the backup device or file to identify the last backup set in the file:


    Next, run this code:


    where X is the maximum file id identified by the Position output column from RESTORE HEADERONLY. If you use the Database Maintenance Wizard, alter the previous RESTORE statement in the Schedule Task.


  • Consider selecting the Overwrite Existing Media option to make sure that the backed up file is always the first backup set in the file.


  • Consider backing up the SQL Server databases to a tape on which only SQL Server database backups exist.


  • Consider scripting the commands and then run the commands from a command prompt or batch file. For example:

    DECLARE @DBName         VARCHAR(200)   ,
            @TargetLocation VARCHAR(1000)  ,
            @BackupName     VARCHAR(200)   ,
            @FileNumber     VARCHAR(10)    ,
            @xpcmdString    VARCHAR(2000)  ,
            @PrintString    VARCHAR(200)
    SELECT @DBName         = '[pubs]'               , 
           @TargetLocation = 'c:\BACKUP\pubsbk.jnk' , 
           @BackupName     = 'pubs backup'
    SELECT @xpcmdString = 'BACKUP DATABASE '+@DBName 
                          +' TO DISK = N' + CHAR(39)+@TargetLocation+CHAR(39)
                          +' WITH  NOINIT ,  NOUNLOAD ,  NAME = N' + CHAR(39)+@BackupName+CHAR(39)
                          +',  NOSKIP ,  STATS = 10,  NOFORMAT '
    EXEC (@xpcmdString)
    CREATE TABLE #TempBackupResults
    BackupName nvarchar(128)               ,
    BackupDescription  nvarchar(255)       ,
    BackupType smallint                    ,
    ExpirationDate datetime                ,
    Compressed tinyint                     ,
    Position smallint                      ,
    DeviceType tinyint                     ,
    UserName nvarchar(128)                 ,
    ServerName nvarchar(128)               ,
    DatabaseName nvarchar(128)             ,
    DatabaseVersion  int                   ,
    DatabaseCreationDate  datetime         ,
    BackupSize numeric(20,0)               ,
    FirstLSN numeric(25,0)                 ,
    LastLSN numeric(25,0)                  ,
    CheckpointLSN  numeric(25,0)           ,
    DatabaseBackupLSN  numeric(25,0)       ,
    BackupStartDate  datetime              ,
    BackupFinishDate  datetime             ,
    SortOrder smallint                     , 
    CodePage smallint                      ,
    UnicodeLocaleId int                    ,
    UnicodeComparisonStyle int             ,
    CompatibilityLevel  tinyint            ,
    SoftwareVendorId  int                  ,
    SoftwareVersionMajor  int              ,
    SoftwareVersionMinor  int              ,
    SoftwareVersionBuild  int              ,
    MachineName nvarchar(128)
    SELECT @xpcmdString = 'RESTORE HEADERONLY FROM DISK = N' + CHAR(39)+@TargetLocation+CHAR(39)
    EXEC (@xpcmdString)
    SELECT @FileNumber = CONVERT(VARCHAR(20), MAX(Position))
    FROM #TempBackupResults 
    WHERE BackupName=@BackupName
    SELECT @xpcmdString = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39)+@TargetLocation+CHAR(39)
                          +' WITH  FILE = ' + @FileNumber
    SELECT @PrintString = 'Verifying Integrity of Backup Set Number ' + @FileNumber
    PRINT @PrintString
    EXEC (@xpcmdString)
    DROP TABLE #TempBackupResults


Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.


To reproduce this problem from within SQL Server Enterprise Manager, follow these steps:

  1. Select a database (for example, pubs).
  2. Right-click All Tasks...Backup Database.
  3. Perform a backup to some file.
  4. Leave the default Append to media selected.
  5. In the Options dialog box, select Verify backup upon completion.
  6. Click OK.

You will see a message that indicates the backup operation and its verification have completed successfully. However, the RESTORE VERIFYONLY option may show in Profiler that it always executes against FILE=1. However, if the backup task is appended to the device or file and then the backup is repeated it will not be the last backup set in that file.

Keywords: kbbug kbfix KB253230