Rebuild and Restore Master Database in SQL Server - Easy Guide

A Master Database is a primary configuration database that acts as a main system database in SQL Server. It stores metadata of instances of SQL Server and all the information of databases like login credentials, linked servers, and location of all the database files. There are many other system databases such as tempdb, msdb, etc., which are required for the normal functioning of SQL Server. In the case of damage or corruption of the master database, it can lead to the failure of a complete SQL Server. We will restore master database in SQL Server to get of from the above problem.

Restoration and Rebuilding of the Master Database depends on the situation of the instance of SQL. A user can restore the master database only if the SQL Server instance is opening, else you need to rebuild it.

How to Know If the Master Database Is Corrupt?

As a database administrator, it will be an important task to identify the signs that show the corruption of the master database. Here are some of the common problems that lead to the inaccessibility of the master database.

• Fail to start the instance of SQL Server.
• Frequently freezing or crashing of the database at the time of performing any operation, which includes Master Database.
• Unable to connect with database or server.
• Stuck in a state where only restoration and rebuilding of the master database.

#1: Manually Restore the Master Database in SQL Server

Here in this section, we will look into how to restore a Master Database in an SQL Server.

• Start the SQL Server instance in single-user mode.

Use the -f or -m as a startup parameters        

• Run the code on the command prompt. Change MSSQLxx.instance with the proper folder name.

cd C:\Program Files\Microsoft SQL Server\MSSQLxx.instance\MSSQL\Binn
sqlservr -c -f -s <instance> -mSQLCMD
-mSQLCMD allows only sqlcmd can connect to SQL SERVER.
Use '-s MSSQLSERVER' for the default instance name.
-c starts the SQL Server as an app. to bypass the Service Control Manager for quick startup.        

• To Connect with SQL Server, you need to use SQLCMD in another command prompt window.

SQLCMD -S <instance> -E -d master        

you need to replace <instance> with your SQL server instance name.

• Use the Restore Database SQL statement to back up the master database.

RESTORE DATABASE master FROM? <backup_device>? WITH REPLACE        

• Restart the SQL server instance.

• Now, restore the other databases.

If the SQL Server instance doesn’t start due to any reason like a damaged master database. First, we need to rebuild the system database.

#2: Rebuild the Master Database in SQL Server

Before building the system database, we need to perform the below steps.

• Record the server-wide configuration.

SELECT * FROM sys.configurations;        

• Record all the hotfixes and collation to reapply these after rebuilding the system database.

SELECT
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;        

• Record the log files and the location of all data.

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));        

• Locate the current backup of the master, model, and msdb databases.
• Locate the current backup of the distribution database, if the instance is configured as a replication distributor.
• You must ensure that you are a member of the sysadmin fixed server role.
• You must verify the copies of the master, model, msdb data, and log files exist on the local server.

Now let’s rebuild the system database.

• Creating an installation media, insert SQL Server installation media into the disk drive or locate the location of setup.exe file.

C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\SQLServer2024        

• Now run the setup command in the command prompt with Administrator privilege.

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]        

• Once done, check the log file to verify the completion process.

C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Logs.        

• Restore all the recent backups of the master, model, and msdb databases.

Now, we will cover a section that can repair the master database corrupted in SQL Server with the help of the efficient tool.

#3 Automate Method to Rebuild Master Database in SQL Server

Cigati SQL Recovery Tool would be helpful to restore the SQL Server Master Database. It can recover both MDF and NDF files without altering the data. This tool supports all the versions of MSSQL Server files to recover them and lets users preview the recovered file before saving. It provides two different types of Recovery modes (Standard & Advanced) which deal with many types of corruption. Users can save the recovered data into SQL Scripts and SQL Table in CSV format. Let’s see the steps on how to use the tool.

Download Free Trial | Buy Now

Conclusion

The master database is a primary configuration database in the SQL server. Any faults in this database can lead to the failure to start the SQL Server. You can restore master Database in SQL Server which will be the best option to get back the database. The restoration process will only be performed if the SQL Server starts, if not, you need to rebuild the system database. To help users easily restore the master database, we have discussed an automated method using SQL recovery tool. You can opt for any of the upper methods to repair Master Database corrupted in SQL Server.