SQL Server作业的备份(二)
id)+''', '
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' www.2cto.com
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+''', ' www.2cto.com
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 = e