December 2, 2010 at 12:03 pm
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?
December 8, 2010 at 6:22 am
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;-)
December 13, 2010 at 4:48 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
November 11, 2014 at 11:52 am
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?
February 11, 2016 at 1:09 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply