作业备份，不是备份数据库，是备份作业。
我的方法是把作业导出成文件备份起来，因为当你服务器维护的多了的时候很多你的作业 就很成问题，很麻烦。
最好能够作业实现同步，这个也是第一步，保存成文件，之后个人设想使用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
'
