Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restoring SQL Server system databases msdb and model


By:   |   Read Comments (16)   |   Related Tips: More > Disaster Recovery

Attend a SQL Server Conference for FREE >> click to learn more


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:

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

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
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.

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 04, 2014 - 10:47:37 AM - PSC SQL Server DBA Back To Top

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

Steps:

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 08, 2013 - 1:29:39 AM - NAREN Back To Top
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
 
ALTER DATABASE XXXXX SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE XXXXX SET ONLINE
 
RESTORE DATABASE XXXXX WITH RECOVERY
 
The above cmd is not working
 
 
 
Thanking in Advance....

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

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
GO
RESTORE DATABASE [msdb]
FROM DISK = N'E:\MSDB_Backup.Bak'
WITH REPLACE
GO


 


Friday, October 04, 2013 - 12:43:32 PM - Atif Shehzad Back To Top

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.

http://www.mssqltips.com/sqlservertip/2333/using-the-emergency-state-for-a-corrupt-sql-server-database/

 

Thanks


Wednesday, October 02, 2013 - 9:46:09 AM - Chung Back To Top

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 07, 2013 - 1:15:32 AM - Atif Back To Top

@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.

Thanks


Wednesday, February 06, 2013 - 7:07:51 AM - harini Back To Top

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


Wednesday, January 09, 2013 - 11:30:24 PM - Atif Shehzad Back To Top

@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.

Thanks


Wednesday, January 09, 2013 - 1:28:18 PM - Anbuselvi Back To Top

Hi,

 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.

 

Thanks!

Anbuselvi


Friday, June 08, 2012 - 12:49:21 AM - Atif Back To Top

@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.

Thanks


Thursday, June 07, 2012 - 8:10:15 AM - Linda Back To Top

Hello,

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 07, 2012 - 11:05:05 PM - Atif Back To Top

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

http://www.mssqltips.com/sqlservertip/2425/rebuilding-sql-server-on-different-hardware-after-a-failure/

http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/

http://www.mssqltips.com/sqlservertip/1544/sql-server-backup-and-restore-of-the-resource-database/

Thanks


Wednesday, March 07, 2012 - 12:09:33 PM - Qasim Ali Back To Top

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

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

http://www.mssqltips.com/sqlservertip/2425/rebuilding-sql-server-on-different-hardware-after-a-failure/

http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/

http://www.mssqltips.com/sqlservertip/1544/sql-server-backup-and-restore-of-the-resource-database/

Thanks


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

 

SIR, CAN HOW CAN WE RESTORE MASTER DATABASE??AND RESOURCE?? CAN WE DO IT?

IF YES?? PLZ SHARE IT WHEN WE HAVE TO DO IT?? AND I HAVE A DOUBT THAT SUPPOSE WE DON'T HAVE LATEST BACKUP OF NSDB,MASTER,MODEL AND RESOURCE. THEN FOR AN INSTANCE SERVER CRASHES@@@@@ THEN WHAT CAN WE DO???


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

 

WOOOOOOW!!!!!!!!!! IT'S REALLY VVVVV USEFULL INFO...THANKS FOR SHARING.....REALLY GOOD EXPLANATION....


Learn more about SQL Server tools