A job backup, not a backup database, is a backup job.
My approach is to export the job as a file backup, because when your server maintenance more than a lot of your homework is very problematic, very troublesome.
It is best to be able to work to achieve synchronization, this is also the first step, saved into a file, after the individual imagine using PowerShell to synchronize the work
Copy Code code as follows:
DECLARE @jobname VARCHAR (m), @category_calss_i INT, @category_calss VARCHAR (), @category_name VARCHAR (50)
, @category_type VARCHAR, @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 = 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= ' +r Trim (@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 (a), @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 (), @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 (@database_name NVARCHAR), @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 '
