Script to generate Job's

• carlos.tapadinhas

  Ten Centuries

  Points: 1058

  More actions

  November 5, 2008 at 3:07 am

  #214961

  Hi to all,

  I have to create all the jobs i have in my databases in productions, to a database in my machine, does anybody know the best way to generate the script to create the jobs? I have databases in SS 2005 and other in SS2000

  Thanks

• Francisco Javier Megías Velasco

  SSC Veteran

  Points: 255

  More actions

  November 5, 2008 at 3:25 am

  #894105

  Hi,

  Open the Management studio of SQL 2005.

  Select the job folder in the explorer objects window (left).

  In the right window, (explorer details), select all the jobs.

  Right click, ant then select "add job as", CREATE TO, File.

  Then run the created .sql file in the destination machine, to create the jobs.

  HTH...

• Cowboy DBA

  SSCrazy

  Points: 2135

  More actions

  November 5, 2008 at 3:25 am

  #894106

  Save this as a .vbs file:

  Dim oSQLServer

  Dim oStream

  SET oSQlServer = CreateObject("SQLDMO.SQLServer")

  SET oStream = CreateObject("ADODB.Stream")

  oSQLServer.Connect "servername", "login", "password"

  Dim idStep

  Dim ScriptJob

  Dim CountJobs

  Dim JobName

  Dim ScriptAllJobs

  FOR Each oJob IN oSQLServer.JobServer.Jobs

  CountJobs = oSQLServer.JobServer.Jobs.Count

  Next

  FOR idStep = 1 TO CountJobs

  JobName = oSQLServer.JobServer.Jobs.Item(idStep).Name

  wscript.echo "Scripting: " & JobName

  ScriptJob = oSQLServer.JobServer.Jobs.Item (idStep).Script(4, "c:\temp\" &

  JobName & ".sql")

  ScriptAllJobs = ScriptAllJobs & ScriptJob

  Next

  oStream.Open

  oStream.WriteText (ScriptAllJobs)

  oStream.SaveToFile ("c:\temp\SQLAllScripts.sql"), 2

  oStream.Close

  oSQLServer.DisConnect

  SET oStream = Nothing

  SET oSQLServer = Nothing

• laubenth

  Mr or Mrs. 500

  Points: 588

  More actions

  November 6, 2008 at 10:43 am

  #895082

  I would go with Francisco's script method. Once you get the output you can modify the script anyway you need to. We did this same step years ago and have it modified to create/run the job based on the DB that the script is run against. I would recommend trying this on SS 2000 first if it is available so that you know the script will run against 2000 and 2005.