SQL Server 作业的备份(备份作业非备份数据库)

作业备份，不是备份数据库，是备份作业。

我的方法是把作业导出成文件备份起来，因为当你服务器维护的多了的时候很多你的作业 就很成问题，很麻烦。

最好能够作业实现同步，这个也是第一步，保存成文件，之后个人设想使用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'