SQL Server 2005/2008 : Generate scripts all jobs

SQL Server 2005: How to generate scripts for all jobs?
SQL Server 2005: Script all jobs

There are some TSQL methods that will generate the scripts to create all jobs but there is one major constraint with those scripts.

They all use a variable like @command which is usually nvarchar or varchar and this limits the number of characters in the job step. If you have a large TSQL in the job step then you can not use those TSQL queries to script out all job.

The best method to do this is using a VB Script that I found online. I am not the author of the script but wanted to list it here.

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "yourservername"

strFilename = "C:\DR_Files\CreateJobs.sql"

For Each oJB In conServer.JobServer.Jobs
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing

Save this to a file with the extension 'vbs' and execute it on a command line or via a command line job step using the following command. Make sure you edit the file to use 'yourservername' and also change the path to the output file. In the above example I am saving the file to C:\DR_Files\CreateJobs.sql.

C:\WINDOWS\system32\cscript.exe C:\DR_Files\Generate_Jobs.vbs

This should create a file that can be used to create all the jobs. Make sure you parse the file in a query window to make sure you can create the scripts. But DO NOT EXECUTE the output file.