Technical Article

Job to script all Jobs

,

There are various utilities for scripting MS SQL Server objects : Tables , Procedures , Functions .... etc ...
Small piece of code that will allow you to script all jobs from your SQL Server using SQL DMO object.

Step 1. 
ActiveX Script >> set to >> VB Script : 

============================ 

Dim oSQLServer 
Dim oStream 
Set oSQlServer = CreateObject("SQLDMO.SQLServer") 
Set oStream = CreateObject("ADODB.Stream") 
oSQLServer.Connect "MySERVER", "UserID", "Pwd" 

Dim idStep 
Dim ScriptJob 
Dim CountJobs 
Dim JobName 
Dim ScriptAllJobs 

For Each oJob In oSQLServer.JobServer.Jobs 
   CountJobs = oSQLServer.JobServer.Jobs.Count 
Next 

For idStep = 1 To CountJobs 

    JobName = oSQLServer.JobServer.Jobs.Item(idStep).Name 
    ScriptJob = oSQLServer.JobServer.Jobs.Item     (idStep).Script(4, "C:\" & JobName & ".sql") 

ScriptAllJobs = ScriptAllJobs & ScriptJob 

Next 

oStream.Open 
oStream.WriteText (ScriptAllJobs) 
oStream.SaveToFile ("C:\SQLAllScripts.sql"), 2 
oStream.Close 
oSQLServer.DisConnect 

Set oStream = Nothing 
Set oSQLServer = Nothing 

===================================== 

First Loop will make script for each Job ,and second one for all of them . 
Schedule this Job , on the daily basis and backup your Jobs.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating