Clint-525719 (8/31/2016)
Thank you. I have done quite a bit of googling, but haven't found what I'm looking for yet.As for msdb, our current DR strategy is to actually restore the user databases to a different, existing production server that has it's own user DBs, jobs, etc. So, overwriting msdb is not an option in that scenario because ti would wipe out all of the jobs, etc. for that server. It's not ideal, I know...I'm working on that, but I have to deal with current state first.
On second thought, do you know if it's possible to restore msdb with a different name and then just insert the job data into another msdb?
Thanks!
Try googling this: script to create all jobs in sql server
There are a couple of script up here on SSC - try search on script all jobs
Ugly DR mess...good luck straightening that one out 🙂 But...yes you can restore msdb with a different name as long as you remember to change the filenames, not just the paths on the restore. But you would still need to script out the jobs from there to recreate. I don't think you'd want to try merging the data.
If you don't like any of the scripts you find, you could run a trace while doing the script jobs from Object Explorer details to see how they do it and create your own. Or just modify the ones you find in a search.
And take as long as you want to do it so that the business can see what a daft idea it is to try to merge two different msdb databases for DR...since you aren't actually going to bring up the same systems you run in production today. But it does give you an excuse to use when DR doesn't work like the normal production environment even though there will always be someone who says "Can't you just <insert ridiculous approach here>"
Sue