Restoring the master database, but not in this way!

  • [アーティクル]

When working on issues, we do a whole series of investigation steps, these tend to lead to other random checks which are performed that can often lead to other interesting things.

Whilst working on an issue relating to permissions with a customer I asked for the output of DBCC DBINFO on the master database.  This revealed some interesting information on how they had performed their restore to resolve an earlier issue.

The database id for master was 19, not 1 and the name wasn’t correct.  However, in sys.databases the database id was correct (1) and the name was “master”.

While digging a little deeper into how they came into this issue it turns out that the way the master database was restored wasn’t as per our documented process from books online (https://msdn.microsoft.com/en-us/library/ms190679(v=sql.110).aspx)

The process used was along the lines of:

  1. Restore the master database on a DR server with the name master_temp
  2. Copy the master_temp mdf and ldf from the DR server to the primary server (whos sql service is currently offline)
  3. move the old master mdf and ldf to another location so it’s not in the main data folder
  4. copy in the master_temp mdf and ldf
  5. rename the master_temp mdf and ldf physical files to that of the master database
  6. bring the sql instance online

 

Although this worked ok and the instance came back online this caused the incorrect information in the boot page of the master database.

This is what the output of DBCC DBINFO looked like:

image

 

This is what the output should look like:

image

 

We cannot say for certain what impact this meta data mismatch will have, but what we can say is that if a support call is raised for an instance in this state and it turns out to be related to an incorrect database id then the issue won’t be supported until the database id is fixed by rebuilding the master database.  This would go for all system databases too.

The recommendation for restoring the master database in this scenario would along the lines of:

  1. Restore master database backup to another server and CHECKDB it.  This is to ensure that it is a good backup and corruption free
  2. Restore same backup to primary server using the process documented in books online (https://msdn.microsoft.com/en-us/library/ms190679(v=sql.110).aspx)
  3. CHECKDB the database again.  The database is on a new storage subsystem after all so needs another CHECKDB to check the physical storage layer

Remember, it’s extremely important not only to backup your system databases regularly, but to restore them to another instance to test that they work.  If you are in a situation where you need to restore your master database, ensure that you follow the process outlined in books online… and practice it!