Backup of SQL Server jobs

A job backup, not a backup database, is a backup job.
My method is to export the job as a file backup, because when your server maintenance more time many of your homework is very problematic, very troublesome.
It's best to be able to work with synchronization, which is also the first step, saved as a file, and then personally conceived to use PowerShell to sync the job.

DECLARE @jobname varchar (in), @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 If Tshc.category_class = 1 Then ' JOB '
When tshc.category_class = 2 Then ' ALERT '
Else ' OPERATOR ' www.2cto.com
END
, @category_type = case if 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
([email protected] 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 ' [Email protected]_type+ ", @name =n" [Email protected]_name+ "]
PRINT ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '
PRINT ' End '

DECLARE @EventLogLevel int, @EmailLevel int, @NetSendLevel int, @PageLevel int
DECLARE @EmailLeveloprid NVARCHAR, @NetSendLeveloprid NVARCHAR (@PageLeveloprid NVARCHAR (256)
DECLARE @isenable int, @description NVARCHAR (1024x768), @owner_log_name NVARCHAR, @delete_level int www.2cto.com
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 ([email protected] and sv.category_id=0)

PRINT ' DECLARE @jobId BINARY (16) '
PRINT ' EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name =n ' [email protected]+ ', '
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 ' [email protected]+ ', '
PRINT ' @category_name =n ' [email protected]_name+ ', '
PRINT ' @owner_login_name =n ' [email protected]_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, @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 to @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 @[email protected], '
PRINT ' @step_name =n ' [email protected]_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 ' [email protected]+ ', ' www.2cto.com
PRINT ' @database_name =n ' [email protected]_name+ ', '
PRINT ' @flags = ' +rtrim (@flags) + ', '
PRINT ' @command =n ' ' +replace (@command, ' ', ' ') + ' "
PRINT ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '

FETCH NEXT from Jbcur to @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
Www.2cto.com
IF (@name is not null)
Begin
PRINT ' EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name =n ' [email protected]+ ', '
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 www.2cto.com

PRINT ' EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N "" [Email protected]+ "]
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 '

Backup of SQL Server jobs