Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restore msdb to another server Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 12:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 4, 2015 12:10 PM
Points: 191, Visits: 1,718

I want to take a backup of msdb on server A and restore it to server B. Has anyone done this in SQL Server 2008 and does it require an update to msdb tables for the server name like SQL Server 2005?
Post #1029448
Posted Wednesday, December 8, 2010 6:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 19, 2016 4:34 AM
Points: 2,850, Visits: 4,076
edwina derrick (12/2/2010)

I want to take a backup of msdb on server A and restore it to server B. Has anyone done this in SQL Server 2008

You can simply restore msdb from a backup that you have made as long as no
one is using it. That really means to just stop the SQL Server Agent
process and restore the database and then start the Agent back up.


edwina derrick (12/2/2010)
does it require an update to msdb tables for the server name like SQL Server 2005?
NO.This will be automatically handled by sql server.









-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1031834
Posted Monday, December 13, 2010 4:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 23, 2016 5:06 AM
Points: 3,034, Visits: 3,709
This can be done, but you need to be very careful about when and why you do it.

The structure and contents of msdb can change with every service pack, cumulative update or hotfix. Microsoft assumes that it has control over msdb and does not document the changes fixes make to msdb, unless it is particularly relevant to the fix. You need to make certain you restore an msdb with exactly the same fix level as the target server, or future maintenance of the target server may not have the intended results. If you do have a msdb-related problem on a server that has an out-of-step msdb, Microsoft may ask you to reproduce the problem on a correctly configured server before they give you further assistance.

If you want to install a standard set of jobs on to a new server, then restoring msdb is the wrong way to do this. You should generate scripts for all of your jobs, then run these scripts on your new server. The same applies for any other type of data initialisation.

If you want to restore msdb as part of a DR process, then this can (just about) be justified.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005. 11 May 2016: now over 37,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Post #1033702
Posted Tuesday, November 11, 2014 11:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 26, 2016 5:03 AM
Points: 1,516, Visits: 3,590
I know this is an old thread... however... I just took a backup of MSDB from a SQL2008R2 and restored it to a SQL2008R2 for our disaster site. All worked OK but when jobs attempt to run they run on the first SQL Server not the disaster recovery server. I cannot seem to find any table within MSDB that tells it the server name.

Anyone have any ideas?



Post #1633558
Posted Thursday, February 11, 2016 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 14, 2016 2:08 PM
Points: 1, Visits: 5
you just have to take backup file from server A. Stop SQL server agent. Perform a normal restore using SSMS or using the syntax "restore database msdb from disk='eneter the directory\msdb.bak' go. Then start sql server agent and you are good to go
Post #1760594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse