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.
#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.
Use the -f or -m as a startup parameters
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.
SQLCMD -S <instance> -E -d master
you need to replace <instance> with your SQL server instance name.
RESTORE DATABASE master FROM <backup_device> WITH REPLACE
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.
Recommended by LinkedIn
SELECT * FROM sys.configurations;
SELECT
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;
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'));
Now let’s rebuild the system database.
C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\SQLServer2024
setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]
C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Logs.
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.
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.