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

Recently, I while upgrading a client from GP 10 to GP 2010 and SQL Server from 2005 to 2008 R2.  The SQL Server was going to be a clean install and not an in place upgrade.   So I was left with the task of moving 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:

Agent
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

  1. 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)
  2. Open the JOB.sql file in notepad and remove the TEST Job.
  3. 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.  To get the same functionality for SQL 2008 you will need to install ‘Microsoft SQL Server 2005 Backward Compatibility Components’ at  http://www.microsoft.com/en-us/download/confirmation.aspx?id=3522

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s