In SQL Server Management Studio I can right-click a job, Script job as, and then save the created script as a .sql file. In order to back up my jobs, I would like to automate the above process, but cannot find any way of doing so. Is this possible? How?
2 Answers
Aside from backing up the msdb database, which is where they are stored, you could script them out once, and then check for changes every day. If there are no changes, there isn't any reason to back them up again. If you did figure out someone changed a job or a schedule, I'm sure you next question would be what actually changed and when it changed. Otherwise, you wouldn't know which version of your job to restore to.
I wrote a procedure a while back to do just this. You can find it on GitHub because it's too long to post here. One dependency of this script is that it logs the current jobs, schedules, etc to a table in a database called AdminTools. This is so we can compare what is in msdb to what we have logged. You can change this to use any database you want to put your logging table.
Also, I wrote a script to do the same thing for Agent Alerts. It can also be found on GitHub.
Here is the guts of the agent job procedure, which pulls all the jobs and schedules, and displays it very similarly to SSMS (hence all the logic in the case statements).
select
jobs.job_id
,job_name = jobs.name
,job_desc = jobs.description
,jobs.enabled
,jobs.date_created
,jobs.date_modified
,jobs.version_number
,jobs.start_step_id --ID of the step in the job where execution should begin
,job_owner = serv_princ.name --Security identifier number (SID) of the job owner, to check if job now owned by SA --need to work on this
,notify_level_eventlog = case
when jobs.notify_level_eventlog = 0 then 'Never'
when jobs.notify_level_eventlog = 1 then 'When the job succeeds'
when jobs.notify_level_eventlog = 2 then 'When the job fails'
when jobs.notify_level_eventlog = 3 then 'When the job completes (regardless of outcome)'
end
,notify_level_email = case
when jobs.notify_level_email = 0 then 'Never'
when jobs.notify_level_email = 1 then 'When the job succeeds'
when jobs.notify_level_email = 2 then 'When the job fails'
when jobs.notify_level_email = 3 then 'When the job completes (regardless of outcome)'
end
,email_operator_name = emailop.name
,email_operator_email = emailop.email_address
,email_operator_enabled = emailop.enabled
,level_page = case
when jobs.notify_level_page = 0 then 'Never'
when jobs.notify_level_page = 1 then 'When the job succeeds'
when jobs.notify_level_page = 2 then 'When the job fails'
when jobs.notify_level_page = 3 then 'When the job completes (regardless of outcome)'
end
,page_operator_name = pageop.name
,page_operator_address = pageop.pager_address
,page_operator_enabled = pageop.enabled
,page_operator_scheduled_days = SUBSTRING(
CASE WHEN pageop.pager_days & 1 = 1 THEN ',Sun' ELSE '' END
+ CASE WHEN pageop.pager_days & 2 = 2 THEN ',Mon' ELSE '' END
+ CASE WHEN pageop.pager_days & 4 = 4 THEN ',Tues' ELSE '' END
+ CASE WHEN pageop.pager_days & 8 = 8 THEN ',Wed' ELSE '' END
+ CASE WHEN pageop.pager_days & 16 = 16 THEN ',Thurs' ELSE '' END
+ CASE WHEN pageop.pager_days & 32 = 32 THEN ',Fri' ELSE '' END
+ CASE WHEN pageop.pager_days & 64 = 64 THEN ',Sat' ELSE '' END
, 2, 64)
,page_operator_weekday_sked = stuff(stuff(right('00000' + cast(pageop.weekday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.weekday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,page_operator_saturday_sked = stuff(stuff(right('00000' + cast(pageop.saturday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.saturday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,page_operator_sunday_sked = stuff(stuff(right('00000' + cast(pageop.sunday_pager_start_time as varchar),6),3,0,':'),6,0,':') + ' - ' + stuff(stuff(right('00000' + cast(pageop.sunday_pager_end_time as varchar),6),3,0,':'),6,0,':')
,steps.step_id
,steps.step_name
,steps.command
,on_success_action = case
when steps.on_success_action = 1 then 'Quit reporting success'
when steps.on_success_action = 2 then 'Quit reporting failure'
when steps.on_success_action = 3 then 'Go to next step (' + cast(steps.step_id + 1 as varchar) + ')'
when steps.on_success_action = 4 then 'Go to step: ' + cast(on_fail_step_id as varchar)
end
,on_fail_action = case
when steps.on_fail_action = 1 then 'Quit reporting success'
when steps.on_fail_action = 2 then 'Quit reporting failure'
when steps.on_fail_action = 3 then 'Go to next step (' + cast(steps.step_id + 1 as varchar) + ')'
when steps.on_fail_action = 4 then 'Go to step: ' + cast(on_fail_step_id as varchar)
end
,last_run_outcome = case
when steps.last_run_outcome = 0 then 'Failed'
when steps.last_run_outcome = 1 then 'Succeeded'
when steps.last_run_outcome = 2 then 'Retry'
when steps.last_run_outcome = 3 then 'Canceled'
when steps.last_run_outcome = 5 then 'Unknown'
else 'Undefined'
end
,last_run_date = case when steps.last_run_date = 0 then 'Never' else stuff(stuff(steps.last_run_date,5,0,'-'),8,0,'-') end
,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')
,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')
from
msdb.dbo.sysjobs jobs
left join
msdb.dbo.sysjobsteps steps on
steps.job_id = jobs.job_id
left join
msdb.dbo.sysoperators emailop on
emailop.id = jobs.notify_email_operator_id
left join
msdb.dbo.sysoperators pageop on
pageop.id = jobs.notify_email_operator_id
left join
sys.server_principals serv_princ on
serv_princ.sid = jobs.owner_sid
select
sched.schedule_uid
,sched.schedule_id
,job_name = jobs.name
,job_enabled = jobs.enabled
,schedule_name = sched.name
,schedule_frequency = case
when sched.freq_type = 1 then 'One time only on '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' at '
+ case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
when sched.freq_type = 4 then 'Every '
+ cast(sched.freq_interval as varchar)
+ ' days,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 8 then 'Every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' weeks on '
+ SUBSTRING(
CASE WHEN sched.freq_interval & 1 = 1 THEN ',Sun' ELSE '' END
+ CASE WHEN sched.freq_interval & 2 = 2 THEN ',Mon' ELSE '' END
+ CASE WHEN sched.freq_interval & 4 = 4 THEN ',Tues' ELSE '' END
+ CASE WHEN sched.freq_interval & 8 = 8 THEN ',Wed' ELSE '' END
+ CASE WHEN sched.freq_interval & 16 = 16 THEN ',Thurs' ELSE '' END
+ CASE WHEN sched.freq_interval & 32 = 32 THEN ',Fri' ELSE '' END
+ CASE WHEN sched.freq_interval & 64 = 64 THEN ',Sat' ELSE '' END
, 2, 64)
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ', every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 16 then 'Every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' months, on day '
+ cast(sched.freq_interval as varchar)
+ ' of that month,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 32 then 'Every '
+ case
when sched.freq_relative_interval = 1 then 'first '
when sched.freq_relative_interval = 2 then 'second '
when sched.freq_relative_interval = 4 then 'third '
when sched.freq_relative_interval = 8 then 'fourth '
when sched.freq_relative_interval = 16 then 'last '
else ''
end
+ case
when sched.freq_interval = 1 then 'Sunday'
when sched.freq_interval = 2 then 'Monday'
when sched.freq_interval = 3 then 'Tuesday'
when sched.freq_interval = 4 then 'Wednesday'
when sched.freq_interval = 5 then 'Thursday'
when sched.freq_interval = 6 then 'Friday'
when sched.freq_interval = 7 then 'Saturday'
when sched.freq_interval = 8 then 'day'
when sched.freq_interval = 9 then 'weekday'
when sched.freq_interval = 10 then 'weekend'
end
+ ', of every '
+ cast(sched.freq_recurrence_factor as varchar)
+ ' months,'
+ case
when sched.freq_subday_type = 1 then ' at '
when sched.freq_subday_type = 2 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' seconds, from '
when sched.freq_subday_type = 4 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' minutes, from '
when sched.freq_subday_type = 8 then ' every ' + cast(sched.freq_subday_interval as varchar) + ' hours, from '
end
+ case
when sched.freq_subday_type = 1 then case
when sched.active_start_time = 0 then '00:00:00'
else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':')
end
else
case when sched.active_start_time = 0 then '00:00:00' else stuff(stuff(sched.active_start_time,3,0,':'),6,0,':') end
+ ' - '
+ case when sched.active_end_time = 0 then '00:00:00'else stuff(stuff(sched.active_end_time,3,0,':'),6,0,':') end
end
+ ' beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 64 then 'When SQL Server Agent service starts beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
when sched.freq_type = 128 then 'When computer is idle beginning '
+ stuff(stuff(sched.active_start_date,5,0,'-'),8,0,'-')
+ ' and ending '
+ case when sched.active_end_date = 99991231 then 'Never' else stuff(stuff(sched.active_end_date,5,0,'-'),8,0,'-') end
end
,next_run_date = case
when sched.freq_type = 64 and sched.enabled = 1 then 'When SQL Server Agent service starts'
when sched.freq_type = 128 and sched.enabled = 1 then 'When computer is idle'
else
case when jobsched.next_run_date = 0 then 'Never' else stuff(stuff(jobsched.next_run_date,5,0,'-'),8,0,'-') end
end
,next_run_time = case
when sched.freq_type = 64 and sched.enabled = 1 then 'When SQL Server Agent service starts'
when sched.freq_type = 128 and sched.enabled = 1 then 'When computer is idle'
else
case when jobsched.next_run_date = 0 then 'Never' else case when jobsched.next_run_time = 0 then '00:00:00' else stuff(stuff(jobsched.next_run_time,3,0,':'),6,0,':') end end
end
,schedule_enabled = sched.enabled
,sched.version_number
,sched.date_created
,sched.date_modified
from
msdb.dbo.sysschedules sched
inner join
msdb.dbo.sysjobschedules jobsched on
jobsched.schedule_id = sched.schedule_id
inner join
msdb.dbo.sysjobs jobs on
jobs.job_id = jobsched.job_id
SMO objects through C# can be used to create scripts with the DDL of SQL Agent jobs. An example of this is below. References to both the Microsoft.SqlServer.Management.Smo and System.Collections.Specialized (if using StringCollection) namespaces are necessary. To have this automated, create an SSIS package that has this as a Script Task, and execute the package as a job from SQL Agent. If any of your jobs have a \ in the title, this will need to be removed to avoid invalid file paths and the Replace method can be added to the Name property of the job below to do this.
Server srv = new Server(@"YourSQLServer");
srv.ConnectionContext.LoginSecure = true;
srv.ConnectionContext.StatementTimeout = 600;
srv.ConnectionContext.Connect();
Scripter script = new Scripter(srv);
ScriptingOptions scriptOpt = new ScriptingOptions();
foreach (Microsoft.SqlServer.Management.Smo.Agent.Job j in srv.JobServer.Jobs)
{
scriptOpt.FileName = @"C:\FolderPathForScripts\" + j.Name + ".sql";
StringCollection sc = j.Script(scriptOpt);
foreach (string s in sc)
Console.WriteLine(s);
}
Related