jump to navigation

Restoring the master database in SQL Server November 10, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.

I had a seminar in SQL Server 2012 recently and one of the people in my class asked me what is the best way to restore a master database

In this post I will like to shed some light on the issue.

As we all know we must include in our backup strategy, the system databases backup. We must backup the master and msdb databases regularly.

If for some reason the master database becomes corrupt or missing , the SQL Server instance cannot be started.

Master and tempdb cannot be repaired. Repair requires single-user mode, since master and tempdb cannot be put into single-user mode.

If your SQL Server master database becomes corrupt, some people suggest  to rebuild the master database, then start SQL Server, then restore the backup of the master database. I do not suggest rebuilding the master database.That is really time-consuming and you need to do a lot of trial-and-error, especially if have a cluster disk subsystem.Some people recommend the full re-installation of the  SQL Server. Well that does not sound ideal as well.

Instead, you can restore a backup of the master database on another instance of SQL Server as long as it’s the same version of SQL Server, then use the restored files to replace the corrupt files on the broken system.

  • An easier solution would be to restore the backup of the master database backup to another instance of SQL Server
  • In the  section of the Restore Database dialog box, use a different database name such as attempted_recovery_master to avoid conflict with the master database on the SQL Server you are restoring.
  • The master .mdf/.ldf files will be be named attempted_recovery_master.mdf and attempted_recovery_master_1.ldf.
  • We then need to detach the attempted_recovery_master database
  • Then we need to copy the attempted_recovery_master.mdf  and the attempted_recovery_master_1.ldf and then paste them to the instance of SQL Server with the corrupted master database.
  • We then must delete the corrupted master.mdf and mastlog.ldf file
  • Then we need to rename attempted_recovery_master.mdf to master.mdf and rename attempted_recovery_master_1.ldf to mastlog.ldf.
  • We can now safely restart the SQL Server service

One thing to note is that the SQL Server versions of the two instances must match. You cannot restore a master database backup from SQL Server 2005 to SQL Server 2008,detach it and then try to restore it to a SQL Server 2005 instance.I will not work.

It is always better to use a restore to fix system database/table corruption.

Hope it helps!!!



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: