Repair Rebuild In SQL Server to Fix Master Database Corruption
Master Database is the most important part of SQL Server. It stores the user login credentials and information pointers to all databases in SQL Server. Needless to say, If something happens to the master database, users are unable to access or work smoothly. In this post, possible solutions to recover from corruption in SQL Server have been discussed. One such way is SQL Server repair rebuild usage. Other ways can be more efficient and easier to follow. Find out more in the following segment.
Possible Solutions to Repair Master Database
There are multiple methods through which a system administrator can repair or fix master database corruption. Following are some of the methods listed below:
Solution#1 Using The Microsoft SQL Server Management Studio Express
This is a Management platform provided by Microsoft for SQL Server Administration. It is a great facility to recover a corrupt master database file. For using this management tool user needs to first have Microsoft Web Platform installed on their system. After that, install the MS SQL Management Studio Express according to the SQL Server version.
- Step 1: Launch the Management Studio Express from C:/Program files/Microsoft SQL Server / SQL Server Management Studio Express.
- Step 2: Now, Type the Windows Authentication login credentials
- Step 3: After that, Expand databases and find wt_shed. Right-click on this file and then go to Properties >> Options
- Step 4: Next, Scroll down to the bottom of the page. Then, modify Restrict Access to SINGLE_USER
- Step 5: Now, select OK button and repeat step#2 for wtMaster part of the database
- Step 6: From here, select New Query from the toolbar. After that, copy and paste the commands below into the query pane.
- dbcc checkdb(‘wtMaster’,REPAIR_REBUILD)
- dbcc checkdb(‘wt_sched’,REPAIR_REBUILD)
- Step 7: Now, select Execute for repairing the SQL Server
- Step 8: After that, follow the steps 4 and 5 and change back the Restrict Access from SINGLE_USER to MULTIPLE_USER
Solution#2 Using The Rebuild Wizard
This is also a prominent solution for fixing corrupt master database or MSDB files. This may prevent the SQL Server from starting. In such cases, users can rebuild the master database and restart SQL Server from a previously backed up version. The rebuild wizard is used to rebuild the master database.
This is a long and time-consuming step as SQL Server in being installed again. It requires a lot of patience and technical knowledge. Users must take a precautionary backup of server files before rebuilding it from scratch. Follow the steps below in order to rebuild and restore SQL Server. The Rebuild Wizard or Rebuildm.exe file can be found on the default location \Program Files\Microsoft SQL Server\80\Tools\BINN directory.
- First, Launch the Rebuild Master by double clicking Rebuildm.exe
- Now, In the Rebuild Wizard check whether the collation settings and database location are correct. And then, click Rebuild
- Next, click Yes on the confirmation box. Now the rebuilding process has started
- After that, a message box will appear at the end of the rebuilding process. Click OK to move to the next step
- Now, Go to Command prompt and enter the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.
- After that, type sqlservr.exe –c -m to launch the SQL Server Configuration Manager
- In the SQL Server Configuration Manager, go to the Startup Parameter tab.
- Now, type ‘-m’ in the startup parameter box
- Next, Click on Add and then Apply to confirm the changes
- After that, restart SQL Server
- Having done that, type the following command in the query pane to rebuild master database in SQL Server
Start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 sapwd=
This will rebuild the corrupt database in SQL Server including the master database file. Now, restore the master database file, users need to have the latest backup of master.mdf and master.ldf files. Launch the SQL Server in single user mode and follow the steps below for recovering SQL server from a backup file.
- Open SQL Server Configuration Manager and select SQL Server 2005 Services
- Next, Go to SQL Server instance and right click on it. And then, click Properties
- Now, On the SQL Server Properties Window, select the Advanced tab.
- Now, Go to the startup parameters tab and prefix ‘-m’ in the startup parameters text box. Remove the prefix after the process is complete.
- Now, Launch the SQL Server and type in the following command to restore SQL Server from a master backup file.
- RESTORE DATABASE master FROM DISK = ‘C:\BackupLocation\master.bak’ WITH REPLACE; GO
Solution#3 Alternative Solution- Using Third-Party Application
At times, the manual solution can be very difficult to understand and follow. A thorough knowledge of working as a system administrator of SQL Server is required. Hence, users look for a workaround from the tedious and irrelevant procedures. Users opt for an alternate solution to performing the lengthy steps. This involves downloading a third-party application like SQL Database Repair. It is a reliable and efficient solution for recovering corrupt database files of SQL Server. It is very easy-to-use and can be used by both a technical and a non-technical user.
Wrapping It Up
Corruption in the master database is a major issue that can prevent users from accessing SQL Server. Once the master database is corrupted all the essential information regarding login details are lost as well. Hence, users can use the manual solutions suggested in this post for SQL Server Repair Rebuild Usage. An alternative solution is also suggested for non-technical users. A professional solution like SQL Recovery software can make the entire task easier and simpler.