Rajendra Gupta
Check SQL Services in Linux

Rebuild system databases for SQL Server on Linux and Windows

August 15, 2022 by

This article explores the process for rebuilding system databases – Master, MSDB, and Model for SQL Server on Linux and Windows.

Introduction

Sometimes, you cannot start a SQL Server instance because a system database (or some of them) is corrupted. In this case, it is necessary to rebuild the system databases. Rebuild operation resets the system databases and allows the instance to be online without any error. However, since you have to reset all of your data in the database, you must first ensure having system database backups.

If you rebuild your system databases without making any backup copies, you will lose your logins and jobs. And if you try to open an instance with a database after rebooting the instance, the database won’t be visible, and the SQL instance appears like a newly installed one.

The system rebuild database process is different in Windows and Linux SQL instances. Let’s explore the solution in this article.

Best practices to follow before rebuilding system databases in SQL Server on Linux and Windows

Before we proceed with the rebuilding system databases, here are a few considerations that you should consider for recovering instances without any issues.

  • Make regular full backups for the system database( Master, Model, and MSDB). You cannot take database backup if these databases are corrupted. Therefore, always take precautions before an incident.
  • Store the system configurations such as Product level, version, edition, collation. You might need to reapply the Service packs or CUs after rebuilding system databases.

The following T-SQL script uses the SERVERPROPERTY() function for capturing instance detail. You can run the script on both Windows and Linux SQL Server.

  • You should store the current location of database files for system databases, and the rebuild database process configures these databases into the original location. If you have moved system databases after installing SQL Server, you must move those files after rebuilding.
  • SQL Server recreates the system databass from the template files exists on the local server. The template script location is C:\Program Files\Microsoft SQL Server\MSSQL<13>, <14>, <15> .MSSQLSERVER\MSSQL\Binn\Templates.

Note: Use the correct folder number as per the SQL Server version for the template file location.

  • SQL Server 2016: MSSQL13
  • SQL Server 2017: MSSQL14
  • SQL Server 2019: MSSQL15

For example, in my case, files are located in the following directory.

Templates

If these files are not in the directory, you can use the repair instance feature or manually copy these files from the installation media.

Note: The rebuild process does not allow specifying which system database to rebuild, and it rebuilds all system databases to their default state.

In my demo instance, I have the following user databases, logins, and SQL agent jobs.

View logins and agent jobs

Steps to rebuild system databases on Windows SQL Server

Follow the below steps for rebuilding system databases in Windows SQL Server instance.

  • If you have system databases template scripts available in the C:\Program Files\Microsoft SQL Server\MSSQL<13>, <14>, <15> .MSSQLSERVER\MSSQL\Binn\Templates directory, you can launch the setup.exe from the Setup bootstrap folder.

In my demo instance, the setup.exe is present at the C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019.

The command for rebuilding the system database in Windows SQL Server is as below.

Setup /Q /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ]

Let’s understand the parameters and their values before execution.

  • /Q or /QUIET: This parameter is to run the setup without any user interface. Do not use this parameter to see the rebuild in progress.
  • /ACTION=REBUILDDATABASE: It specifies the SQL Server setup to re-create system databases.
  • /INSTANCENAME=InstanceName: If you have a default instance, enter MSSQLSERVER. Else, enter the SQL instance name.
  • /SQLSYSADMINACCOUNTS=accounts: Specify the Windows group or Individual account that needs to be added in the SYSADMIN fixed server role. If you require multiple accounts, separate them with a blank space.
  • [ /SAPWD=StrongPassword ]: If you use a mixed authentication SQL instance, specify the complex password for the SA account.
  • [ /SQLCOLLATION=CollationName]: If you want to change the server collation, you can use this parameter and pass the new collation value. However, this parameter is not required to use with the existing collation.

To rebuild the system database for my demo, I used the following script.

> Setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Administrator /SAPWD= Hello@123!

The rebuild process completes and returns to command-prompt without any message. You can navigate to the setup bootstrap folder and examine the Summary.txt log file.

Rebuild system databases

The Summary.txt has shown that the rebuild database is completed successfully.

View rebuild progress in summary.txt file

Connect to the SQL instance and verify the DB connectivity. The instance is up, and however, we do not have any user database in the list.

Connect to SQL Server and validate

Post-rebuild tasks

The instance is online; however, it is similar to a new SQL Server installation. You do not have user databases, logins, roles, agent jobs, etc.

Therefore, you need to do the following post-rebuild tasks.

Restore the master database

To restore the master database, follow the steps mentioned below.

  • Stop SQL Server services: You need to stop all SQL Services, including agent, browser, reporting, integration. You can use the SQL Server Configuration Management or command-prompt command –
  • net stop mssqlserver.

Stop SQL Server

  • Start SQL Server in single-user mode with parameter -m. It allows only a single administrator connection. Therefore, we allow SQLCMD to connect to the SQL instance.

net start mssqlserver /m”SQLCMD”

Start SQL Server in single-user mode

  • Connect SQL Server using CMD and restore the Master database backup with the following script. It uses REPLACE keyword to replace the existing master database with the backup.

Restore master database

View user databases

Restore MSDB system database

Now, you can restore the MSDB database for SQL Server agent jobs, DB mails, backup, and restoration history.

Note: If you modified SQL Server collation, do not restore system databases. Else, it replaces the new collation with the previous collation.

  • If you have different paths for the system database earlier, you might want to move them to the new drive.
  • Validation is essential if you rebuild your system databases, and it makes sure you do not have any issues after the rebuild.

Rebuild system databases SQL Server on Linux

The SQL Server works on Linux ( RedHat, Ubuntu, SUSE, and macOS ) as well. Like Windows, you might end up with an issue where SQL Services are not online due to corruption in the master database. In that case, you have to rebuild system databases in SQL Server on Linux.

However, the steps for rebuilding system databases for SQL Server on Linux are different from Windows. The initial requirement or precautions remain the same in both Windows and Linux SQL Server. You should configure regular system database backups to restore them in case you have to rebuild system databases.

As shown below, in my demo environment, SQL Services are online.

Check SQL Services in Linux

Stop the SQL Services using the following statement in the terminal:

sudo systemctl stop mssql-server.service

To rebuild the system database in SQL Server on Linux, we need to run the sqlservr with the force-setup parameter.

sudo -u mssql /opt/mssql/bin/sqlservr –force-setup

Uses force setup parameter for rebuild in SQL Server on Linux

Rebuild progress

It restores the system database with the template and shows the message -Recovery is complete. Press CTRL+C to shut down SQL Server. We specify the SA password during the rebuild statement in the Windows SQL Server, but SQL Server Linux uses the mssql-conf utility to set the SA password below. Always specify a strong password for the SA account.

sudo /opt/mssql/bin/mssql-conf set-sa-password

Use mssql-conf utility for SA password in SQL Server on Linux

Now, you can start SQL Server, verify SQL instance and perform the post rebuild steps.

Connect to SQL Server on Linux

Note: Do not rebuild system databases until you do not have any other option for SQL Server instances.

Conclusion

This article explored rebuilding system databases for the Windows SQL Server and SQL Server on Linux. As a reminder, you should always take regular system database and configuration backups to recover in case of any accidental issues.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views