SQL Server maintains system databases (master, model, msdb, and resource) for a server instance to operate. However, you cannot connect to a server instance if there are corruption issues in the system databases. To bring the server online, you can try to restore the system databases from backups. If there is no recent backup, rebuilding the system databases might help resolve the issue. This blog explains the steps to rebuild the system databases.
Before you begin the rebuild process, there are some prerequisites you need to follow to ensure that the system databases will be restored to their original settings.
C:\Program Files\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\MSSQL\Binn\Templates
Note: Before applying the following steps, check SQL error logs to find more details on why the server instance is not starting. Chances are that the issue might not be with the master database.
Follow these steps to rebuild system databases for a server instance:
C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQLServer2017
Here, ‘140’ is the version number of the SQL Server. You can replace the version with your SQL Server version (‘110’, ‘120’, ‘130’, etc.).
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
In the above command, replace InstanceName with the name of your SQL Server instance.
Also, substitute ‘=accounts‘ with the local Windows account name or the domain name that you want to use to access SQL Server after the rebuild process. This is important because you are going to lose all the logins on rebuilding the master database. When adding more than one account, enter blank space to separate the accounts. And, add a password for the SQL Server account.
For instance, in the REBUILD command, we have replaced the instance name with MSSQLSERVER that we will rebuild. Further, we have added local Windows account name for the SYSADMINACCOUNTS, and entered the password for the SA account. We have removed SQLCOLLATION, as it is not required.
Press Enter and the rebuild process completes without any error. You can verify if the rebuild process completed successfully by checking the Summary.txt log file. This file can be found at the path – C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Logs.
This blog explained the step-by-step instructions for rebuilding all the system databases. It also outlined some prerequisites you must meet prior to rebuilding. For repairing corrupt SQL Server database files (.mdf and .ndf), you can use a SQL database repair tool. Stellar Repair for MS SQL is one such tool that helps repair both .mdf and .ndf files and restore the database to its original form with all the data intact.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.