作业备份，不是备份数据库，是备份作业。
我的方法是把作业导出成文件备份起来，因为当你服务器维护的多了的时候很多你的作业 就很成问题，很麻烦。
最好能够作业实现同步，这个也是第一步，保存成文件，之后个人设想使用powershell 来把作业同步起来
复制代码 代码如下:
declare @jobname varchar(30),@category_calss_i int ,@category_calss varchar(50),@category_name varchar(50)
,@category_type varchar(30),@category_id int
,@category_type_i int
select @jobname = ‘powershell’,@category_calss = ”,@category_name=”,@category_type = ”
select @category_calss = case when tshc.category_class = 1 then ‘job’
when tshc.category_class = 2 then ‘alert’
else ‘operator’
end
, @category_type = case when tshc.category_type = 1 then ‘local’
when tshc.category_type = 2 then ‘multi-server’
else ‘none’
end
,@category_name = tshc.name
,@category_type_i = category_type
,@category_calss_i = tshc.category_class
,@category_id = tshc.category_id
from
msdb.dbo.sysjobs_view as sv
inner join msdb.dbo.syscategories as tshc on sv.category_id = tshc.category_id
where
(sv.name=@jobname and tshc.category_class = 1)
print ‘ begin transaction’
print ‘declare @returncode int’
print ‘if not exists (select name from msdb.dbo.syscategories where name=n”’ + @category_name +”’and category_class=’ +rtrim(@category_calss_i)+’)’
print ‘begin’
print ‘exec @returncode = msdb.dbo.sp_add_category @class=n”’+ @category_calss+”’, @type=n”’+@category_type+”’, @name=n”’+@category_name+””
print ‘if (@@error <> 0 or @returncode <> 0) goto quitwithrollback’
print ‘end’
declare @eventloglevel int,@emaillevel int,@netsendlevel int,@pagelevel int
declare @emailleveloprid nvarchar(256),@netsendleveloprid nvarchar(256),@pageleveloprid nvarchar(256)
declare @isenable int , @description nvarchar(1024),@owner_log_name nvarchar(512),@delete_level int
declare @jobid uniqueidentifier,@start_step_id int,@server nvarchar(512)
select
@eventloglevel=sv.notify_level_eventlog
,@emaillevel=sv.notify_level_email
,@netsendlevel=sv.notify_level_netsend
,@pagelevel=sv.notify_level_page
,@emailleveloprid = isnull((select top 1 name from msdb..sysoperators where id = sv.notify_email_operator_id),”)
,@netsendleveloprid = isnull((select top 1 name from msdb..sysoperators where id = sv.notify_netsend_operator_id),”)
,@pageleveloprid = isnull((select top 1 name from msdb..sysoperators where id = sv.notify_page_operator_id),”)
,@isenable = sv.enabled
,@description = sv.description
,@owner_log_name = isnull(suser_sname(sv.owner_sid), n””)
,@delete_level = sv.delete_level
,@jobid = sv.job_id
,@start_step_id = start_step_id
,@server = originating_server
from msdb.dbo.sysjobs_view as sv
where (sv.name=@jobname and sv.category_id=0)
print ‘declare @jobid binary(16)’
print ‘exec @returncode = msdb.dbo.sp_add_job @job_name=n”’+@jobname+”’,’
print ‘ @enabled=’+rtrim(@isenable)+’, ‘
print ‘ @notify_level_eventlog=’+rtrim(@eventloglevel)+’, ‘
print ‘ @notify_level_email=’+rtrim(@emaillevel)+’, ‘
print ‘ @notify_level_netsend=’+rtrim(@netsendlevel)+’, ‘
print ‘ @notify_level_page=’+rtrim(@pagelevel)+’, ‘
print ‘ @notify_email_operator_name =”’+rtrim(@emailleveloprid)+”’, ‘
print ‘ @notify_netsend_operator_name=”’+rtrim(@netsendleveloprid)+”’, ‘
print ‘ @notify_page_operator_name=”’+rtrim(@pageleveloprid)+”’, ‘
print ‘ @delete_level=’+rtrim(@delete_level)+’, ‘
print ‘ @description=n”’+@description+”’, ‘
print ‘ @category_name=n”’+@category_name+”’, ‘
print ‘ @owner_login_name=n”’+@owner_log_name+”’, ‘
print ‘ @job_id = @jobid output’
print ‘if (@@error <> 0 or @returncode <> 0) goto quitwithrollback’
–select * from msdb.dbo.syscategories
declare @step_id int
declare @step_name nvarchar(512) ,@cmdexec_success_code int,@on_success_action int,@on_success_step_id int
,@on_fail_action int,@on_fail_step_id int,@retry_attempts int,@retry_interval int,@os_run_priority int
,@subsystem nvarchar(512),@database_name nvarchar(512),@flags int,@command nvarchar(max)
declare jbcur cursor for select step_id from msdb..sysjobsteps where job_id = @jobid order by step_id ;
open jbcur;
fetch next from jbcur into @step_id
while @@fetch_status = 0
begin
select @step_name = step_name
,@cmdexec_success_code= cmdexec_success_code
,@on_success_action = on_success_action
,@on_success_step_id = on_success_step_id
,@on_fail_action = on_fail_action
,@on_fail_step_id = on_fail_step_id
,@retry_attempts = retry_attempts
,@retry_interval = retry_interval
,@os_run_priority = os_run_priority
,@subsystem = subsystem
,@database_name = database_name
,@command = command
,@flags = flags
from msdb..sysjobsteps a where job_id = @jobid and step_id = @step_id
print ‘ exec @returncode = msdb.dbo.sp_add_jobstep @job_id=@jobid, ‘
print ‘ @step_name=n”’+@step_name+”’, ‘
print ‘ @step_id=’+rtrim(@step_id)+’, ‘
print ‘ @cmdexec_success_code=’+rtrim(@cmdexec_success_code)+’, ‘
print ‘ @on_success_action=’+rtrim(@on_success_action)+’, ‘
print ‘ @on_success_step_id=’+rtrim(@on_success_step_id)+’, ‘
print ‘ @on_fail_action=’+rtrim(@on_fail_action)+’, ‘
print ‘ @on_fail_step_id=’+rtrim(@on_fail_step_id)+’, ‘
print ‘ @retry_attempts=’+rtrim(@retry_attempts)+’, ‘
print ‘ @retry_interval=’+rtrim(@retry_interval)+’, ‘
print ‘ @os_run_priority=’+rtrim(@os_run_priority)+’, @subsystem=n”’+@subsystem+”’, ‘
print ‘ @database_name=n”’+@database_name+”’,’
print ‘ @flags=’+rtrim(@flags)+’ ,’
print ‘ @command=n”’+replace(@command,””,”””)+””
print ‘ if (@@error <> 0 or @returncode <> 0) goto quitwithrollback’
fetch next from jbcur into @step_id
end
close jbcur
deallocate jbcur
print ‘ exec @returncode = msdb.dbo.sp_update_job @job_id = @jobid, @start_step_id = ‘+rtrim(@start_step_id)
print ‘ if (@@error <> 0 or @returncode <> 0) goto quitwithrollback ‘
declare @enabled int,@freq_type int,@freq_interval int,@freq_subday_type int,@freq_subday_interval int
,@freq_relative_interval int,@freq_recurrence_factor int,@active_start_date int,@active_end_date int
,@active_start_time int,@active_end_time int,@name varchar(512)
select
@name = a.name
,@enabled = enabled
,@freq_interval = freq_interval
,@freq_type = freq_type
,@freq_subday_type=freq_subday_type
,@freq_subday_interval=freq_subday_interval
,@freq_relative_interval=freq_relative_interval
,@freq_recurrence_factor=freq_recurrence_factor
,@active_start_date=active_start_date
,@active_end_date=active_end_date
,@active_start_time=active_start_time
,@active_end_time=active_end_time
from msdb..sysschedules a
inner join msdb.dbo.sysjobschedules b on a.schedule_id = b.schedule_id
where job_id = @jobid
if(@name is not null)
begin
print ‘ exec @returncode = msdb.dbo.sp_add_jobschedule @job_id=@jobid, @name=n”’+@name+”’, ‘
print ‘ @enabled=’+rtrim(@enabled)+’, ‘
print ‘ @freq_type=’+rtrim(@freq_type)+’, ‘
print ‘ @freq_interval=’+rtrim(@freq_interval)+’, ‘
print ‘ @freq_subday_type=’+rtrim(@freq_subday_type)+’, ‘
print ‘ @freq_subday_interval=’+rtrim(@freq_subday_interval)+’, ‘
print ‘ @freq_relative_interval=’+rtrim(@freq_relative_interval)+’, ‘
print ‘ @freq_recurrence_factor=’+rtrim(@freq_recurrence_factor)+’, ‘
print ‘ @active_start_date=’+rtrim(@active_start_date)+’, ‘
print ‘ @active_end_date=’+rtrim(@active_end_date)+’, ‘
print ‘ @active_start_time=’+rtrim(@active_start_time)+’, ‘
print ‘ @active_end_time=’+rtrim(@active_end_time)+’, ‘
print ‘ @schedule_uid=n”’+rtrim(newid())+””
print ‘ if (@@error <> 0 or @returncode <> 0) goto quitwithrollback’
end
print ‘ exec @returncode = msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = n”’+@server+””
print ‘ if (@@error <> 0 or @returncode <> 0) goto quitwithrollback’
print ‘commit transaction’
print ‘goto endsave’
print ‘quitwithrollback:’
print ‘ if(@@trancount>0)rollback transaction’
print ‘endsave:’
print ‘ ‘
print ‘go’