Is my master database really corrupt?

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.

Related: SQL Server Database Corruption, Part VI: Regular Corruption Checks

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:

BACKUP DATABASE master TO DISK = N'C:SQLskillsmaster.bck';GORESTORE DATABASE mastercopy FROM DISK = N'C:SQLskillsmaster.bck'WITH MOVE N'master' TO N'C:SQLskillsmastercopy.mdf',MOVE N'mastlog' TO N'C:SQLskillsmastercopylog.ldf';GO

That works perfectly. But if I try to run DBCC CHECKDB, it’s not so good:

DBCC CHECKDB ('mastercopy') WITH NO_INFOMSGS;    GO    Msg 8992, Level 16, State 1, Line 1    Check Catalog Msg 3851, State 1: An invalid row (class=12,depid=0,depsubid=0) was found in the system table sys.syssingleobjrefs (class=12).    Msg 8992, Level 16, State 1, Line 1    Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13).    <24 similar errors removed for brevity>         Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65611,depsubid=100) was found in the system table sys.syssingleobjrefs (class=76).    Msg 8992, Level 16, State 1, Line 1    Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65611,depsubid=101) was found in the system table sys.syssingleobjrefs (class=76).    CHECKDB found 0 allocation errors and 28 consistency errors not associated with any single object.    Msg 8906, Level 16, State 1, Line 1    Page (1:10) in database ID 40 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'.    CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 99)' (object ID 99).    CHECKDB found 1 allocation errors and 28 consistency errors in database 'mastercopy'.

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.