SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


restore msdb to another server


restore msdb to another server

Author
Message
edwina derrick
edwina derrick
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 1718
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?
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 4076
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;-)
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5737 Visits: 3860
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,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
Markus
Markus
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2969 Visits: 3767
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?



Hailegziabher
Hailegziabher
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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:-D:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search