Data Management

Recover the master database in SQL Server

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


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.

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.

Figure F


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.

Figure G


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.
10 comments
AnnG.Moeller
AnnG.Moeller

SQL database software repairs corrupt MDF file of the SQL Server database created all the SQL Server including 2012/2000/2005/2008/2008 R2 and gracefully recovers SQL XML data type files also.This SQL recovery tool recovers MDF file, NDF File components like triggers, tables, views, rules and stored procedure with ease as well as recovers the deleted tables of the SQL database.

Download:- http://www.filesrecoverytool.com/sql-database-repair.html

Enriquemallon
Enriquemallon

Master database is the backbone of SQL Server database if in case it gets affected the whole SQL database may have to suffer disaster. But their many options are available which acclaimed that master database can be recovered easily. In accordance you must read this article which thought you how to recover SQL master database in specific way.

Just read once:- http://www.sqlrepairtool.org/repair-mdf.html 

frank.thornton
frank.thornton

Corruption in SQL server database can happen due to any uncertain like virus attack, power failure, hardware issue, OS malfunction, sudden system shutdown, so on. When a SQL server data file (.mdf) is corrupt then DBAs can try several methods to repair and recover data from it. 


  • Run DBCC CHECKDB: It checks & reports all the error message in errorlog, if there is any problem with the database. Try to analyze & understand the error message logged in the errorlog. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file. 

Detailed information about DBCC CHECKDB is available here: http://www.techrepublic.com/forums/discussions/sql-database-recovery-from-corrupt-database-file/

  • Restore from backup: If above command fails to fix the error message the try to restore the database from clean backup. 
  • 3rd party tool: Try 3rd party Recovery Toolbox for SQL Server to recover data from corrupt mdf file. Most of the software have demo version that shows the preview of corrupt SQL server data file. 

http://www.sql.recoverytoolbox.com/

isabella145
isabella145

Hi,

Very nice post I really appreciate for this work… But sometime the manual method are quite difficult especially for the non technical user so for them they can use a third party repair tool. They can a use MS SQL Repair Tool to repair and recover the corrupt data base from any sort of corruption issue.  Fro more information visit:


http://sqlmdf.filerepairtool.net/blog/repair-corrupt-sql-server-database

HarryKowal
HarryKowal

To repair and recover a corrupt MDF file you can apply any manual method but all methods are not safe and may affect your data. To recover and repair a corrupt sql file use SQL repair tool. This is the best way to repair a sql file. Free download it from : - http://www.filesrepairtool.com/sql-database-repair.html

rosishi
rosishi

Hello,

I would also like to suggest some information about a professional application by which one can very easily repair damaged or corrupted SQL server Database . It is well known as Kernel for SQL Recovery Tool, by this well programmed application you can repair and recover corrupted MDF files without any errors.

Editor's Picks