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"