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.
|
1 2 3 4 5 6 7 8 9 |
SELECT SERVERPROPERTY('ProductVersion ') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') as Edition, SERVERPROPERTY('ResourceVersion') AS ResourceVersion, SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime, SERVERPROPERTY('Collation') AS Collation |
- 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.
|
1 2 3 4 |
SELECT name, physical_name AS current_file_location FROM sys.master_files |
- 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.
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.
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.
The Summary.txt has shown that the rebuild database is completed successfully.
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.
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.
- 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”
- 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.
|
1 2 3 4 |
RESTORE DATABASE master FROM DISK ="C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\master.bak" WITH REPLACE |
Restore MSDB system database
Now, you can restore the MSDB database for SQL Server agent jobs, DB mails, backup, and restoration history.
|
1 2 3 4 5 6 7 8 |
USE [master] ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [msdb] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\msdb.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE [msdb] SET MULTI_USER GO |
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.
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
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
Now, you can start SQL Server, verify SQL instance and perform the post rebuild steps.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023