Restore master database in SQL Server

master database is a database that keeps all the information at the system level in an instance. For example, logins, endpoints, linked servers, system configuration settings.

Therefore, without a master database, a sql server instance does not work. Therefore, you should regularly back up the master database and other system databases. You can get detailed information about system databases in my article “SQL Server System Databases”

So how do we restore the master database when it is corrupted?

The need to restore the master database usually begins with the failure to open the sql server service as a result of a corruption in the master database. When you want to start the SQL Server service from SQL Server Configuration Manager, you will see an error as follows.

In the article “SQL Server Configuration Manager Settings“, you can access the article that explains the settings that can be made through Configuration Manager.

Do not panic. This error does not always mean that your database is corrupted. Your problem may not be corruption. This error only says that the service is not responding and we need to look at the event log.

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

In Windows Search, we open the Event viewer as follows.

You can see the related error on the right side after clicking on Application under the windows logs as below. In my example, the log file of the master database has been deleted. Therefore, the sql server service cannot start because it cannot find the log file of the master database.

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL12.TESTINSTANCE\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 2(The system cannot find the path specified.)

You cannot start the sql server service without a master database.

First of all, you can start instance in single user mode and try to restore the master database from the cmd command line.

If you cannot succeed, you can restore the master database to another instance in the same version and copy the files.

Or you can try the third way, which is a longer but definitive solution. Rebuild system databases.

Before performing Rebuild system databases, make sure that you have the latest backup of the master, model and msdb databases. Because when you rebuild your system databases, everything will be reset. Then we need to restore not only the master database, also the model and msdb databases. To rebuild the system databases, you can use the article “Rebuild System Databases in SQL Server“.

After reboot, right-click on the relevant instance in SQL Server Configuration Manager, click Properties, and add -m parameter as below by clicking Add. After then stop the service.

Then we write cmd in windows search and run it as an administrator.

And start the sql server service as follows. You must write your own instance name instead of TESTINSTANCE.

After you start the service, if your instance is default instance, you should just type sqlcmd and press enter.

If you are going to connect to the named instance, you must type;

or

Of course, in the above script, you should write the name of your own server instead of servername, and the name of your own instance instead of instancename.

I performed this process for the instance named TESTINSTANCE in the screenshot below. This is an important step. You should not connect to the wrong Instance.

Restore master;

Then we start the restoration process. We perform the restore with the following command. Instead of “C:\MSSQL”, you should write the path where your backup is located.

SQL Server automatically stopped the service when the restore was finished. We need to go to Configuration Manager and remove the -m parameter that we added earlier.

When you connect to the instance after the Restore process, you will see your old databases and login. If you rebuild the master database before you restore it, you will not be able to see your jobs. In such a case, you must restore the msdb database.

You can perform the restore operations of the msdb and model database as normal database restore. There is no extra setting. But, to restore the msdb database, you must stop the SQL Server Agent Service from SQL Server Configuration Manager.

You can find the restore scripts of msdb and model system databases below.

Restore model;

Restore msdb;