The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, April 26, 2024 Login
Public

Copy Jobs from one Server to Another 6/24/2009 10:43:24 AM

This script copies jobs from one msdb database to another, useful for when SQL Server is re-installed, and the DBA Forgot to script out the sql agent jobs; but you still have the old msdb mdf and ldf. 

Simply attach the database as msdb_old.

It is also possible to use this via a linked server and copy some sqlagent jobs from one server to another; this script is specific to sql server 2005. 

One item I had to adjust is that some of the jobs appeared "greyed" out afterwards and I had to open each job and adjust the target server to "local". 

But it was still quicker than re-creating all the jobs.  (I was unable to restore the previous version of the msdb database because it was at a lower service pack level than what the new server was built at - had I been smart as the new server was built there would have been a restore of the msdb database completed when the new server was at the same level, but I missed that step and was then left with the task of recreating all the jobs, argh).

I'd be cautious using this script if there were a lot of reporting subscription jobs (as there would have to be some synchronizations with the report database and website), but in this particular case all the jobs were custom backups, tlogs, exports, imports and other purely tsql and ssis related tasks.

http://www.lifeasbob.com/Code/ScriptVault.aspx?script_id=80 

 


Blog Home