Rebuild System Databases in SQL Server

In this article, we will talk about system database corruption. It means that one or more system databases are corrupted. Corruption in system databases, such as master databases, is a big problem because SQL Server will stop. Let’s see how to fix this problem. 

About System Databases

System databases are databases used internally by SQL Server and are necessary for its operation.

There are five system databases: 

• Master database: It stores system-level information of the instance of SQL Server. 
• msdb database: It is used by SQL Server Agent for scheduling jobs. 
• Model database: It is used as a template for the creation of a new database.
• Resource database: It stores internal system objects that are associated with the sys schema of every database.
• Tempdb database: It is used as a space to store temporary objects or intermediate result sets.

In the SQL Server Management Studio (SSMS), under the system database folder of the Object Explorer, you can find four of the five system databases.

In particular, the master database is considered the “heart of SQL Server engine” as it contains information, such as processes, locks, remote accesses, etc. It also contains information about other user databases. If this database is corrupted, SQL will not start. 

SQL Server Engine No Longer Starts

We often notice that the system databases are damaged just because the database engine does not start. When we look at the SQL Server error log, this error appears:

Error: 17113, Severity: 16, State: 1.

Error 2 (The system cannot find the file specified.) occurred while opening file

'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain configuration information at startup.

An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The message indicates that the master.mdf file is not accessible.

In this case, we need to rebuild the system databases.

Rebuild System Databases

Note: The following procedure rebuilds the master, model, msdb, and tempdb system databases. We cannot specify the system databases to be rebuilt. The rebuild operation drops and recreates the system databases. Therefore, the changes made in the system databases will be lost and we need a backup to restore. 

Before starting this procedure, we need to check some things.

The rebuild procedure uses the system database template files. So, go to the \Binn\Templates folder and make sure that these files are present.

Next, locate the setup.exe file.

Now, we need to change the actual path to the folder where the installation file is located. 

To do this, open a command prompt and type:

Cd C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019 

Now, we will execute the setup.exe file with some parameters. 

These are some of the parameters:

• /QUIET — performs a silent installation
• /ACTION — sets the value to RebuildDatabase
• /INSTANCENAME — the name of the instance 
• /SQLSYSADMINACCOUNTS — the name of the account (account must have the admin rights in SQL Server)
• /SAPWD — provides a new password for SA login (enable SA account if it is disabled and set up with a strong password)
• /SQLCollation — provides new collation name to SQL Server (optional)

 Now type the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<XYZ> /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=<PWD>

Remember to replace the <XYZ> string with the instance name and the <PWD> string with the password.

Press ENTER to execute the setup. 

If no error message comes out, the procedure is successful. You have recovered your instance and the SQL Server will be functional again. 

Restore Backups of Master and msdb Databases

Now our SQL Server is running again. However, keep in mind that after the rebuilding operation, changes made to the system databases will be lost. 

To get the system back to where it was, we need to restore backups of the master and msdb databases. 

To do this, start the SQL Server in single-user mode.

Open a command-line prompt and then start SQL Server with these arguments:

The -s argument represents the name of the instance. 

With the -m argument, the SQL Server will only accept a connection if the application is SQLCMD.

Now that SQL server is in single-user mode, we can restore the master system database.

With the sqlcmd command, we can execute the T-SQL command from the command line.

After -S argument, write the correct instance name.

Now we can restore the master database. For this, type the following command:

When the restore process is complete, the SQL Server instance will shut down. 

Finally, start the SQL server instance as usual and then restore the msdb database.

Conclusion

Using the above process, we can solve most of the problems that occur due to system database corruption. However, there are some particular cases. For example, it could happen that the model database is corrupted. The model database is used as the template when you create a new database. 

Since the tempdb database is created each time SQL Server starts and the creation of the tempdb needs the model database, SQL Server will not start if the model database is damaged. In this case, we can try to start SQL Server with the trace flags -T3608 and -T3609 and then restore the model database from backup via T-SQL.

[Read more: How to Fix Recovery Pending State in SQL Server Database and Resolving SQL Database Stuck “In Recovery” Mode]