The significance of a Master Database in the context of an SQL server cannot be overstated, as it houses vital configuration information for the SQL server. The primary configuration data of the master database is stored in a physical file known as MDF files, while parallel transaction logs are saved in the masterlog.ldf file. This repository includes crucial data such as user and login details, along with pointers to all the databases. In summary, the smooth operation of your SQL server depends on the integrity of the master database; any issues with it can hinder your ability to start and work efficiently.
How to Determine Whether Master Database Is Corrupt or Not?
As a system administrator, it's crucial to be aware of potential indicators that could signal corruption or damage in your master database. Below, we've outlined some of these symptoms:
- ssibility of input/output errors or segmentation faults
- Unsuccessful to start SQL server
- Report generation by Database Consistency Checker Utility
Several circumstances can lead to corruption or loss of your master database. A power surge, for instance, can force the SQL server to restart. However, in such cases, the SQL server may fail to start after the rebooting process. To determine the cause of this, you can examine the error log, as depicted in the image below.
In this scenario, your SQL server will encounter a refusal to start due to a corrupted master database. The recommended solution involves a multi-step process: initially rebuilding the master database, then initiating the SQL server, and ultimately restoring the database backup. However, it's important to note that the reconstruction of the corrupt master database is a challenging, laborious, and time-intensive task. It assumes a strong command of technical expertise as it entails a complete reinstallation of SQL server, demanding a significant investment of time and patience.
Prior to embarking on the process of reconstructing, rejuvenating, and reclaiming your master database in SQL Server, it is crucial to establish a strategy for addressing potential corruption issues. Subsequently, diligently follow a systematic approach to ensure a secure recovery.
Note: However, it is strongly recommended to take a full backup of master database, before rebuilding or restoring the master database – in order to avoid any chances of odd scenarios.
Pre-rebuild tasks
When you decide to reconstruct the master, model, msdb, and tempdb system databases in SQL Server, these databases are first removed and then regenerated in their initial locations. If you specify a new collation during the reconstruction, the database will be created with the specified collation setting.
Before initiating the master database rebuild, it is essential to complete certain preparatory steps to ensure the database aligns with the current configuration.
- Select all server-side configuration values.
- Capture the SQL Server's attached hotfixes and proceed to apply them to the database following the restoration process.
- Maintain a comprehensive record of the precise locations of system database data and log files. This record will prove invaluable during the rebuilding process, ensuring the successful reinstallation of system databases to their original locations.
- Check the latest backup of master, model, and msdb databases.
- Make sure that you have the permissions to rebuild the databases. Only the sysadmin server role group member can rebuild the database.
- To execute the rebuilding process, you need duplicates of the master, model, msdb data, and log templates, all of which can be found on the local server in their default location.
- C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Templates
After the completion of master database rebuilding, you need to follow the below tasks:
- Restore the most-recent full backups.
- If the SQL Server instance is part of the replication Distributor, it is necessary to perform a restoration of the replication database.
- Move the system databased to their respective locations where they were saved previously.
- Ensure that the server-side configuration values align with the previously existing values.
Recovering your compromised master database can be a delicate operation, requiring utmost care due to its interdependence with other databases within the instance. To facilitate this process, you can utilize the Rebuild Wizard utility (Rebuildm.exe), conveniently located in the default user directory:
\Program Files\Microsoft SQL Server\80\Tools\BINN
To start Rebuild Wizard utility, simply open the program by double-clicking on it, and this is how it appears:
Please specify the preferred collation setting and indicate the file location on your database server. Additionally, ensure you have the x86 directory from the SQL CD and direct it to your local hard drive for quicker and more convenient recovery.
Upon verifying the information, simply hit ‘Rebuild’ button to let this utility build a brand new master database for your server.
Once you've successfully established a well-constructed master database, you can initiate SQL Server in single-user mode by executing the following command in the command prompt:
sqlservr.exe –c -m
Then open SQL Server Configuration Manager and go to the Startup Parameter tab and specify –m in the parameter box to add and apply changes. Then restart the SQL server.
Type the following syntax to rebuild master database in SQL Server 2005.
Start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 sapwd=< NewStrongPassword>
The above mentioned procedure will help you to rebuild the corrupt databases (including model, msdb and master database) that are available on the same disk.
In order to recover the master database files, it is essential to possess the most recent backups of the master.ldf and MDF files. Initiate the SQL Server in single-user mode, and meticulously adhere to the following steps:
- Launch “SQL Server Configuration Manager” and then select “SQL Server 2005 Services”
- Go to the SQL Server instance, and perform right-click operation and select “Properties” option.
- Select “Advanced” tab on the “SQL Server Properties” window. Go to Startup Parameters box, and then add “-m;” prefix to the already existing parameters.
Note: Ensure that the prefix is then removed when the desired task is completed. Then you can connect to the SQL server by running the following command to restore the database.
RESTORE DATABASE master FROM DISK = ‘C:\BackupLocation\master.bak’ WITH REPLACE;GO
A third party MDF repair tool can be of great help to rebuild and restore damaged master database of SQL server. Kernel for SQL Database Recovery is a reliable third-party software that lets you to repair corrupt database and restore complete database objects (Triggers, Rules, Functions, Tables, Stored Procedure) with absolute data integrity. It takes no time in repairing MDF files (with batch file recovery) and restore the master database and system databases without taking any pain. The advanced software algorithms can automatically detect database version and lets you to restore the data while SQL server is still running. Let us know it’s working through screen displays with sequential steps. First, you need to install the SQL Recovery tool.
- Commence the software application and navigate to the "Browse" button for importing your SQL Server Database file. Subsequently, choose your scanning preferences, SQL database version, and initiate the recovery process by clicking the "Recover" option.
- Retrieve all SQL database objects, and then choose the specific object you want to inspect. Pick the desired objects from the left panel and click the "Save" option.
- On the Saving Mode page, you are presented with two saving alternatives. You can opt for either the Live SQL Server option or the Batch File option. When choosing the Live SQL Server option, you are required to provide specific details such as the server name and database name. Alternatively, when selecting the Batch File option, you must specify the destination path for saving the batch file. Simply click on the "Browse" button to input the desired saving path, and then confirm by clicking "OK."
- The SQL database objects saving process will get start.
- The saving of SQL database objects is shown as completed with the message. Click OK.
- Thus, your selected SQL database objects have got restored at the specified saving location.
Summary
SQL Server database security is main concern for its administrator and in case they observe any symptoms of corruption, seeking instant solution is what they should do. They can try rebuilding and restoring the SQL database manually of if it did not work, a helpful third-party tool Kernel for SQL Server tool with quick and automated solution is at your service.
