Restoring SQL Server system databases msdb and model

Problem

Due to a recent rebuild of the master database for a SQL Server instance, I now need to restore the msdb and model databases.  In this tip we walk through the process that you need to follow to restore the model and msdb databases successfully.

Solution

Before we get started, let’s quickly discuss what the msdb and model databases are used for.

• The msdb database contains scheduled jobs, alerts and backup history therefore a proper backup plan should be implemented for the msdb system database.
• The model database is the blue print for creating any new user database for that particular instance of SQL Server. DBAs can modify the model database with whatever settings that are required and when a new user database is created it will reflect the configuration of the model database. If the model database has been modified then it should be backed up.

Hence both the msdb and model databases may need to be recovered in scenarios like database corruption, a rebuild of the master database or after a new server configuration. The restore process of msdb or model demands additional considerations than that of a user databases. The restore process may get complicated if versions are not tracked and exclusive access is not ensured in all aspects. In the next sections we will go through the restore process for the msdb and model databases.

Note: this exercise was performed on SQL Server 2005, but the same rules would also be applied on SQL Server 2005 and onwards.

Backup msdb

Use the following command to create a full backup of the msdb database using T-SQL commands. You will need to modify the script to use a valid backup path.  You can perform the same process for the model database as well.

--Script 1: Create backup of msdb
USE [master]
GO
BACKUP DATABASE [msdb]
TO DISK = N'E:\MSDB_Backup.Bak'
WITH INIT,
NAME = N'msdb Backup for MSSQLTips Demo'
GO

After this is complete we have a full backup that can be restored.

Prepare for restore

To ensure we have a smooth restore we need to follow these steps.

• Get the version of destination server
• Get the version of source server on which the backup was created
• Match the versions for the source and destination servers
• Ensure exclusive access to the database

The term source server here refers to the server on which the backup was created and destination server is where the restore will occur.

Get version of destination server

The versions of SQL Server need to be the same for the source and destination when restoring the msdb or model database. If the versions for the source and destination servers do not match then the restore will fail as shown below:

In the case of a mismatch, the version of SQL Server for the destination needs to be manipulated by installing or removing service packs.

Here are the three ways to get the version for the SQL Server database engine.

Using SSMS Object Explorer

Connect to a SQL Server instance through SSMS and find the SQL Server version in object explorer as shown below.

Using SERVERPROPERTY system function

The SERVERPROPERTY system function can be used for retrieving SQL Server version information to get edition and service pack information.

Using @@Version

The SQL Server version can also be retrieved by using @@Version. It can be used in a simple select statement as shown below.

Get version of source server on which backup was created

Now we need to get the version of the source server on which the backup was created.  The best way to do this is to get the information from the backup file itself as shown below using the RESTORE HEADERONLY command.

--  Script 2: Get information of backup file
RESTORE HEADERONLY
FROM DISK = N'E:\MSDB_Backup.Bak'
GO

Above is the partial output from the command and we can see the SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild.   These three values equate to the output from the other commands we look at earlier.

Match the versions of source and destination server

In our case we have version 9.0.5000 which matches both the source and destination servers. If this is not the case then apply or remove service packs on the destination server to match the source server. Once both versions are the same then we are ready to go further with the process.

Following is a mapping of version codes for SQL Server 2005 and onwards from Microsoft SQL Server support.

Ensure exclusive access

Exclusive access for the database is required just like restoring any database, but for the msdb it is slightly more demanding.  In the case of msdb we also have to consider the SQL Agent service. If the SQL Server agent service is running then exclusive access can not be achieved.  Below we can see that the SQL Agent service is running by querying sys.sysprocesses.

So for msdb we need to stop the SQL Agent service to make sure we can get exclusive access. This can be done by right clicking on SQL Server Agent and selecting Stop.

Restore msdb

At this point our requirements are fulfilled and we are ready to perform the restore. Execute the following command for the restore process.

--Script 3: Restore msdb
USE master
GO
RESTORE DATABASE [msdb]
FROM DISK = N'E:\MSDB_Backup.Bak'
WITH REPLACE
GO

The msdb database is now restored as shown below. Now we need to put the database back in multi user (if this was changed) and start the SQL Server agent service.

The same requirements and process also work for the model database except that the SQL Agent service is not an issue.

Another thing to note is that you can restore msdb and model databases across editions such as Express, Developer, Standard and Enterprise.  You only need to be concerned that the versions are the same.

Next Steps

• As part of best practices, go through your various phases of a disaster recovery process. Manipulating system databases is an integral part of disaster recovery.
• Click here to read a tip about getting exclusive access of SQL Server databases
• Click here to read a tip about system databases in SQL Server
• Click here to read details about versions and releases of SQL Server
• Click here to read about using SERVERPROPERTY
• Click here to read about using @@Version