A Master Database is highly important for an SQL server, as it holds the primary configuration details of the SQL server. The core information of master database is recorded in a physical file, called MDF files, whereas the parallel transaction logs are stored on to the masterlog.ldf file. This means the total user & login details, and the information of pointers to all the databases are stored on the master database. In brief, if anything happens to the master database, then you can’t start and productively work on your SQL server.
Being a system administrator, it is important for you to know the symptoms that may indicate the corruption or damage in your master database. Some of the symptoms are shared below:
There are various situations in which your master database can go corrupt or missing. Power surge is one such reason that compels the SQL server to reboot, but after the rebooting process the SQL server may not start in this scenario. However, you can check the error log, as shown in the image below to verify the reason of corruption.
In this situation your SQL server would refuse to start with the corrupt master database, so ideally the solution is to first rebuild the master database, then start SQL server and then restore the backup of the database. However, rebuilding the corrupt master database is a complex, tedious and time-consuming activity, and of course it is understood that you hold technical command over it. It can take a substantial amount of time and patience, as it involves reinstalling SQL server from scratch.
Before moving towards the steps to rebuild, restore and recover your master database in SQL server, it is important to have a plan to deal with the issues of corruption and then follow a methodical approach to recover from it safely.
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.
Restoring your corrupt master database is tricky and you need to be fully cautious while its recovery, as all the other databases are connected in the instance. For this, you can use the Rebuild Wizard utility (Rebuildm.exe), which is located on this 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:
Provide the desired collation setting along with the location of files of your database server. Also, you need to provide the x86 directory from the SQL CD and direct it to the local copy of your hard drive to obtain faster and convenient recovery.
Upon verifying the information, simply hit ‘Rebuild’ button to let this utility build a brand new master database for your server.
Now, when you have achieved a good master database, then can start SQL server in a single-user mode. For this simply run this command on 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.
To restore the master database files, you need to have latest backup of the master.ldf and MDF file. Start SQL server in a single user mode and follow the below give steps with complete precision:
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 server 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.