4

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.

Brett Veenstra
  • 1,497
  • 5
  • 18
  • 27

1 Answers1

3

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • 1
    Yup, found this on the SQL Server Central too (http://www.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