How to Restore Master Database in SQL Server?

Written By Aswin Vijayan

Approved By Mack John

Published On January 18th, 2024

Reading Time 5 Minutes Reading

Your SQL Server Master Database might be damaged due to several known and unknown reasons like – instant failure of any hardware or software, abrupt system shut down, virus attack etc. In this article, we will deal with manual ways to recover the lost master database and restore master database in SQL Server without much hassle. We will also perform the recovery of SQL database by using a third party utility i.e. SQL Recovery Tool.

Manual Method to Restore a Current SQL Server Master Database from Backup Situation 1:

SQL master database contains the data about the various databases and hence to recover it from backup is not an easy task, it is tricky. When you have a backup of the SQL master database, you can perform the master database recovery by following the below procedure-

The SQL Server Configuration Manager is started.
Select Properties after you right-click on the SQL Server service. Next, select the Startup parameters tab.
Type –m in the text box area and then click on Add button -> Apply.
The SQL Server is restarted.

You will enter a single-client mode and from here you can proceed with the recovery of the SQL master database from the backup. Once the single-client mode is activated, then the command line SQL Server Management Studio can be used to recover the master database from backup. Perform the below-given steps to use the command line tool-

Go to …/Tools/Bin directory.
Locate the sqlcmd.exe file
Execute the sqlcmd.exe file
If you have a trusted connection, use –S flag. If the connection is public then use –U and –P flag.
Use the SQL Server Configuration Manager to remove the –m flag.
Restart the SQL server and go back to multi-client mode.

Situation 2: To Restore Master Database in SQL Server from Backup

To build a master database you will need to use the backup which you are not sure that it might be the complete or perfect one. Master database can be rebuilt by two ways- either by recreating all of the system databases for the 2005 and 2008 versions or use the layout option to overwrite only the master database in 2008R2 version.

Recreate System Databases using the Setup

Go to the directory- C:\Program Files\Microsoft SQL Server\(XYZ)\Setup Bootstrap \. Here XYZ is the version of your SQL Server.
Execute suitable commands to recreate the database. You must have administrative rights and hence you will be asked to enter admin ID and password.

Recover the master database from the backup if you have one. Close down the administrations and supply the model database documents and clear MSDB with the duplicates you spared off. In case of you not having any backup, situation 3 will help you recover SQL database.

Master database template use

The layout databases made at the introducing time are replicated over the database and exchange log records of the system databases by the setup.exe/REBUILDDATABASE choice. The location of the layout documents is C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Bin\Templates. Use situation 1 to restore the SQL master database in case you have a backup of it. In case you don’t have a backup then the next situation 3 will help you get one.

Situation 3: To Reconstruct Information from the SQL Server Master Database

Use the SQL Server Configuration Manager and the Admin account to connect to SQL Server. You will observe that the database tree is empty when extended and this is because the master database contains the databases and area data that have been lost due to corruption. Reconnecting of databases can be done by-

Right-click on the database tab.
Select Connect…
On the Attach Database dialogue box, click on Add
The MDF file for each database is to be selected. (the .ndf and .ldf documents will be discovered if they have not been moved when you select the MDF file)

Different object types like the logins, endpoints and connected databases might have been lost from the master database. These can be regained by restoring the system objects. The SQL Server Configuration can be used to restore objects, make scripts to alter and send to the restored server.

Limitations of the Manual Method

The manual method to restore master database in SQL Server is too tedious and cumbersome with no guarantee of the desired result.

Professional Approach

SQL Recovery tool is a great automated utility to restore master database. in SQL Server The user can recover the primary and secondary database i.e. MDF and the associated NDF files. Using the tool users can also recover the tables, views, functions, triggers, procedures, etc. This tool restore master database in SQL Server 2019, 2017, 2016, 2014, 2012, 2008/2008 R2, 2005, 2000 versions.

Read More: How to Resolve SQL Server Page Level Corruption?

Summation

This blog explains the process to restore master database in SQL Server and the limitations associated with the manual method. It suggests the use of a professional tool for best-guaranteed results.