Top 3 Methods To Restore Master Database in SQL Server

If I am not wrong, the reason behind visiting this page is for searching the methods to restore Master Database in SQL Server. Take it easy! You have landed on the right page. In this blog, I have covered easygoing methods to recover the master database.

Before moving to the solutions, Firstly let us grasp some information on Master Database in the SQL Server.

What is the need for Restoring the Master Database?

There can be many factors in which you may need to recover the Master Database:

• If some crucial data has been removed mistakenly and you want it back. Like a login, linked server, or other system objects.
• You can easily restore data if you have a complete backup of the Master Database.

Caution!! What will happen if your master database has been damaged or there is some hardware or software failure?

Keeping in mind about the users, I had covered all the possible solutions to recover the master database.

So without consuming time let’s discuss all the cases.

Case 1- If you have a good backup of the Master Database, then follow these steps:

• Firstly, Stop the instance
• Then Open cmd(command prompt)
• After this, Run –m, in single-user mode
• Right-click on the SQL service > select properties > startup parameters tab. 
• Then type -m in the top box > click add > apply > restart SQL Server.
• Look for sqlcmd.exe in your …/Tools/Binn directory and run it.
• Use the -S flag if you have a named instance OR -U and -P flags if you’re not using a secure connection. 
• Lastly, You can restore the master database from your backup.

Case 2- If you don’t have a good backup of the Master Database, want to recreate it. Also, wants to restore the Master Database in SQL Servers.

You must run Setup to rebuild all of your system databases for versions 2005, 2008, and  2008R2.

You can use the template option to overwrite only the Master Database for versions 2008R2 and above. Therefore, to run setup follow these steps:

• At first, Open bootstrap directory- “C:\Program Files\Microsoft SQL Server\<nnn>\Setup Bootstrap\<release>” 
• Now, Replace <nnn> with the correct version and release it.
• After this, run the same command by typing your instance name
• After typing your instance name, you will see the windows account which has administrative rights and a secure password for SA. 

Important!! Before entering the below command, make sure you have saved the master database somewhere safe.

Now, Enter the following command in cmd:\setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<admin_account> /SAPWD=<sa_password>]

Now, your Master Database is successfully recreated. Therefore, you can easily recover your Master Database from a backup.

Note: The above method is not applicable for version 2008R2 and above. Therefore, we have to use a Template Master Database for it.

Important Points to Remember while using Template Database:

• In spite of using T-SQL scripts, we use template databases that are created at install time.
• If your master database and model databases are good then it may be easy to manually copy the template files to where they belong.
• Instead of recreating all three system databases and recovering the master database and model from backup.
• The template files can be accessed in the “C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\Templates” directory.

Alert!! The above methods are only useful if you have a good backup of your Master Database. Otherwise, you have to follow the last case that I am going to discuss now.

Case 3- If You want to restore Master Database in SQL Server without having a backup. 

Remember these steps:

• Connect to the server using SSMS and the Admin account. 
• Instantly you will observe that when you expand the database tree, it is empty because the master database contains all the information and it is gone.
• But the databases are there, we just need to inform SQL Server of their location.
• Now, you will need to write down the location of all the databases and transaction log files for each database.
• You need to attach them manually using the T-SQL code with the correct database name or SQL Server Management Studio’s database command using the Database menu.

Steps to find the .ndf and .ldf files:

• Right-click on the database tab
• Click on Attach > Add on Attach Database dialog.
• After selecting the MDF file for each database it will search the .ndf and .ldf files( they must not be moved).

Cannot recover the Master Database yet? No need to worry! Some users find it difficult to use manual approaches to recover a master database( MDF and LDF) files. Considering this situation, I have also mentioned a professional method to recover the Master Database in SQL servers.

If you find SQL Server is unable to run, then there could be a corruption issue in SQL Database files. One of them is the corruption in the Master Database which occurs due to damage in secondary files of SQL Database. If you are unable to recover them from the manual method, then always prefer professional utilities first. So, you can pick SQL Database Recovery Software as the first option to recover corrupted SQL files. Hence, you can easily restore Master Database in SQL Server.

CONCLUSION

In this blog, Firstly, we have discussed the necessity of restoring the master database. Then we have mentioned all the possible conditions to restore Master Database in SQL Server. Lastly, we come to the conclusion that using a professional approach, a user can easily recover Master Databases.

Related Post