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
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.htmlAnonymous
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-serverAnonymous
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 removedAnonymous
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 simpleAnonymous
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-databaseAnonymous
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.htmlAnonymous
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?_=1416149856104Anonymous
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 removedAnonymous
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 removedAnonymous
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