Verifying backups of the master database
Published 22 November 2018
In SQL Backup Pro, you can include a database integrity check (DBCC CHECKDB) as part of a scheduled restore job, to verify that your backups can be used.
However, this process will not work with backups of the master database (which are restored for test purposes as a user database) as the integrity check will fail because the master database contains pages and metadata that cannot exist in user databases.
If the master database is restored from a backup as a user database (for example, as master_Verification) the database integrity check will fail with errors such as:
06/06/2012 13:05:05: SQL error 8992: Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
06/06/2012 13:05:05: SQL error 8992: Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=1,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
...
06/06/2012 13:05:05: SQL error 8906: Page (1:10) in database ID 21 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
06/06/2012 13:05:05: SQL error 8954: CHECKDB found 1 allocation errors and 11 consistency errors not associated with any single object.
06/06/2012 13:05:05: SQL error 8989: CHECKDB found 1 allocation errors and 11 consistency errors in database 'master_Verification'.
How to verify backups of the master database
The following steps will provide some verification of your backups of the master database:
- Run DBCC CHECKDB on the original master database to ensure it is not corrupt.
- Schedule regular backups of the master database, including CHECKSUM and RESTORE VERIFYONLY in the backup job. (Select CHECKSUM and VERIFY on step 6 of the Schedule Backup Jobs wizard. If CHECKSUM is included in a backup, it will also be run on restore unless you specify NO_CHECKSUM by editing the RESTORE command.)
- Schedule regular restores from the latest backup to a test database (for example, master_Verification) without a database integrity check. (Select Do not run database integrity check following restore on step 4 of the Schedule Restore Jobs wizard.)
Further information
For information about restoring the master database, see Restoring Backups.
For information about backup verification, see Backup Verification.
For more information about running DBCC CHECKDB on the master database, see Paul Randall's post, 'Is my master database really corrupt?' on www.sqlmag.com.
For general information about DBCC CHECKDB, refer to your SQL Server documentation.
