Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, October 9, 2014 9:42 AM
Points: 191, Visits: 1,693

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, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 4:18 AM
Points: 2,886, Visits: 3,256
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1033702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse