The Easiest Way To Rebuild The master Database


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 (25)

  1. Quantum John says:

    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.

  2. Anonymous says:

    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

  3. Quantum John says:

    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.

  4. Anonymous says:

    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 here
    http://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

  5. Anonymous says:

    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 article
    http://www.sqlrecoverysoftware.net/sql-server-mdf-file/

  6. Shanky_621 says:

    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.

  7. Keith says:

    Great solution!

    I'm assuming different sp/cu levels won't matter either?

  8. Gerardo says:

    Quantum I have a problem , when i recreated a sql with the same configurations, but when i try to restore de backup of the master database, i get this error , " media family on device xxxxxxxxxxx is incorrectly formed server cannot process this media family on device ", i have a backup of the master but i cannot load it from the original server cause the master database its corrupt and I cant load the service of sqlserver, please any advice would be really apreciated. regards

  9. Bhargav Lalisetti says:

    Very Nice…Its' good to Understand and easiest way..

  10. gumoff says:

    path of db/instance has to be same as original…

  11. Nasar Azam says:

    Excellent and very helpful article.

  12. John Dinh says:

    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.

  13. pradeep says:

    its very simple

  14. SQL Depends says:

    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?

  15. julialloyds45 says:

    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

  16. RonSexton says:

    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.

  17. Freddy says:

    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!

  18. myplast1 says:

    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

  19. EmilysMalone says:

    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

  20. Faz says:

    thx for the great solution.. saved lot of time.

  21. ankemercy says:

    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/

  22. Abbers says:

    Thank you so much! I had to hunt around in the a cupboard for my SQL Server install CDs, but you got me back up and running. My model DB was corrupted following a BSOD and drive failure, so the SQL services would not start. Therefore I couldn’t start the
    Enterprise Manager GUI, nor could I work out the command line syntax to restore from my recent backups. So I installed SQL server to a clone server, recovered the flatfile MDF and LDF files from last nights’ backup and now I can leave the office and go home!

  23. millerklark says:

    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/

  24. SeanDMcG says:

    This was a great help. I had a cluster whose data array got powered off, corrupting the master. One caveat here: depending on your backup software, the version of the "recovery" server may need to be the same as the damaged SQL server __when the master
    was first created__ . I created a recovery server that was patched to the same level as my cluster. However , when restoring with CommVault it informed me that the version was (in effect) too high. I had to remove the patches to get the restore to take. NOTE:
    I am pretty sure this is an issue with CommVault SQL data agent not allowing the file to be restored with a different name. Your software or backup routines may differ. After that, it all worked fine. I know I’m keeping a copy of these files handy elsewhere.

  25. tonyhill20 says:

    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

Skip to main content