Moving all Jobs on a server

Question

Moving all Jobs on a server

dwilliscp

SSCrazy Eights

Points: 9270
More actions
September 25, 2013 at 1:01 pm

#283138

Is there a way to get a SQL Script, from my old server, that has all of its jobs? So I can then run the SQL on the new server and not have to manually re-create all of my jobs?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

In SSMS you can right-click on the job, select "Script Job as" > "Create to" > ("Query Window" or "File"). This will produce the DDL to create each job. I recommend opening and checking each newly created job as there may be some assembly required (e.g. a job category that does not exist on the target server).

SSIS also has a transfer jobs task. I have never personally used it but it sure sounds like it would work ;-).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

sturner SSC-Insane Points: 22411 More actions · Answer 2

If you want and have the desire to, you can script out all of the Agent jobs using method from SMO from a .net utility. I actually do this on all my servers to generate a script file of all Agent jobs that gets pushed up the DR site along with the backup files daily. Makes it a lot less stressful to get the DR servers up and running if we ever had to fail over.

The probability of survival is inversely proportional to the angle of arrival.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Alan.B (9/25/2013)
In SSMS you can right-click on the job, select "Script Job as" > "Create to" > ("Query Window" or "File"). This will produce the DDL to create each job. I recommend opening and checking each newly created job as there may be some assembly required (e.g. a job category that does not exist on the target server).

I just used this approach last week for a migration and it worked perfectly. In fact, the script includes the commands to check syscategories and create the category if it doesn't exist. The only thing I had to do outside of the generated script was to create the operators on the destination server. All in all, it was painless.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 734639 More actions · Answer 4

The scripting does work great, and I'd actually recommend that you do it periodically as a DR item. Keep the scripts handy in case you don't want to, or can't, restore msdb.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 5

dwilliscp

SSCrazy Eights

Points: 9270

September 26, 2013 at 12:16 pm

#1653699

Thanks for everyone's help!