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.

How to Determine Whether Master Database Is Corrupt or Not?

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:

STEP 1: Possibility of input/output errors or segmentation faults

Possibility of input/output errors or segmentation faults STEP 2: Unsuccessful to start SQL server

Unsuccessful to start SQL server STEP 3:Report generation by Database Consistency Checker Utility

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.

Pre-rebuild tasks

When you wish to rebuild the master, model, msdb, and the tempdb system database, then these databases are dropped from SQL Server and recreated at their original location. If you mention a new collation in the rebuilt process, then the database is created with the collation setting.

You should perform some prerequisites before rebuilding the master database to make sure that the database adapts itself to the current settings.

Select all server-side configuration values. Record the hotfixes attached to the SQL Server. Apply these hotfixes to the database after restoring the database. Keep the record of the location of all data and log files of the system databases. The rebuilding process will help to install all the system databases to their exact original location. 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. The copies of the master, model, msdb data, and log templates are required to run the rebuild process. The copies are present on the local server at the default location - C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Templates

Post-rebuild tasks:

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 belongs to the replication Distributor, then you should restore the replication database. Move the system databased to their respective locations where they were saved previously. Check that the server-side configuration values are matching the values present previously.

Rebuild:

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.