How to Rebuild Master Database in SQL Server
Introduction of Master Database Rebuild Tools
A master database plays an indispensable role in SQL server, because it's recognized as the primary configuration database, and it usually exists in forms of two files - an MDF file and an LDF file. As we know, the MDF file (master.mdf) is the primary database file that contains schema and data, whereas the LDF file (mastlog.ldf) stores the parallel transaction logs. When the master database gets deleted, corrupted or damaged in some way and cannot restore from the backup, then Microsoft SQL server would greatly break down. Being a Microsoft SQL server administrator, it's essential to know how to fix a corrupted master database with the tools already at hand, including the built-in Rebuild Wizard and third-party Qiling MS SQL repair tool.
Both tools are supposed to help administrators deal with commonplace MS SQL server problems that need a rebuild, restore or recovery. There are three scenarios, in general, in which you may need to apply either tool to get a server back to its initial health state.
- Reboot SQL server after a power surge, but failed.
- A sudden system shutdown makes the server unable to start.
- TheMDF file corrupteddue to a virus, misoperation or hard disk errors.
NOTE: It can be tricky to restore master database because it contains the information about all the databases in the instance and it is required to startup. So, we recommend you take a full backup of the master database before proceeding with the next troubleshooting solutions, in case of any mishap. See three SQL server recovery models.
Rebuild Master Database in SQL Server #Rebuild Wizard
The Rebuild Wizard utility (rebuildm.exe) is located in a default user directory:Program Files\Microsoft SQL Server\ 80\Tools\Binn subdirectory. Navigate to the path and double click on the program to start the Rebuild Wizard.
Step 1: Upon verifying the information, click Rebuild to let the utility to build a brand new master database for your SQL server.
Step 2: After you're returned to a good master database, log in a single-user mode this time. To start the server in a single-user mode, run this command on the command prompt.
sqlservr.exe –c -m
Step 3: Next, open SQL Server Configuration Manager and switch to the Startup Parameter section. Specify -m in the parameter box and click Add. Then click Apply.
Step 4: Type the following syntax to rebuild the master database in SQL Server 2005.
Start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 sapwd=< NewStrongPassword>
After the four steps of operation, you should be able to rebuild the corrupt the master database together with model and msdb on the same disk.
Rebuild Master Database in SQL Server #Qiling MS SQL Recovery
Qiling MS SQL Recovery is a third-party SQL repair utility, so you need to download and install it for use. With great compatibilities in all operating systems and server editions, Qiling MS SQL Recovery is mainly designed for restoring corrupt MDF/NDF files, fixing database log files and recovering corrupted objects liketables, indexes, triggers, keys, views, stored procedures, functions, and more.
Apart from the capability of restoring a corrupt or failed SQL Server resulted from master database problems, it has added powerful features of locating and restoring deleted data and records. What's more, it's flexible for you to save a repaired database, with options of 4 formats: MSSQL, CSV, XLS, and HTML.
Step 1: Select the corrupted database for recovery
- Launch Qiling MS SQL Recovery.
- Select the corrupted database file by clicking "Browse" (the two dots) or "Search".
- After selecting the file, click the "Repair" button to start the Analyzing process.
Note: To use this software, you need to stop the SQL Server service.
Step 2: Repair the corrupted database
- The software displays all the recoverable items in a tree-like structure. The items are shown in a left pane.
- Select the desired component to be recovered. From the window, click the "Export" button.
Step 3: Export to the database or as scripts
- Choose to export the database objects to database or export the items as scripts
- If you choose "Export to database", enter the information required and choose the target database.
- A window appears up asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to begin the repairing process, click "OK".
Note: Before clicking "OK", you need to restart the SQL Server service.
Comparison and Conclusion
In the event that your master database is corrupted, or destroyed badly and cannot restore from backups, the rebuild.exe and Qiling SQL software can be of great help to rebuild it to its default state.
Choosing Rebuild Wizard, it's going to be time-consuming, tedious and somewhat sophisticated to rebuild a master database, which usually requires reinstalling the SQL server from scratch. On the contrary, Qiling MS SQL repair tool is comparatively easy to initiate the troubleshooting in an automated way. It provides a GUI interface that allows users to specify the source files, and it asks for only a click of Repair for a complete recovery.
Related Articles
- Download 2022 Best SQL Server MDF Viewer (for Free)
- [Solved] MS SQL Server Database Corruption Repair
- SQL to XML | How to Export Data from SQL Server to XML
- SQL Database Recovery Pending Access Denied