September 19, 2002 at 2:21 pm
Looking for a way to programatically script a job in sql for backup purposes. I understand how to use the wizard to script jobs but need to automate this process. Anyone have any ideas on ways to get this information using T-SQL???
September 19, 2002 at 2:28 pm
I've kind of done that. What I build the job one time. Then every time after that I dynamically build the steps, and execute the jobs. I use a combination of sp_delete_jobstep, and sp_add_jobstep SP's to accomplish this. Of course you could also use sp_add_job, sp_add_jobchedule.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 2:57 pm
Create a job (ActiveX Script), paste this code, change the appropriate information and you are set. It is a SQL-DMO piece that we use.
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 "<servername>"
strFilename = "<path where output file will be placed>_JOBS.sql"
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
Be great!
Mike
Be great!
Michael
September 19, 2002 at 3:18 pm
That script was perfect! I appreciate it. Exactly what we're trying to accomplish. Here's one more quicky for you.
How can I do the same thing for a database. I want to script all elements (table, procedures, triggers, etc) minus the data. Basically just a skeleton of the database with procedures. I can do it using the management wizards but don't know how to script it. can you help here?
Thanks again. I appreciate your assistance.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply