Rebuild System Databases in SQL Server – DZone Database


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 FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAmaster.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 BinnTemplates folder and make sure that these files are present.

files in the BinnTemplates folder

Next, locate the setup.exe file.

location of 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 FilesMicrosoft SQL Server150Setup BootstrapSQL2019 

screenshot of command prompt

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=BUILTINAdministrators /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:

command-line prompt

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.

SQLCMD

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

command for restoring the master database

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]



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Article

'Pleasure': A young woman's matter-of-fact pursuit of porn stardom

Next Article

Eufy HomeVac H30 Venture review

Related Posts