up vote 3 down vote favorite
1
share [g+] share [fb]

When migrating servers, how can I easily script all my SQL Server Jobs onto a new machine?


NOTE

Not accepting "backup msdb" as the answer.

link|improve this question
50% accept rate
feedback

1 Answer

up vote 2 down vote accepted

In SQL Management Studio:

  • Click on the jobs folder in Object Explorer
  • Display the Object Explorer Details window (under View menu)
  • Select all or some of your jobs
  • Right-click, script job as Create To New Query Window
  • Change the connection of your query window to the new server
  • Execute the generated script(s)

There you go!

Note: this is for SQL 2005+. SQL 2000 Enterprise Manager has an option to Generate Scripts on the Job Agent, I believe.

link|improve this answer
1  
Yup, found this on the SQL Server Central too (sqlservercentral.com/Forums/FindPost648030.aspx). – Nathan Bedford Jul 23 '09 at 14:23
Review the resulting scripts carefully. I've forgotten the details, but I hit a situation in SQL 2005 where a GUID was generated in the SSMS script for the JobId, and the script then checked for the existance of that GUID when the job was being loaded. Not too useful if you run the script a second time on the machine, since the job name will be duplicated... – Philip Kelley Jul 23 '09 at 20:12
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.