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.Figure A
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.
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.
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.