August 30, 2016 at 6:00 pm
I am trying to find a script that I can run which will return CREATE statements for all jobs on a SQL Server. I know how to go to Object Explorer Details, then select all the jobs I want, then script the CREATE statements. But what I am trying to do is have a job run every night to export the CREATE statements to a file for DR purposes.
Has anyone seen anything like this?
Thanks!
August 31, 2016 at 12:17 pm
Clint-525719 (8/30/2016)
I am trying to find a script that I can run which will return CREATE statements for all jobs on a SQL Server. I know how to go to Object Explorer Details, then select all the jobs I want, then script the CREATE statements. But what I am trying to do is have a job run every night to export the CREATE statements to a file for DR purposes.Has anyone seen anything like this?
Thanks!
Yes there are scripts out there for this if you google it. Do you have nightly backups of msdb that would be available for DR?
Are you assuming that the restore of msdb is going to fail? The jobs are all in msdb so the restore of that backup would typically be the process for getting jobs, operators, alerts, etc.
Sue
August 31, 2016 at 12:50 pm
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!
August 31, 2016 at 1:34 pm
apparently I wasn't looking in the right places :w00t:
i was able to alter the script on the following page to do what I need:
http://stackoverflow.com/questions/3361163/automatically-create-scripts-for-all-sql-server-jobs
August 31, 2016 at 1:34 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply