November 9th, 2018
March 3, 2021
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
Note: If your database is highly corrupted, the first thing you need to is to make copies of your msdb and model database records.
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.
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.
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.
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:
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.