How to Rebuild Master Database in SQL Server
Introduction of Master Database Rebuild Tools
The master database plays a crucial role in SQL Server, serving as the primary configuration database, and typically existing in two files: the MDF file (master.mdf) containing schema and data, and the LDF file (mastlog.ldf) storing transaction logs. If the master database becomes corrupted, deleted, or damaged and cannot be restored from a backup, Microsoft SQL Server will be severely impacted. As a Microsoft SQL Server administrator, it's essential to know how to fix a corrupted master database using built-in tools like the Rebuild Wizard and third-party tools like Qiling MS SQL repair tool.
Both SQL Server rebuild, restore, and recovery tools are designed to help administrators resolve common issues with MS SQL servers that require a rebuild, restore, or recovery. There are three general scenarios where either tool may be needed to get a server back to its initial health state: a rebuild is necessary when the server's structure is damaged, a restore is required when data is lost or corrupted, and recovery is needed when the server is in a failed state.
- Reboot SQL server after a power surge, but failed.
- A sudden system shutdown makes the server unable to start.
- An MDF file became corrupted due to a virus, user error, or hard disk issues.
NOTE: To restore the master database, it's crucial to take a full backup first to ensure no data loss in case of any issues during the restoration process. This is because the master database contains information about all databases in the instance and is required for startup. Taking a full backup of the master database before proceeding with further troubleshooting solutions is recommended, as it provides a safeguard against potential mishaps.
Rebuild Master Database in SQL Server #Rebuild Wizard
The Rebuild Wizard utility (rebuildm.exe) is located in the default user directory: Program Files\Microsoft SQL Server\ 80\Tools\Binn subdirectory, where you can start the Rebuild Wizard by double clicking on the program.
Step 1: After verifying the information, click Rebuild to let the utility rebuild a brand new master database for your SQL server.
Step 2: To recover from a failed database, after you're returned to a good master database, log in a single-user mode. To start the server in a single-user mode, run this command on the command prompt: mysqld --skip-grant-tables --skip-networking --single-login. This will allow you to access the database without the need for authentication, making it easier to perform recovery operations.
sqlservr.exe –c -m
Step 3: To configure SQL Server to run as a service, open SQL Server Configuration Manager, navigate to the Startup Parameters section, and add -m to the parameter box, then click Add and Apply.
Step 4: The syntax to rebuild the master database in SQL Server 2005 is: `ALTER DATABASE [master] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET MULTI_USER WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 0);` `DBCC DBREINDEX ('master', 'ALL');` `DBCC DBREINDEX ('master', 'ALL', 1);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET READ_ONLY WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET READ_WRITE WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 0);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;` `DBCC DBREINDEX ('master', 'ALL');` `DBCC DBREINDEX ('master', 'ALL', 1);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET MULTI_USER WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 0);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET READ_ONLY WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET READ_WRITE WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 0);` `DBCC DBREINDEX ('master', 'ALL');` `DBCC DBREINDEX ('master', 'ALL', 1);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;` `DBCC DBREINDEX ('master', 'ALL');` `DBCC DBREINDEX ('master', 'ALL', 1);` `DBCC SHRINKFILE ('master_log', 1);` `ALTER DATABASE [master] SET MULTI_USER WITH ROLLBACK IMMEDIATE;` `DBCC SHRINKFILE ('master_log', 0);` `DBCC
Start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 sapwd=< NewStrongPassword>
After completing the four steps of operation, you should be able to rebuild the corrupt 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 that can be used to restore corrupt MDF/NDF files, fix database log files, and recover corrupted database objects such as tables, indexes, triggers, keys, views, stored procedures, and functions. It is compatible with all operating systems and server editions and is designed to be used after a database corruption issue occurs.
Besides restoring a corrupt or failed SQL Server due to master database issues, this tool also offers advanced features for locating and restoring deleted data and records. Additionally, it provides flexibility in saving a repaired database, with options to export it in four different 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 initiate the repair process.
Note: To use this software, you need to stop the SQL Server service.
Step 2: Repair the corrupted database
- The software displays all recoverable items in a tree-like structure, with items 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
- To export database objects or items, you can choose to either export them to a database or export them as scripts. This allows you to either transfer the database structure and data to a new database or to create a set of scripts that can be used to recreate the database objects and data in another environment.
- If you choose "Export to database", you'll be prompted to enter the necessary details and select the target database for the export.
- To initiate the repair process, a window will pop up asking for credentials to connect to the server and the destination to save the recovered items. Click "OK" to proceed.
Note: You need to restart the SQL Server service before clicking "OK".
Comparison and Conclusion
If your master database is severely corrupted or destroyed and cannot be restored from backups, the rebuild.exe and Qiling SQL software can be used to rebuild it to its default state. This can be a lifesaver in emergency situations.
Using the Rebuild Wizard can be a lengthy and complex process that requires reinstalling the SQL server from scratch, making it a time-consuming and tedious task. In contrast, the Qiling MS SQL repair tool offers a more straightforward approach, providing a user-friendly GUI interface that allows users to specify the source files and initiate the repair process with just a single click, making the recovery process much easier and automated.
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