Quickest way to move jobs

Question

Quickest way to move jobs

crazy_new

SSCarpal Tunnel

Points: 4804
More actions
August 4, 2016 at 2:07 am

#328631

Hi Guys,
I need to move all the jobs from one of my live servers to a DR server.
I want to restore the msdb backup instead of scripting them out and recreating them.
Is there something that I can miss when just restoring msdb that would cause jobs to fail or is it that straight forward?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Since it's a DR server, you're going to need a way to propagate changes to live jobs into DR, so why not use that same technique to do the initial copy? I really wouldn't like to risk copying over something that I didn't want to by restoring the whole database. Besides, you'll want to make sure that jobs, alerts, operators and so on aren't enabled on a passive DR server. It's much easier to do that with a script than manually after a restore.

John

BrainDonor SSCoach Points: 19254 More actions · Answer 2

I have to agree with John. At a previous place we always scripted the jobs from the test server for deployment on the live server, and then onto the DR server (with the job disabled). In addition this also meant that there were scripts in source control for everything, should we even have an issue with the DR server.

Running a script sounds a lot easier than dealing with any potential issues from restoring the msdb database from another server.

Is there a reason that it has to be done by copying the msdb?

Steve Hall
Linkedin
Blog Site

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 3

Hi Guys,

Thanks for the feedback. There is no reason I has to be done by restoring the msdb. I just thought I might be faster than scripting out every single job.

BrainDonor SSCoach Points: 19254 More actions · Answer 4

If you have a large number of jobs that aren't currently scripted then it will be a bit of work to script them all for the initial setup. However, this approach should be easy going forward, when just scripting the one job you're dealing with.

Steve Hall
Linkedin
Blog Site

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 5

Thanks for all the feedback guys, I will keep this in mind.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

You can script out all jobs at once by listing the jobs in object explorer details-->select all jobs--> right click -->script jobs

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

VastSQL (8/7/2016)
You can script out all jobs at once by listing the jobs in object explorer details-->select all jobs--> right click -->script jobs

+1.

For those that don't know how to get there, press {f8} to display the Object Explorer, scroll down to SQL Server Agent and expand it, select Jobs, and then press {f7}.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

william.scott 34593 SSC Enthusiast Points: 179 More actions · Answer 8

Thanks Jeff, do you have a similarly easy way to get the SSIS Jobs scripted out?

Thanks In Advance

bill

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

william.scott 34593 (8/8/2016)
Thanks Jeff, do you have a similarly easy way to get the SSIS Jobs scripted out?
Thanks In Advance
bill

Only if the jobs are executed by SQL Server Agent, in which case you'd gen the code for them the same way as any other job. I don't know how to do it on, for example, a stand alone instance dedicated to just SSIS because I'm fairly ignorant when it comes to SSIS. In fact, I've spent a good part of my career replacing SSIS packages with T-SQL.

Sorry I can't help more there. Hopefully, someone will see this and fill in that gap for your.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)