How to Rebuild and Restore SQL Server Master Database

In MS SQL Server, master database is the primary database that stores system information. This includes login details, linked servers, endpoints, system configurations, existence of other databases, etc. If the master database gets corrupted or damaged, the SQL Server service or instance may not start. Also, the other databases can become inaccessible.

In this article, we’ll discuss what causes corruption in SQL Server master database and how to rebuild and restore the master database.

Causes of Corruption in Master Database in SQL Server

The master database in SQL Server can get corrupted due to one or more of the below reasons:

• Bad sectors on the hard drive where the master database is located.
• Sudden system shutdown while working on the master database.
• Bugs in MS SQL Server.
• Malware or virus infection in the system hosting the database/server.
• Sudden restart of MS SQL Server instance.

Methods to Restore the Master Database in SQL Server

The following are some methods you can use to rebuild or restore the SQL Server master database. However, before proceeding, ensure the following:

• Select all server-side configuration values.
• Note down the location of all the data and log files in the system database.
• Check whether you have a latest and readable full backup.

Method 1: Repair Server Installation Files using SQL Server Installer

If the server installation files in master database are corrupted, then you can try repairing them by using the MS SQL Server Installer. To do so, follow these steps:

• Locate the SQL Server installer folder on your system and click on setup.exe.

• On the SQL Server Installation Center window, click Maintenance and then click the Repair option.

• Select the server instance you want to repair and click Next.

• In the Ready to Repair window, verify the details and then click on the Repair option.

Alternatively, you can run the SQL Server Installer using the below command in the Command Prompt:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=sa  /SAPWD= MyP983”#3  /SQLCOLLATION=Latin1_General_CI_AS 

Method 2: Restore the Master Database from Backup

If you have an updated full backup, then you can use the RESTORE DATABASE T-SQL statement to restore the master database. Before restoring, make sure you are restoring the backup to the exact version or later version of SQL Server. If there is version mismatch, then you can encounter the error message, saying “Can’t be restored because it was created by a different version of the server (13.00.4435) than this server (14…)”.

Follow the below steps to restore the master database in MS SQL Server:

Step 1: Start the Server Instance in Single-User Mode

You can use the Command Prompt to start the server instance in single-user mode. Here’s how to do so:

• Open Command Prompt on your system and run the following commands:

NET STOP MSSQLSERVER
NET START MSSQLSERVER /m

• Next, use the SQLCMD utility to start the SQL Server

SQLCMD -S <instance> -E -d master

Alternatively, you can use the SQL Server Configuration Manager to start the instance in single-user mode. Here are the steps:

• In your system’s search box, type SQL Server Configuration Manager and press Enter.
• In SQL Server Configuration Manager, select the Service instance, right-click on it, and click Stop.

• Right-click the instance again and click Properties.
• In SQL Server (MSSQLSERVER) Properties window,under Startup Parameters, specify the -m parameter, and then click on the Add button.

• Click OK.

The SQL Server instance will start in single-user mode.

Step 2: Restore Backup using T-SQL Statement

You can use the following RESTORE DATABASE T-SQL statement to restore a full backup of the master database:

RESTORE DATABASE master FROM  <backup_device>  WITH REPLACE

Step 3: Perform Post-Restore Tasks

Once you have restored the master database from backup, perform the below actions:

• Restart the server instance normally without using startup parameters.
• Make sure you’ve restored the recent full backup.
• Save the restored master database to the original location.
• Check and match the server-side configuration values with the previous configuration values.

Alternate Method to Restore Master Database File

If you don’t have a full backup or your backup file is not readable, then you can repair the database (MDF/NDF) file using a professional SQL repair tool, like Stellar Repair for MS SQL Technician. It can repair and recover primary and secondary database files without any file size limitations. Also, it can recover data from corrupted SQL database backup (BAK) files with complete precision. The tool can recover all the objects, such as views, tables, procedures, etc., from MDF/NDF files. It saves the recovered data in a new database file and various other formats. The tool is compatible with repairing backup files of all types, such as Full Backup, Transaction Log Backup, and Differential Backup.

Key features of Stellar Repair for MS SQL Technician:

• Repairs primary and secondary database files.
• Repairs severely damaged or corrupted BAK files and recovers all the data.
• Recovers indexes, tables, stored procedures, views, keys, etc. with complete integrity.
• Allows specific recovery of objects.
• Allows to preview recovered data, including views, constraints, etc.
• Supports Windows 11, 10, 8.1, 8, 7 and Windows Server 2022, 2019, 2016, and earlier versions.

Conclusion

If your master database is damaged, you may fail to access your MDF/NDF files or SQL Server instance. You can follow the methods mentioned in this post to restore the master database. If your backup file is corrupted or unreadable, you can use Stellar Repair for MS SQL Technician. It is a professional SQL repair tool that can help you restore the corrupt backup file with absolute precision. This software is compatible with all SQL Server versions.