rsJobs.Close cn.CloseSet rsJobs = nothing Set rsJobSteps = nothing Set rsJobSchedule= Nothing Set cn = nothingWscript.Echo sDataWScript.Echo "Ending Now : " & Nowfunction ScrubString (sInStr) ScrubString = replace (sInStr,"'","''") End Functionpublic function getScriptPath() dim s s = WScript.ScriptFullName s = left(s, InStrRev(s, "\" , -1)) getScriptPath = s end function
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
Dim rsJobs, rsJobSteps, rsJobSchedule
Dim sSQL
Dim sAppPath
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1WScript.Echo "Beginning Now : " & NowsAppPath = getScriptPath()
sSQL = "exec sp_help_job"Set cn = CreateObject("ADODB.Connection")
cn.Open "File Name=" & sAppPath & "connect.UDL"
'-- Open Table
Set rsJobs = CreateObject("ADODB.Recordset")
Set rsJobSteps = CreateObject("ADODB.Recordset")
Set rsJobSchedule = CreateObject("ADODB.Recordset")
rsJobs.Open sSQL, cn , adOpenForwardOnly, adLockReadOnly
Do While Not rsJobs.EOF
WScript.echo "/***" & rsJobs("name") & "***/"
wscript.echo "exec sp_add_job @job_name = '" & ScrubString(rsJobs("name")) & "', @enabled = '" & rsJobs("enabled") & "', @description = '" & ScrubString(rsJobs("description")) & "', @start_step_id = '" & rsJobs("start_step_id") & "', @owner_login_name = '" & rsJobs("owner") & "', @notify_level_eventlog = '" & rsJobs("notify_level_eventlog") & "', @delete_level = '" & rsJobs("delete_level") & "'"
WScript.Echo vbtab & "/***Steps***/"
sSQL = "exec sp_help_jobstep @job_id = '" & rsJobs("job_id") & "'"
rsJobSteps.Open sSQL, cn , adOpenForwardOnly, adLockReadOnly
Do While Not rsJobSteps.EOF
Wscript.echo "exec sp_add_jobstep @job_name = '" & ScrubString(rsJobs("name")) & "', @step_id = '" & rsJobSteps("step_id") & "', @step_name = '" & ScrubString(rsJobSteps("step_name")) & "', @subsystem = '" & rsJobSteps("subsystem") & "', @command = '" & ScrubString(rsJobSteps("command")) & "', @flags = '" & rsJobSteps("flags") & "', @cmdexec_success_code = '" & rsJobSteps("cmdexec_success_code") & "', @on_success_action = '" & rsJobSteps("on_success_action") & "', @on_success_step_id = '" & rsJobSteps("on_success_step_id") & "', @on_fail_action = '" & rsJobSteps("on_fail_action") & "', @on_fail_step_id = '" & rsJobSteps("on_fail_step_id") & "', @database_name = '" & rsJobSteps("database_name") & "', @database_user_name = '" & rsJobSteps("database_user_name") & "', @retry_attempts = '" & rsJobSteps("retry_attempts") & "', @retry_interval = '" & rsJobSteps("retry_interval") & "', @output_file_name = '" & rsJobSteps("output_file_name") & "'"
rsJobSteps.MoveNext
Loop
rsJobSteps.Close
WScript.Echo vbtab & "/***Schedule***/"
sSQL = "exec sp_help_jobschedule @job_id = '" & rsJobs("job_id") & "'"
rsJobSchedule.Open sSQL, cn , adOpenForwardOnly, adLockReadOnly
Do While Not rsJobSChedule.EOF
WScript.Echo "exec sp_add_jobschedule @job_name = '" & ScrubString(rsJobs("name")) & "', @name = '" & ScrubString(rsJobSchedule("schedule_name")) & "', @enabled = '" & rsJobSchedule("enabled") & "', @freq_type = '" & rsJobSchedule("freq_type") & "', @freq_interval = '" & rsJobSchedule("freq_interval") & "', @freq_subday_type = '" & rsJobSchedule("freq_subday_type") & "', @freq_subday_interval = '" & rsJobSchedule("freq_subday_interval") & "', @freq_relative_interval = '" & rsJobSchedule("freq_relative_interval") & "', @freq_recurrence_factor = '" & rsJobSchedule("freq_recurrence_factor") & "', @active_start_time = '" & rsJobSchedule("active_start_time") & "'"
rsJobSchedule.MoveNext
Loop
rsJobSchedule.Close
rsJobs.MoveNext
Loop
rsJobs.Close
cn.CloseSet rsJobs = nothing
Set rsJobSteps = nothing
Set rsJobSchedule= Nothing
Set cn = nothingWscript.Echo sDataWScript.Echo "Ending Now : " & Nowfunction ScrubString (sInStr)
ScrubString = replace (sInStr,"'","''")
End Functionpublic function getScriptPath()
dim s
s = WScript.ScriptFullName
s = left(s, InStrRev(s, "\" , -1))
getScriptPath = s
end function
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
但是那样所有的步骤名称都没了。