Recover the master database in SQL Server

by Steven "Warren MCSE, MCDBA" | Jan 15, 2003 8:00:00 AM

Tags: Storage, Databases, Steven Warren MCSE, MCDBA, database, Microsoft SQL Server, master database, server

Takeaway: If the master database fails, Microsoft SQL Server can be brought to its knees. See how to recognize this event and learn the steps for recovering the master database using the Enterprise Manager and the Query Analyzer.

As a Microsoft SQL Server administrator, you must know how to recover a corrupt master database. The master database stores your logins and, most importantly, the pointers to all of your databases. Without the master database, you can't successfully start SQL Server. I'm going to walk you through the process of recovering the master database in the event of corruption and show you how to rebuild the master database, if necessary.

Have a plan
It is important to have a plan for dealing with the corruption and/or failure of your master database. That will help you follow a methodical approach when disaster strikes, rather than acting too quickly under pressure. I have been in many situations where it would have been easy to panic, but I've managed to weather the storm by remaining calm and following the proper methodology when dealing with a problem.

How do you know if your master database is corrupt?
Before we discuss how to recover and rebuild your master database in the event of a failure, we need to look at how you can tell if it's corrupt. To demonstrate, I'll break a master database to show you what happens if your master gets corrupted.

Let's pretend that your company had a power surge and your SQL Server rebooted. Upon reboot, SQL Server would not start. If you check the error log (Figure A), you'll see that the master database is either corrupt or missing. Now that you know what message to look for, let’s see how to recover a master database.


Recover your master database
Your first step in recovering your master database is to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory. Let’s walk through the Rebuild Wizard to see how it works.

Start by double-clicking Rebuildm.exe to bring up the screen shown in Figure B.


On this screen, you can specify the collation settings of your database server and the location of your data files during your original install. To make the latter easier and faster, copy the x86 directory from the SQL CD to your hard drive and point to the local copy. Once you have verified all of this information, click Rebuild. You'll then be prompted to confirm the operation, as shown in Figure C.


Click Yes. Once the process is completed, you'll see a message telling you that the rebuild was successful. You now have a brand new master database and are ready to restore your master database.

First, start SQL Server in single-user mode by opening up a command prompt and issuing the command sqlservr.exe –c -m from the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory. The results are shown in Figure D.


After you start SQL Server in single-user mode, you can restore your master database from a backup. You can restore it using either the Query Analyzer or SQL Enterprise Manager. If you're using Query Analyzer, run the query shown in Figure E.


If you're using Enterprise Manager, right-click on the master database, choose All Tasks | Restore Database, and browse to where your device is located, as shown in Figure F. Click OK twice, and you have successfully restored your master database.


Once you've restored your master database, exit single-user mode and restart SQL Server in normal operation mode.

If for some reason your restore operation does not work, you can try an alternative method. Simply rebuild the master database and attach all of your databases that reside in the data directory. You can attach the databases using Enterprise Manager or Query Analyzer. In Enterprise Manager, right-click on Databases and choose Attach Database, as shown in Figure G. In Query Analyzer, the sample script in Listing A shows how to attach your databases.


Final word
Now that you have learned how to successfully re-create your master database in the event of a disaster, you can add these techniques to your disaster recovery plan. That way, you won’t be left scrambling when a corrupt master database in SQL Server brings your database server to a halt.

Print/View all Posts Comments on this article

Should end-users be taught this? Jay Garmon | 05/12/04

Easier, safer way to this? B.A. | 05/15/04

Of course not! AngelaBut | 05/18/04

Master Recovery dennyco@... | 08/22/06

RE: Recover the master database in SQL Server iftikharqamar@... | 09/10/07