Do you have a corrupt backup with MSSQL?

  • How do you know if your backup is corrupted?
  • Do you really want to know that your backups are corrupt? [rhetorical question, if you answered no, then look for another job].

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Microsoft provides a corrupted database: broken.zip
  • To use the database do:
  1. RESTORE DATABASE broken FROM DISK='c:\broken.bck' WITH MOVE 'broken' TO 'c:\broken.mdf',
  2. MOVE 'broken_log' TO 'c:\broken_log.ldf';
  3. GO
Processed 160 pages for database 'broken', file 'broken' on file 1.
Processed 2 pages for database 'broken', file 'broken_log' on file 1.
RESTORE DATABASE successfully processed 162 pages in 0.314 seconds (4.203 MB/sec).

Verify if the database is corrupt

  1. DBCC CHECKDB ('broken') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  2. GO
Msg 8928, Level 16, State 1, Line 1
Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data): Page (1:143) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data), page (1:143). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'brokentable' (object ID 2073058421).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'broken'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (broken).

Yes, we have a corrupt database, courtesy Microsoft.

Verify if the backup is corrupt

  • The only way of knowing if your backup is corrupt is to use: restore verifyonly.
  1. RESTORE VERIFYONLY FROM DISK='c:\broken2.bck';
  2. GO
The backup set was written with damaged data by a BACKUP WITH CONTINUE_AFTER_ERROR.
  • NEVER, NEVER & NEVER use the option BACKUP WITH CONTINUE_AFTER_ERROR.

Proper way of verifying if the backup is corrupt

  1. RESTORE VERIFYONLY FROM DISK='c:\broken.bck' WITH CHECKSUM;
  2. GO
Msg 3187, Level 16, State 1, Line 1
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
  • You should:
    1. backup with checksum
    2. After running the backup, run restore verifyonly with checksum
  • Only these 2 operations will validate your backup.