Recently, while working with a customer to help restore their SQL services, I came across an issue I hadn’t seen before and more interestingly, one where little information can be found within the community.
This can be replicated by yourself by attaching or restoring a copy of the master database as a user database. You may never need to do this but just in case you do, don’t be alarmed if ‘corruption’ is found within the user database once it’s attached. The problem was that the customer didn’t have access to their backups. So, my task was to attach all the databases to a recovery instance and execute a DBCC CHECKDB against them. All the user databases were fine and most of the system databases (restored as user databases too) were also fine. All except for the master database. Or so I thought…
To replicate the problem for this blog I’ll be backing up and restoring the same master database that I have on my own development instances’.
First, backup the master database:
BACKUP DATABASE MASTER TO DISK = N'C:\BACKUPS\MASTER.BAK'
WITH INIT, CHECKSUM
When executing a DBCC CHECKDB at this point, you’ll notice this completes successfully:
Moving onto performing the restore. To restore the master database as a user database, you’ll need to use RESTORE FILELISTONLY to determine to file locations. This is handy as the ‘WITH MOVE’ clause will be required when restoring the database files to a different location:
RESTORE DATABASE MASTER2 FROM DISK = N'C:\BACKUPS\MASTER.BAK'
WITH
MOVE 'master' TO 'C:\Program Files\Microsoft SQL Server\INSTANCELOCATION\MSSQL\DATA\master2.mdf',
MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\INSTANCELOCATION\MSSQL\DATA\mastlog2.ldf'
Refreshing the databases node, you should now see the new ‘MASTER2’ database:
Now the database has been restored we can execute the DBCC CHECKDB against it. Below you can see a snippet of the errors that are output:
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).
Msg 8992, Level 16, State 1, Line 1
Don’t be alarmed by this, the newly restored MASTER2 user database isn’t corrupt at all; it’s a symptom of the database being restored as a user database. If we backup the new MASTER2 database and this time restore it over the top of our existing master (system) database, you’ll find the corruption is no longer reported.
The master database has special structures at the start that no user (or other system) database has. When DBCC CHECKDB is executed against the master (system) database DBCC CHECKDB considers this special structure and ignores the ‘corruption’. As you can see, if the master database is restored as a user database CHECKDB doesn’t know its past life and treats it like any other user database reporting corruption.
Once the master database has been restored executing DBCC CHECKDB against the newly restored master system database will show the database is corruption free.
If you'd like to know more about our dedicated support service and how we can help you manage your SQL Server environment click the link below: