Share via

The Easiest Way To Rebuild The master Database

  • Article

If your SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running, the conventional advice is to rebuild the master database, start SQL Server, then restore the backup of the master database. That's because SQL Server won't start with a corrupt master database. But rebuilding the master database is usually quite time-consuming and frustrating, with a substantial amount of trial-and-error, especially if it's on a cluster. It can be so troublesome, a lot of people consider reinstalling SQL Server from scratch! If that's what you're facing, then this may be a big help...

The easiest way to rebuild the SQL Server master database is: Don't!

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.

HOW-TO: Restore your master database backup to another instance of SQL Server and in the "To database:" section of the Restore Database dialog box, use an alternate database name such as "recover_master" to avoid conflict with the master database on this instance of SQL Server. This will cause the mdf/ldf to be named "recover_master.mdf" and "recover_master_1.ldf". Then detach the recover_master database, go into the file system, copy the recover_master.mdf and recover_master_1.ldf to the instance of SQL Server with the corrupt master database. Delete the corrupt master.mdf and mastlog.ldf files, rename recover_master.mdf to master.mdf, and rename recover_master_1.ldf to mastlog.ldf. Now you're ready to try starting the SQL Server service again!

Because the master database is usually very small, this method should just take a few minutes, it should work for all versions of SQL Server, and it works for both stand-alone instances and clustered instances.

IMPORTANT: The version of SQL Server used for the restore has to match that of the damaged instance. For example, if you restore a database backup from SQL Server 2005 on SQL Server 2008, the restore process will modify the structure of the file, and you won't be able to use the restored mdf/ldf on the older version of SQL Server.

By the way, if you've ever experienced pain in rebuilding a master database, and your reaction to this post is "why didn't I think of that?"... it could have been because you were under a lot of pressure to get the system working again, it was in the middle of the night, you were already exhausted, etc.

 

This post isn't tagged as off-topic because disaster recovery planning and practice is a major STIG requirement, including backups and test-restores. Think of how bad off you'll be if you don't have a backup of your master database: You'll be wasting a lot of time recreating logins, roles, database mappings, and setting permissions -- all of which could be avoided with a simple backup. And test restores not only validate the quality of your backups, they also give you valuable practice so the first time you perform restores isn't while you're under the stress of a real disaster!

------------------------------------------------------------------------------------------------

(SEO phrases:

Rebuild the master database on SQL Server 7.

Rebuild the master database on SQL Server 2000.

Rebuild the master database on SQL Server 2005.

Rebuild the master database on SQL Server 2008.

Rebuild the master database on SQL Server 2008 R2.

Rebuild the master database on SQL Server 2011.

Rebuild the master database on a clustered instance of SQL Server.)

Comments

  • Anonymous
    January 01, 2003
    You're right, Keith, SQL Server's metadata schema doesn't get changed in service packs and cumulative updates. I'm not sure about 2008 vs. 2008 R2... I need to check that out.

  • Anonymous
    January 01, 2003
    This tool efficiently recovers entire MDF files and SQL database including triggers, tables, keys, procedures, indexes. It automatically rebuilds a new database MDF file making it the best tool among many others in the market. Visit: -http://www.pcrecoverytools.com/sql-database-recovery.html

  • Anonymous
    January 01, 2003
    Hi Gerardo, sorry for not replying promptly, but if the system sent me a notice when you posted, I didn't see it. I'm guessing you resolved your problem long ago. At any rate, that message looks to me like it might be a corrupt backup file. Test-restores should be performed on a regular schedule to make sure the backup files are good.

  • Anonymous
    January 01, 2003
    Download an easy-to-use RecoveryFix for SQL database recovery tool without paying anything. Use it to recover and preview data from damaged SQL server. You can try this software from herehttp://www.sqlrecovery.org and more details please read this post.http://diskdatarecovery.jimdo.com/2013/07/23/how-to-restore-lost-data-on-server-while-running-sql-server

  • Anonymous
    January 01, 2003
    Really wonderful article and the way to restore master database is may be quiet comfortable and easy. To restore SQL master database must read this articlehttp://www.sqlrecoverysoftware.net/sql-server-mdf-file/

  • Anonymous
    January 01, 2003
    Hi John
    I think should specifically mention about service packs also what will happen if suppose both databases are sql server 2005 but service packs are differnt.I guess you cannot attach master DB from SP4 version to SP3.

  • Anonymous
    February 02, 2011
    Great solution! I'm assuming different sp/cu levels won't matter either?

  • Anonymous
    February 22, 2011
    The comment has been removed

  • Anonymous
    August 08, 2012
    Very Nice...Its' good to Understand and easiest way..

  • Anonymous
    June 06, 2013
    path of db/instance has to be same as original...

  • Anonymous
    September 12, 2013
    Excellent and very helpful article.

  • Anonymous
    October 25, 2013
    Interesting solution! With this solution, is it possible to copy databases to new server with the following process?    Shutdown database server A    Install server B with SQL Server software    Copy all data and log (mdf & ldf) files from A to B    Start server B This process does not require master database restoration.

  • Anonymous
    February 28, 2014
    its very simple

  • Anonymous
    April 08, 2014
    The result of the below query shows the server name of the other good instance name, as I have taken the backup file from a good master DB from other instance.

    To resolve this drop server name and add server name is all it took.
    Is this correct or any other best way?

  • Anonymous
    April 22, 2014
    To recover and restore your master database you require expert knowledge so you need to repair your database using SQL MDF Repair Tool. It is very helpful to recover any corrupted database easily without applying lengthy commands. By applying this tool any one can recover and restore the corrupted master database because it not necessary to know DOS commands and other manual process while using this database recovery tool.

    For More Information: http://sqlmdf.filerepairtool.net/blog/recover-restore-sql-master-database

  • Anonymous
    June 02, 2014
    Thanks this is a great article and was very helpful where SQL Server refused to start in minimal mode for the master database restore. All database files where wiped out due to someone doing a file level restore of the server and all open files, database files, where lost. This included the 'resources' database. It complained about everything and would not start. This was a great help in getting around this issue.

  • Anonymous
    June 11, 2014
    A great guide!!! Thanks mate!

    1 of the sql server in my environment failed to start the sql server due to corrupt master db. there is no backup since it is a QA server. by following this guide, copying a master db from another working sql server, the corrupt sql server is now able to start up. then attach back the previous db brought back 90% of previous environment.

    thanks very much!

  • Anonymous
    July 08, 2014
    MS SQL is an application produced by Microsoft which is used broadly for efficient data management by many organizations around the world and has really become an indispensable need of users all over. SQL or the Structured Query Language helps the users to query the databases and also to easily retrieve information from databases that had been made already. In this MS SQL Server, the files are saved in .mdf file format.
    For more information and free download
    http://sqldatabserecovery.weebly.com
    http://sqldatabserecovery.weebly.com/recovery-for-sql.html

  • Anonymous
    November 19, 2014
    This will only work if the SQL Server was stopped when you copied the MDF file from the old SQL Server. If you copied the file while the SQL Server was running this file probably will not attach either and you will need to get a valid backup of the database. Download help tool for sql database - SQL Server Restore Toolbox. http://www.sqlserver.restoretools.com/ You can read up more on SQL Server here.http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/498-creating-a-new-database-using-an- mdf-file?_=1416149856104

  • Anonymous
    February 03, 2015
    thx for the great solution.. saved lot of time.

  • Anonymous
    May 19, 2015
    Have a look here http://www.en.repairsqlserver.net/. By using this smartly programed software, one can easily repair badly corrupt or damaged MDF files of SQL Server Database within seconds. You can download a free trail of this tool from here http://www.mdfrecovery.net/

  • Anonymous
    June 09, 2015
    The comment has been removed

  • Anonymous
    June 22, 2015
    Get more effective software, it easy to recovery of indexes, tables, stored procedures, views, primary keys and unique keys from corrupt SQL database. To know more detail click here -http://www.sqlrecoverytool.mdfrecovery.org/

  • Anonymous
    August 20, 2015
    The comment has been removed

  • Anonymous
    September 14, 2015
    A perfect solution for repair corrupt, damaged or corrupt MS SQL Server database, you can use more trusted effective software, you can use Kernel for SQL Database Recovery Tool. It can easy to repair all lost and damaged data. Click here -http://www.sqlrecoverytool.mdfrecovery.org