In addition to the user databases that a SQL Server administrator can create, there are also system databases. The primary ones visible in Object Explorer are master, msdb, model and tempdb. All these databases, except for tempdb, support backup operations. In this article, we’ll discuss whether these databases need to be backed up, and if so, how and how often.

Model database backup

What is stored in the model database?

The model database serves as a template for the CREATE DATABASE operation. If you create stored procedures, tables and populate them with data in the model database, any new databases will automatically be populated with these tables, data, procedures, etc.

Creating a database with standardized content is useful when you’re creating databases automatically. However, this is a relatively rare practice.

Do you need to back up the model database?

Generally, no.

If you are using this database for its intended purpose — to standardize databases — it’s likely that it will change very rarely. It might be more appropriate to store its structure in a version control system like Git as a SQL script. This approach simplifies version management and allows you to track changes.

The only scenario where backing up the model database makes sense is if it contains a large amount of data that changes programmatically from time to time.

Msdb database backup

What is stored in the msdb database?

The msdb database is a system database that stores all SQL Server Agent jobs, everything necessary for their operation and their execution history.

Additionally, this database contains the history of backups and restores. The backup information from msdb is used by SSMS (SQL Server Management Studio) when restoring through the UI.

Do you need to back up the msdb database?

Yes, if you use SQL Server Agent or if one of your database recovery scenarios might require the backup history to, for example, automatically generate a backup set in the restore window in SQL Server Management Studio.

If you don’t use SQL Server Agent and plan to restore databases using third-party software or T-SQL commands, you likely don’t need msdb backups.

How often should you back up msdb, and how long should backups be retained?

Since it’s difficult to imagine a scenario where you’d need to restore the msdb database to a date far in the past, and the database itself doesn’t take up much space, we recommend backing it up as frequently as possible, alongside your regular database backups.

For example, if you’re doing transaction log backups of your regular databases every 15 minutes, you should also back up the msdb database every 15 minutes, immediately after the regular database backups. You can retain msdb backups for a minimal period, such as 1 day or just 1–2 copies.

Master database backup

What is stored in the master database?

The master database stores SQL Server configuration settings, metadata for all other databases, and information about logins and access rights. It is a key database that ensures the proper operation of the entire SQL Server DBMS.

Do you need to back up the master database?

Yes, but it’s not that simple.

The master database is highly dependent on the version of SQL Server, the service pack, the edition, and external configurations, such as the operating system version and the host name.

If you attempt to restore the master database to a SQL Server configuration that differs from the one on which the backup was made, it could lead to anomalies or improper operation of SQL Server. This is not a bug, and it’s documented in SQL Server’s documentation.

The best scenario for restoring the master database is when your SQL Server DBMS is broken and won’t start. In this case, the server version and external configuration remain unchanged, and you can either reinstall SQL Server or rebuild the system databases before restoring the master database, thereby returning the DBMS to its original state.

However, if the entire Windows or Linux server hosting SQL Server is broken and you’re setting up a new server with a clean OS, there’s a high chance that something will be different. In this case, instead of restoring the master database, it’s better to restore all regular databases and reconfigure SQL Server from scratch.

How often should you back up the master database, and how long should backups be retained?

Ideally, you should back up the master database after every configuration change to the DBMS or after adding/removing a database. However, doing this manually is inconvenient, and automating backup triggers for DBMS changes is complex. Therefore, choose a backup interval that aligns with your work practices. For example, if you make changes to SQL Server configuration once a year, there’s no need to back up the master database every 5 minutes — once a month would suffice.

There is little sense in keeping master database backups for a long time, as it’s hard to imagine a situation where you’d need to roll back SQL Server’s configuration to a date far in the past. Most likely, you will restore the latest available configuration in the event of a failure, so it’s sufficient to keep only the last 1–2 backups.

How to restore the master database

If you attempt to restore the master database like a regular database, you will encounter an error:

To restore the master database, the server must be running in single-user mode.

As stated in the error, to restore the master database, you need to set it to single-user mode. It’s not difficult to do; here’s a guide:

• Stop the SQL Server. To do this, press Win+R and type services

• Find SQL Server in the list and select Stop:

• Open the Command Prompt as an administrator:

• Navigate to the directory with the SQL Server executable file. For SQL Server 2016, an example path might be (though path may vary):

cd "c:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn"

• Execute the command to start SQL Server in single-user mode:

sqlservr -c -f -s MSSQLSERVER -mSQLCMD

• If your SQL Server instance name differs from the default value, you need to specify it instead of MSSQLSERVER. The -mSQLCMD parameter ensures that only the sqlcmd utility can connect to SQL Server, preventing anyone else from accessing your database during the operation.

You will see the SQL Server startup log.

• Now, open another command prompt, also with administrator privileges, and run sqlcmd

sqlcmd

• After that, execute the command to restore the master database:

RESTORE DATABASE master FROM DISK = 'c:\temp\16\master_backup.bak' WITH REPLACE;
GO

• If the restoration was successful, you will see a message indicating that the master database has been restored, and SQL Server will automatically shut down:

Processed 512 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

• Now you can reopen the list of Windows services and start SQL Server again:

If the server starts successfully, then the master database has been restored!

What to do if the system database is corrupted

If a system database is corrupted, SQL Server won’t start, and you won’t be able to perform a restore operation for the system database, even if you have a backup. However, there’s no need to rush to reinstall SQL Server — it’s enough to simply rebuild the system databases.

Open a command prompt as an administrator and navigate to the SQL Server installer directory:

cd "C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022"

Execute a rebuild of the system databases using this command:

setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="NT AUTHORITY\SYSTEM" /SAPWD=sa_password

Parameters:

/ACTION=REBUILDDATABASE — this parameter specifies that only the system databases should be rebuilt

/INSTANCENAME=MSSQLSERVER — name of SQL Server instance,  MSSQLSERVER — this is the default name for SQL Server

/SQLSYSADMINACCOUNTS="NT AUTHORITY\SYSTEM" — the name of the system account on the server, "NT AUTHORITY\SYSTEM" — this is the default system account name on Windows Server,

/SAPWD=sa_password — the password for the sa user.

Rebuilding the system databases will reset all SQL Server settings, and in SSMS, the database system will appear as a clean installation. However, your regular databases will not be lost; they will simply be disconnected.

If you have a backup of the master database, now is the time to restore the master database first, followed by the other system databases. If you don’t have a backup of the master database, you will need to reconfigure SQL Server, add the necessary logins, reattach the disconnected databases, and so on.

Conclusion

This article discussed scenarios for when, how, and how often to back up system databases. However, these are general considerations intended to prompt readers to think about their backup and recovery strategy, rather than a detailed step-by-step guide.

What you really need to do is consider how you will restore databases after a failure, model these scenarios (for example, by breaking a test database), and then restore its functionality. Only then will you be able to determine whether you need to back up system databases and how to do it effectively.

By the way, you can also back up system databases using SqlBak and SQLBackupAndFTP.