Restoring SQL Server system databases msdb and model

By:   |   Comments (17)   |   Related: > Disaster Recovery


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.


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]
TO DISK = N'E:\MSDB_Backup.Bak' 
NAME = N'msdb Backup for MSSQLTips Demo'

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:

Msg 3168, Level 16, State 1, Line 1
The backup of the system database on the device E:\MSDB_Backup.Bak cannot be restored because it was created by a different version of the server (9.00.1399) than this server (9.00.5000).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

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.

Get SQLServer Version by using SSMS Object Explorer

Using SERVERPROPERTY system function

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

Get SQL Server version using SERVERPROPERTY system function

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 SQLServer Version by using Version variable

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
FROM DISK = N'E:\MSDB_Backup.Bak'

Backup file information by using RESTORE HEADERONLY

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.

Release Product Version
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 RTM 10.50.1600.1
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 RTM 9.00.1399

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.

SQL Agent - Generic Refresher in 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
FROM DISK = N'E:\MSDB_Backup.Bak'

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.

msdb restored

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 19, 2018 - 4:31:32 PM - Tim Gensler Back To Top (75469)

When restoring msdb, you may have to worry about users who were simply in the SQLAgentOperator roles not being able to run jobs.

Microsoft put out a KB on this: how-to-fix-permission-issues-when-moving-msdb-database-between-different-instances 

and it all revolves around certificate signing or stored procedures and the user: ##MS_AgentSigningCertificate##


Wednesday, June 4, 2014 - 10:47:37 AM - PSC SQL Server DBA Back To Top (32076)

SQL Server Standard Edition version 11.0.3128 {2012 R1}

Operating System Windows 2012 Release 1.

Objective: Create a new development failover cluster and move SQL Agent Jobs to the new Development Instance


1) Performed a Default Instance Failover cluster install.

2) Moved all user databases from source stand-alone development environment to new failover instance using same drive letters.

3) In MSSM attached each user database.

4) Shutdown the SQL Server Agent.

5) Restored the msdb database using MSSM.  The jobs are showing up and everything is there.

Problem:  Under Maintenance Plans I have a plan called 'Full Daily Backups -- System Databases'  Upon opening this job and click on Manage Connections, It opens up a Manage Connections dialog box that has:

Name:                                 Server:                                   Authentication

Local server connection         apps07                                   Windows Authentication

Apps07 is the old/source database.  I need it to say Apps08 the new destination server.  I have a lot of SSIS jobs that all suffer from the same problem. 


How do I change the 'Local server connection'?


Sunday, December 8, 2013 - 1:29:39 AM - NAREN Back To Top (27733)
we have update windows security patches on the active node1 (principal DB) and restated the server the instance are moved to passive node2  (principal DB showing in recovery). we are unable to make principal online.  
Node1 and Node2 are Failover Cluster (same location)
DataBase is Mirroring (Different Geographical location)
we are tried using cmds: 
USE master
The above cmd is not working
Thanking in Advance....

Wednesday, October 30, 2013 - 8:43:52 AM - MarkB Back To Top (27333)

I was just in this state. You can restore using TSQL - that doesn't attempt to open msdb to get the backup path info. See "Script 3" above:

--Script 3: Restore msdb
USE master
FROM DISK = N'E:\MSDB_Backup.Bak'


Friday, October 4, 2013 - 12:43:32 PM - Atif Shehzad Back To Top (27040)

Hi Chung, Try to put the msdb in Emergency state and then execute restore scripts. Following is the link to put in emergency state. In emergency state you may take other measures to repair the database.



Wednesday, October 2, 2013 - 9:46:09 AM - Chung Back To Top (27009)

We are running SQL 2008 Standard.  We currently have the MSDB database marked suspect.  When we go through SSMS, we cannot right-click on the DB and restore.  When we do, we get an error saying that the DB is marked suspect and cannot be opened (we don't even get to choose from a backup file).  How can we restore from a backup?

Thursday, February 7, 2013 - 1:15:32 AM - Atif Back To Top (21961)

@harini. If versions source and destination server do not match then you have two options. First try to match these by adding or removing service packs/patches. If it does not work then you have to manualy transfer the configurations, logins, jobs etc through scripts.


Wednesday, February 6, 2013 - 7:07:51 AM - harini Back To Top (21947)

if the server versions are not matched then how can we restore the database.

Wednesday, January 9, 2013 - 11:30:24 PM - Atif Shehzad Back To Top (21364)

@Anbuselvi. It is not possible to restore backup of master database on version other than its source version. User databases can be restored to any next version but that is not the case for system databases.You have to script out the configurations and logins to transfer on new version.


Wednesday, January 9, 2013 - 1:28:18 PM - Anbuselvi Back To Top (21352)


 We are migrating from SQL Server 2008 to SQL Server 2008 R@, restored every databases, but when tried to restore the Master DB, we got the error


Msg 3168, Level 16, State 1, Server VISTORSAT1, Line 1 The backup of the system database on the device D:\Jon\DBS\master.bak cannot be restored because it was created by a different version of the server (10.00.4000) than this server (10.50.2500).

Msg 3013, Level 16, State 1, Server VISTORSAT1, Line 1 RESTORE DATABASE is terminating abnormally.

I guess in all ways R2 version 10.50.2500 is greated than 10.00.4000.

Any help would be appreciated.




Friday, June 8, 2012 - 12:49:21 AM - Atif Back To Top (17871)

@Linda. Yes of course you may attach the data and log files, it would serve the same purpose as database restore. I would suggest that first you may take full backups of all databases. It would be online operation to backup the databases. Then detach the user databases, and after applying patches or restoring the system databases you may attach the user databases. In this way you would have backup copies also available and time would be saved by just attaching and detaching the databases instead of restoring these.


Thursday, June 7, 2012 - 8:10:15 AM - Linda Back To Top (17837)


I need to devise a back out plan for patching SQL Server 2005 and provide an estimate of how long the backout would take.  Question is:  After I restore the system databases, can I attach the user databases or do I need to do a restore of each user database? 

Thank you for your assistance.

Wednesday, March 7, 2012 - 11:05:05 PM - Atif Back To Top (16289)

Mr. Qasim, You have two main tasks in this scenario. 1) To change the hardware platform 2) To change (upgrade) the version. As it is not a disaster recovery situation but a migration task, so more options are there. Please consider the following broad flow

  • Backup the databases (including system DBs) on source server
  • Note down the version info
  • Deploy same version of SQL Server on the destination hardware
  • Restore user and system DBs on same version at destination server, also rebuild the master and resource
  • Now you may upgrade the server smoothly on new hardware

I hope this flow would lead to successfull change of version and platform. Please also look at following tips and discus here if there is some other complication


Wednesday, March 7, 2012 - 12:09:33 PM - Qasim Ali Back To Top (16280)

Artical is very Helpful -

I wanted to discuess my scenario, We are planning to change our Hardware resources for database produciton servers, also we want to upgrade our all instances to SQL 2008 Enterprise SP1.

My current info is MS SQL Server 2008 RTM SP2 (10.0.1600.22) -- source

My Destination will be MS SQL Server 2008 RTM SP1 (10.50.1600.1) --  destination

My question is, what if our source and destination are not matching..... like if i wanted to upgrade from sql 2005 to sql 2008 (changing hardware too)

Waiting for you answer. Thanks.

Saturday, December 31, 2011 - 7:06:58 AM - Atif Shehzad Back To Top (15487)

Mr. Faheem, Thanks for your appreciation. Taking your second question first. After your last available backup of master, If you have major changes applied to server configuration and logins / permissions, then this may lead you to a hard situation. And for msdb, after last backup if you have made major changes/additions to scheduled jobs or other objects stored in msdb then also it is a hard situation. In such situations last available backups and documentations (if created) may help you to smooth the system.

In most of the cases model database restore is not much hard to manage even if lastes backup is not available. Any documentation about changes along with track from other user databases created through that model would make the restore smooth.

Taking backups of system databases is very important and easy to manage, so try not avoid it.

And about restoring master of resource database, it is important to practice it for any disaster recovery situation. Please read following good tips related to this topic


Friday, December 30, 2011 - 9:27:16 PM - Faheem Back To Top (15480)




Friday, December 30, 2011 - 9:22:06 PM - Faheem Back To Top (15479)



get free sql tips
agree to terms