automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIA
HI
Use SQL-DMO:
'_________________________________________________ ______
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjel?lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
let?r?ljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'_________________________________________________ _________________
Andras Jakus MCDBA
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>
|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> Use SQL-DMO:
> '_________________________________________________ ______
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjel?lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> let?r?ljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '_________________________________________________ _________________
> Andras Jakus MCDBA
> "pk" wrote:
|||Have look at
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It shows how to schedule scripting most objects in the database and save
them in sourcesafe (although I haven't got round to automating that yet -
suspect I will soon).
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>