Technical Article

Script All Jobs

,

We utilize this to script our jobs on a scheduled basis.  The main advantage is that it is automated and really comes in handy when there is a large number of jobs.

1.  Create a job; ActiveX Script.
2.  Paste the code; change the server/path to your desired values.

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 "<servername>"

strFilename = "<path where output file will be placed>_JOBS.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

Rate

5 (2)

Share

Share

Rate

5 (2)