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.
Prerequisites
- Ensure you are a member of the sysadmin fixed server role to rebuild the system databases.
- Keep a track of the hotfixes applied to your server instance and the current collation. You will need to reapply the hotfixes after completing the rebuild process.
- When the system databases are rebuild, the databases are installed at the original location. So, if you?ve moved the data or log (.ldf) files of a system database to another location, you will need to move the files back again. And so, you must record the current location of the data and log files.
- The template files are required during the rebuild process. Ensure that the copies of the master, model, and msdb data and log template files exist on the local server. You can find the templates files at:
C:\Program Files\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\MSSQL\Binn\Templates
Steps to Rebuild System Databases in SQL Server
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:
- Before initiating the rebuilding process, take a FULL backup of all the system databases and user databases. You may need to restore the backup to avoid any data loss. For further details, see Back Up and Restore of System Databases (SQL Server).
- Go to your installation media drive to copy the location of the setup.exe file on the local server on your system. By default, the setup.exe file is placed in:
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.).
- Now open command prompt as an administrator and run the following script:
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.
- On completing the rebuild process, you may have to restore the system databases from the backup taken in Step 1. This usually happens when you need to recover any (lost) user databases that were present before the rebuild process. Once the restore process is complete, SQL Server will shut down. Restart the server and check if you can access the databases.
To Conclude
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.
