November 19, 2014 at 11:41 am
I just took a backup of the MSDB database from Server A and restored it to the disaster recovery Server B at our disaster site. When jobs attempt to run from Server B's schedule they execute on Server A not Server B. I cannot seem to find any table within MSDB that lists a servername. Any ideas on this?
November 20, 2014 at 11:08 am
um, restoring MSDB on a different server sounds a bit scary to me. Although I'm sure you could do it but probably wouldn't myself if I could help it.
I have just used SQL Search to do a text search for Server Name on one of my test servers and that did not show any tables in MSDB with the server name stored in them (not sure if SQL Search includes system tables though). However quite a lot of my jobs have the server name referenced in code in the Job steps. So maybe that's it? If your server is configured for Logshipping I'm pretty sure some of those tables store server information.
November 20, 2014 at 11:17 am
But... this is in case of a disaster... so... how am I supposed to get all of the 50+ jobs from my live prod SQL Server if we have a disaster? THere has to be a somewhat easy way to do this. I cannot find ANYTHING about this anywhere. It baffles me that no one has posted about this anywhere 6 years after SQL Server 2008 came out.
November 20, 2014 at 5:34 pm
As part of my daily backup I usually script out all SQL jobs using PowerShell to an off-site location. As well as logins, server settings, linked servers, database and server level perms etc. And I guess it would be nice to restore msdb from a backup onto DR server but even then I would assume you would restore msdb to a test server in order to manually script out the jobs. Even if you have 50+ jobs it will be very quick to load all of them into ssms and hit execute all. Or knock up a script to cycle through a folder location and execute all scripts.
Still find it strange that your restored msdb runs jobs against the originating server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply