script out 2005 SQLAgent jobs

• jack-64275

  SSCrazy

  Points: 2017

  More actions

  August 14, 2008 at 2:10 pm

  #858383

  FYI...

  This may be a little late but I'm working on the log shipping situation as well and I just went through and created the Transfer Jobs SSIS package. I ran it manually and it completed without errors.

  If you haven't look at this I think it's a good option... you can have it skip jobs that already exist or overwrite them or fail. You can also set it so that the jobs are disable

  Thanks!

• MG-148046

  SSCrazy Eights

  Points: 9946

  More actions

  August 15, 2008 at 6:38 am

  #858643

  This is some code I cut out in pieces from a .Net program I cobbled together. We script out everything on a server every evening for recovery and DR. This should get you on the right track for your jobs and it includes Operators and Alerts. I don't know that I got everything you will need to make it work "as is" so you will probably need to tweak it.

  References to add to project:

  Microsoft.SqlServer.ConnectionInfo

  Microsoft.SqlServer.Smo

  Microsoft.SqlServer.SmoEnum

  Microsoft.SqlServer.SqlEnum

  Module1.vb

  Imports Microsoft.SqlServer.Management.Smo

  Imports Microsoft.SqlServer.Management.Smo.Wmi

  Imports Microsoft.SqlServer.Management.Smo.Mail

  Imports Microsoft.SqlServer.Management.Smo.Mail.SqlMail

  Imports Microsoft.SqlServer.Management.Common

  Imports System.Collections.Specialized

  Imports System

  Imports System.IO

  Imports System.Text

  Module Module1

  Dim db As Database

  Dim scrp As Scripter

  Dim wf As StreamWriter

  Dim path As String = "ScriptSQL"

  Sub Main()

  Dim svr As Server

  Dim ServerName As String

  Dim InstanceName As String

  If My.Application.CommandLineArgs.Count = 2 Then ' check for named instance

  ServerName = My.Application.CommandLineArgs(0)

  InstanceName = My.Application.CommandLineArgs(1)

  svr = New Server(ServerName & "\" & InstanceName)

  path = path & InstanceName

  Else

  svr = New Server() ' default instance

  ServerName = "" 'No Server, will connect local.

  InstanceName = ""

  End If

  scrp = New Scripter(svr)

  scrp.Options.ScriptDrops = False

  wf.WriteLine()

  wf.WriteLine("-- **** Create Operators")

  wf.WriteLine()

  Dim op As Agent.Operator

  For Each op In svr.JobServer.Operators

  smoObjects = New Urn(0) {}

  smoObjects(0) = op.Urn

  scrp.Options.IncludeIfNotExists = True

  Dim sc As StringCollection

  sc = scrp.Script(smoObjects)

  Dim st As String

  For Each st In sc

  wf.WriteLine(st)

  wf.Flush()

  Next

  Next

  wf.WriteLine()

  wf.WriteLine("-- **** Create Alerts")

  wf.WriteLine()

  Dim al As Agent.Alert

  For Each al In svr.JobServer.Alerts

  smoObjects = New Urn(0) {}

  smoObjects(0) = al.Urn

  scrp.Options.IncludeIfNotExists = True Sub Main()

  On Error GoTo ErrHandler

  scrp = New Scripter(svr)

  scrp.Options.ScriptDrops = False

  Dim sc As StringCollection

  sc = scrp.Script(smoObjects)

  Dim st As String

  For Each st In sc

  wf.WriteLine(st)

  wf.Flush()

  Next

  Next

  wf.WriteLine()

  wf.WriteLine("-- **** Create Jobs")

  wf.WriteLine()

  Dim jb As Agent.Job

  For Each jb In svr.JobServer.Jobs

  smoObjects = New Urn(0) {}

  smoObjects(0) = jb.Urn

  scrp.Options.IncludeIfNotExists = True

  Dim sc As StringCollection

  sc = scrp.Script(smoObjects)

  Dim st As String

  For Each st In sc

  wf.WriteLine(st)

  wf.Flush()

  Next

  Next

  End Sub

  End Module

  MG

  "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
  Tony Hoare

  "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.