How To Copy SQL Agent Job one Server to another server

Question

How To Copy SQL Agent Job one Server to another server

sree10

SSChasing Mays

Points: 636
More actions
October 27, 2011 at 12:14 pm

#245818

I'm using sql server 2008 R2 which has more than 300 sql agent jobs. Need to copy these jobs from here to another server(sql server 2008 R2).
I can't script them one by one..
Please help me.
Thanks in advance..

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Can you copy msdb from one to the other? It's a bit tricky, and you'll want to look into other effects from it than just copying jobs, but you can backup and restore msdb so long as the two servers have the same patch level.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

calvo SSChampion Points: 12930 More actions · Answer 2

An alternative would be to just script the jobs. Make sure the Object Explorer Details window is open (f7), navigate SQL Server Agent > Jobs.

Select all the jobs you want to copy, right click > script jobs as > [your desired option]. This works well and the only thing I can think of that would prevent them from working on the new server would be a missing operator or mail profile. It's also good for archiving or just scripting jobs on an instance.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

sree10 SSChasing Mays Points: 636 More actions · Answer 3

Thanks mate...your suggestion reduced the pain.

Thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

calvo (10/27/2011)
An alternative would be to just script the jobs. Make sure the Object Explorer Details window is open (f7), navigate SQL Server Agent > Jobs.
Select all the jobs you want to copy, right click > script jobs as > [your desired option]. This works well and the only thing I can think of that would prevent them from working on the new server would be a missing operator or mail profile. It's also good for archiving or just scripting jobs on an instance.

This is generally how I would do it. SO much faster and easier.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events