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

WORKAROUND

• 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 FROM  DISK = N'c:\BACKUP\pubsbk.jnk' WITH  FILE = 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 @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)
  INSERT
    #TempBackupResults 
  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