Verifying backups of the master database
Published 18 February 2013
In SQL Backup Pro 7, 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:
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.)
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.