Print this page
Tuesday, 14 July 2015 00:00

Recover the master database in SQL Server

Rate this item
(0 votes)

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.

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.

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.

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.

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.

Figure F

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.

Reference: http://www.techrepublic.com/article/recover-the-master-database-in-sql-server/

Last modified on Friday, 17 July 2015 08:31
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).