Question: In our new process to verify backups we’ve discovered that our master database seems to be corrupt. However, running DBCC CHECKDB on the actual master database does not show any errors. What’s going on?
Answer: It’s becoming increasingly common these days for DBAs to want to offload consistency checking of production databases to a secondary server, so that the heavy resource usage associated with running DBCC CHECKDB does not affect the production workload. The practice of verifying backup integrity is also increasingly prevalent.
Both practices involve taking a full database backup of the production databases, restoring them on a secondary SQL Server (possibly with some differential and transaction log backups as well) and then running DBCC CHECKDB on the restored copies of the production databases. I’ve blogged about this several times before.
Really good DBAs know the importance of the system databases (master, model, and msdb) and so also include them in their backup checking/consistency checking routines. These databases are restored as user databases with names like ‘msdbcopy’ along with moving their files to avoid conflicts with the real system databases on the secondary server.
The problem with this methodology is that master is a very special case and although it can be restored as I’ve described, it cannot then be consistency checked as a user database.
Let me show you:
That works perfectly. But if I try to run DBCC CHECKDB, it’s not so good:
And if I run DBCC CHECKDB on the original master database, it doesn’t give any errors. This is because there are some elements of metadata that can only exist in master, and page 10 in file 1 in master is a one-off special page for the server called the config page – it cannot exist in other databases.
To summarize, you absolutely should include master in your backup testing methodology, but the consistency checks need to be run on the production server itself.