Fix Error on How to Rebuild and Restore Master Database in SQL Server
leteeThe server users are well-known from the fact that there is an MDF file in the SQL Server, which is the master database file of it. It stores entire core information of the server and hence, titled as a primary data file of it. Now a question arises – what if this MDF file is corrupted in the SQL Server 2008/2005 or any other version? Is there any solution to deal with this corrupt primary database file of the SQL server? Don’t get panic! Let’s begin with the workaround to fix such issue by rebuild and restore master database in SQL Server.
Well, when users will try to access corrupt MDF file, they will encounter an error message i.e., cannot recover the master database. SQL Server is unable to run. In addition to this, many administrative users may find that their server application is not working properly hence, interrupting their work. Apart from this, there are chances of facing following more symptoms while opening/operating damaged .mdf file:
- Segmentation Faults or I/O errors
- Generation of a report from DBCC utility
- Unable to open SQL Server program
Well, whenever a user is facing such symptoms, he/she can check whether there is an error in the log file or not. To do so, one can take reference of the following screenshot for verifying corruption reason:
Rebuild and Restore Master Database in SQL Server
When SQL server refuses to launch with a damaged MDF file, an ideal solution is to rebuild and restore master database in MS SQL Server then, open SQL server, and finally, restore the database backup. How to rebuild master.mdf is a big question mark for the server users because of its quite complex, time-consuming, and tedious task. Therefore, one need to devote his/her significant amount of time for rebuilding master database because this procedure includes reinstallation of the SQL server from beginning.
Note: It is the strict instruction to the server users that they should have the backup of their entire data of MDF file, before proceeding further. However, one should weekly create MDF file backup to be safe from any data loss at the time of disaster.
#1: Rebuild MDF File of SQL Server
It is a tricky and a full caution process to perform reconstruction when your databases are connected with the instance. Well, you have to use Rebuildm.exe utility (Rebuild Wizard), located at \ProgramFiles\Microsoft SQL Server\80\Tools\BINN. Follow the below-mentioned instructions to Rebuild and Restore Master Database in SQL Server:
1. To launch this utility, go towards the location of Rebuildm.exe and double-click on it.
2. Mention the required settings of collation and then, browse path of your database server files
3. From the installation CD of SQL, specify the x86 directory and then direct it towards local hard drive copy for gaining a convenient and faster recovery
4. Take a look at all the information entered by you on current wizard of Rebuildm.exe utility and if all is correct then, hit on Rebuild button for allowing the tool to construct a new master database file for your server
5. Execute the command i.e., sqlserver.exe -c -m, in command prompt window. This will enable users to open the SQL Server in a single-user mode
6. Launch SQL Server Configuration Manager and then click on Startup Parameter tab
7. In Specify a startup parameter text box, type -m to add it. At last, click on Apply >> OK to continue
8. Restart the SQL Server program and rebuild MDF file in SQL Server with help of the following syntax:
Till now, you have successfully rebuild master.mdf file in SQL Server 2005/2008/2012, which is available on one disk.
Also Read: How to Recover SQL Database from Suspect Mode?
#2: Restore SQL MDF File From Backup
After successfully completing up with rebuilding procedure, now you have to restore the primary database of the SQL Server i.e., MDF. For this, you need to have the recent backup of the MDF file and its LDF file. To do so, go through following set of instructions:
- Start SQL Server in the single-user mode
- Launch SQL Server Configuration Manager window and select SQL Server 2005 Services
- From the manager window, go to SQL Server instance and right-click on it to select Properties option
- Go to Advanced tab of the SQL Server Properties window and then in Startup Parameters text box, add “-m;” as prefix in existing parameters. Make sure that the prefix is removed after task completion.
- Connect to your SQL Server with help of following restoration command:
As discussed earlier that one need to have high technical knowledge to rebuild and restore master database in SQL Server. But, there is an alternative error-free solution to this hassle manual workaround i.e., SQL MDF Viewer software. The application provides a fruitful way to repair corrupt MDF file and view data from it. This solution does not require any expertise level knowledge of the server and hence, allowing users to experience an instant recovery from corrupt MDF database files and restore master database in SQL Server without any issue.