Symptoms
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.
Resolution
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:
RESTORE HEADERONLY Next, run this code:
RESTORE VERIFYONLY FROMDISK = N’c:\BACKUP\pubsbk.jnk’ WITHFILE = X ,NOUNLOAD 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.
-or-
Consider selecting the Overwrite Existing Media option to make sure that the backed up file is always the first backup set in the file.
-or-
Consider backing up the SQL Server databases to a tape on which only SQL Server database backups exist.
-or-
Consider scripting the commands and then run the commands from a command prompt or batch file.For example:
DECLARE @DBNameVARCHAR(200),@TargetLocation VARCHAR(1000),@BackupNameVARCHAR(200),@FileNumberVARCHAR(10),@xpcmdStringVARCHAR(2000),@PrintStringVARCHAR(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)+’ WITHNOINIT ,NOUNLOAD ,NAME = N’ + CHAR(39)+@BackupName+CHAR(39)+’,NOSKIP ,STATS = 10,NOFORMAT ‘EXEC (@xpcmdString)CREATE TABLE #TempBackupResults(BackupName nvarchar(128),BackupDescriptionnvarchar(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName nvarchar(128),ServerName nvarchar(128),DatabaseName nvarchar(128),DatabaseVersionint,DatabaseCreationDatedatetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSNnumeric(25,0),DatabaseBackupLSNnumeric(25,0),BackupStartDatedatetime,BackupFinishDatedatetime,SortOrder smallint, CodePage smallint,UnicodeLocaleId int,UnicodeComparisonStyle int,CompatibilityLeveltinyint,SoftwareVendorIdint,SoftwareVersionMajorint,SoftwareVersionMinorint,SoftwareVersionBuildint,MachineName nvarchar(128))SELECT @xpcmdString = ‘RESTORE HEADERONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)INSERT#TempBackupResults EXEC (@xpcmdString)SELECT @FileNumber = CONVERT(VARCHAR(20), MAX(Position))FROM #TempBackupResults WHERE BackupName=@BackupNameSELECT @xpcmdString = ‘RESTORE VERIFYONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)+’ WITHFILE = ‘ + @FileNumberSELECT @PrintString = ‘Verifying Integrity of Backup Set Number ‘ + @FileNumberPRINT @PrintStringEXEC (@xpcmdString)DROP TABLE #TempBackupResults
