Scripting out SQL Server Agent Jobs by using ‘Script Job as..’ function fails

Recently, while upgrading a client from GP 10 to GP 2010 and SQL Server from 2005 to 2008 R2.  SQL Server was going to be a clean install and not an in place upgrade and the client had over 200 SQL Jobs.   Selecting all of the Jobs and using the Script Job as function failed.  To get it to work, I had to break the list of Jobs into 30 separate lists using the Script Job as function.  This would work for the Test Environment but not during the Production upgrade weekend.  So off to Google I went in search for something that would automate the process.  Here is what worked for me:

1. Create a new SQL Server Job on the 2005 server.  I named my job ‘TEST’.

2. Change the job step to ActiveX Script

3.  Paste in the following code in the Command area:

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 “.”

strFilename = “C:JOBS.sql”

strJob = “”

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

During Production upgrade weekend, execute the job.  File will be saved to the C drive as
“C:JOBS.sql”.  (you can change the following code ‘strFilename=”C:JOBS.sql”’ to save to a different location).  Open the JOB.sql file in notepad and remove the TEST Job and if the SQL instance name has changed do a find and replace on the file with the new SQL instance name.   Copy into Microsoft SQL Server Management Studio and execute script on the new SQL 2008 R2 Server. This VB script code works with SQL 2005.

Share this post

Related Posts

Webinar Recap: CMMC News for the DIB

The DoD announcement on Nov 4 “Strategic Direction for the CMMC Program” provided new guidelines for CMMC 2.0. Our KTL webinar on Nov 10 helped to clarify these changes and answer questions for those in the DIB.

Read More »