How to Restore Master Database in SQL Server? Reasons & Case For Recovery

There might be various reasons why you actually require to recover your SQL master database. Take for an instance that you have just deleted something important from your SQL database but later you want it back. It will be very convenient to restore it from a master database. But, suppose your master database has been damaged or inaccessible, how will you regain the data from it? Therefore, in this article, we’ll guide you through various methods to restore master database in SQL server without a hitch.

Your master database may have corrupted because of failure of any hardware or software. Or many more but we will leave it for some other article. For now, let’s start with how to restore the master database in SQL server.

We will be discussing three cases in which you can restore your master database

• You have a master database that is strong and you hold a backup, all you have to do is travel back in time and get back to a known state.
• Your master database is damaged or unstable, but you possess a backup which is still in good health.
• Your master database is corrupted and you do not have any backup from which you could restore.

Note: If your database is highly corrupted, the first thing you need to is to make copies of your msdb and model database records.

Let’s start..

The process to recover the master database of SQL Server

Solution 1. Restore the present master database from backup

Restoring the master database from the SQL server is not as easy as it seems because it comprises the info about various databases. Consequently, if you have the backup of the master database, follow the below-mentioned steps to restore easily and quickly.

#1 Run the SQL Server Configuration Manager.

#2 Right-click the SQL Server service and click on “Properties”.

#3 After that select the startup parameters tab.

#4 Type “– m” in the “Specify the startup parameter” text box and click the Add button, and apply.

#5 Restart your SQL Server.

After performing these steps, you will enter into the single client mode, which is vital to recover SQL master database from the backup. After you activated your Single client mode, you can use the command line in SQL Server Management Studio to restore your SQL server master database from backup.

Follow the below steps to use the command line tool

#1 Navigate to …/Tools/Bin directory and look for sqlcmd.exe.

#2 Once located, run the sqlcmd.exe file.

#3 Use the – S flag for a private connection. But, if you have a public connection, then use the – U and – P flags.

#4 After doing this, you will be able to restore master database backup easily.

#5 Now remove “– m” flag services from the SQL Server and restart the SQL server to return to the multi-client mode.

Solution 2. Rebuild the SQL Server master database from backup

If you have noticed that you don’t have a stable master database then you need to develop one. For this, you need to utilize the backup, which might be the incomplete backup or you are not 100% if the backup is perfect. There are two ways you can use to rebuild your master database.

Database for 2205 and 2008 can be recreated at once. Whereas for 2008R2 and newer you need to use layout option that can overwrite master database only.

Utilizing setup to Recreate System Databases

Follow the below steps-

#1 Navigate to the bootstrap directory @”C:\Program Files\Microsoft SQL Server\(abc)\Setup Bootstrap\”.(abc represent the version of the SQL server, so replace it with your server version.)

#2 Execute the command. (Administrative rights is required to execute any command)

#3 Once you have the master database to work with, recovering it from a backup becomes easier. Now close the administrations and replace the master database and model database with the copy. But how to recover the master database if you don’t have any backup. Let’s discuss it in the next step.

Using the Template Master database

2008R2 master database recovery functions very differently. Instead of using T-SQL contents, there are layout or templates database create when installing, which are capable of replicating over the database and exchange log records of system files by the setup.exe/REBUILDDATABASE option. If your msdb and model database is in a healthy state, then it will be easier to manually copy the layouts or template files to the master database to where they actually reside. Despite re-building all three system database and restore the msdb and model from backup.

The template documents can be found at “C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Bin\Templates.”

Note: xyz, in the above path represents the version of MSSQL.

If you have a backup of the SQL master database, then you can restore your data by following steps from case 1. However, if you don’t have one then you should keep reading to figure out how to restore the master database in SQL server.

Solution 3. Reconstruct Backup from the SQL Server Master database

Reconnecting the databases

#1 Connect to your SQL server using SQL Server Configuration Manager and your Admin account. The first thing you will observe when you extend the database tree that it is empty. This happens because the master database comprises all the databases details and now it is lost.

#2 To reconnect the server’s databases, perform the below steps:

#3 Right click on the databases tab and select “Attach/Connect”

#4 Then click on “Add” on the Attach Database dialogue box.

#5 Select the .mdf file for all the database. You will find .ndf and .ldf file when you select the .mdf file. (only if they are not moved)

Restoring other system objects

While re-building or restoring several other objects will be lost which were stored in the master database. Objects like logins, endpoints, and connected databases which were lost without any backup are now impossible to recover. But on another note, you can use SQL Server Configuration Manager to restore the objects, create scripts and send to the restored server.

If nothing works for you use SQL Repair Tool

When none of the above solutions could restore master database in SQL server, then use a good SQL database repair tool. And I would like to recommend you SysInfoTools SQL Repair tool.

This is evidently one of the best tools that you can use to recover all your crucial data SQL Database Server. As this tool provides high accuracy recovery of file objects including tables, views, stored procedure, programmability, triggers, default, and functions. Following are some features of this tool:

• Dual recovery modes- Standard Mode and Advanced Mode to recovery MDF file.
• It can save recovered data in SQL Server database format or SQL Server Script format.
• Easily repairs corrupt MDF and NDF files.
• Very well compatible with SQL Server ROW-compression and PAGE-compression.
• Allows users to export schema and data from corrupt MDF file.
• Preview recovered data before saving.
• Support BLOB, image, XML and large page data.
• Compatible with all versions of Windows.
• Also support SQL Server version 2000, 2005, 2008, 2012, 2014, 2016.

Conclusion

Use the above methods to restore master database in SQL server. It’s up to you which path you follow to recover your lost data. But, if you want a quick and better solution then give a try to SQL Recovery tool. Hope this article will help you to restore your deleted data.