June 23, 2009 at 10:56 am
I know nothing about VBscript, DMO, or SMO, except that DMO is out and SMO is in with 2008. I have been using some VBScript that I got from the internet to script out all my SQL Server Agent jobs nightly but now that DMO is out, it no longer works and I can't find a replacement. I was wondering if anyone out here could help me out.
The code I have been using is:
Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strAllJobs
Dim strJob
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "(local)"
strPath = Wscript.Arguments(0)
For Each oJB In conServer.JobServer.Jobs
IF (NOT LEFT(OJB.Name, 5) = "DEV -") AND (NOT LEFT(OJB.Name, 9) = "SWN SQL -") THEN
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
strAllJobs = strAllJobs & "--------------------------------------------------" & vbCrLf
strAllJobs = strAllJobs & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strAllJobs = strAllJobs & "--------------------------------------------------" & vbCrLf
strAllJobs = strAllJobs & oJB.Script() & vbCrLf
strFilename = ""
strFilename = strPath & OJB.Name & ".sql"
Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write(strJob)
iFile.Close
Set iFile = Nothing
Set fso = Nothing
strJob = ""
END IF
Next
Set conServer = Nothing
strFilename = ""
strFilename = strPath & "All Production Jobs.sql"
Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write(strAllJobs)
iFile.Close
Set iFile = Nothing
Set fso = Nothing
I pass in the path and this scripted out all of the jobs.
Any help on modifying this to get it to work for SQL Server 2008?
Thanks!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
June 23, 2009 at 11:04 am
There are many T-sql scripts available online to do the same.
http://www.sql-server-performance.com/articles/dba/job_report_p1.aspx
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/61448/
just to start with a few.
Maninder
www.dbanation.com
June 23, 2009 at 11:14 am
Thanks but those are just reports of the jobs on the server and they don't work in 2008 anyway.
I'm trying to come up with a way to script out all of the jobs into a (one or many) sql script files so I can re-create the jobs by running the script.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
June 23, 2009 at 11:24 am
click on jobs folder in sql agent.. then goto object explorer details on the right pane.
ctrl+a to select all--> then right click -> script job as->create to-> new query window an LO! there are your all Agent jobs scripted out
is this what your requiorement was.
Maninder
www.dbanation.com
June 23, 2009 at 11:58 am
No, I know how to script the jobs using Management Studio. I am trying to figure out a way to setup a nightly job that creates scripts of all of the jobs and saves them as SQL script files.
I am trying to automate this process.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply