Backup of SQL Server jobs (backup jobs are not backup databases)

Job backup, not a backup database, is a backup job.

My method is to export a job as a file for backup, because when your server is too large to maintain, many of your jobs become very problematic and very troublesome.

It is best to synchronize jobs. This is also the first step. Save the job as a file. After that, I imagine using powershell to synchronize jobs.
Copy codeThe Code is as follows:
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 'in in transaction'
PRINT 'Clare @ 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. yy_level_eventlog
, @ EmailLevel = sv. policy_level_email
, @ NetSendLevel = sv. policy_level_netsend
, @ PageLevel = sv. policy_level_page
, @ EmailLeveloprid = ISNULL (select top 1 name FROM msdb... sysoperators WHERE id = sv. policy_email_operator_id ),'')
, @ NetSendLeveloprid = ISNULL (select top 1 name FROM msdb... sysoperators WHERE id = sv. policy_netsend_operator_id ),'')
, @ PageLeveloprid = ISNULL (select top 1 name FROM msdb... sysoperators WHERE id = sv. policy_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 'maid @ jobId BINARY (16 )'
PRINT 'exec @ ReturnCode = msdb. dbo. sp_add_job @ job_name = n''' + @ jobname + ''','
PRINT '@ enabled =' + RTRIM (@ isenable) + ','
PRINT '@ policy_level_eventlog =' + RTRIM (@ EventLogLevel) + ','
PRINT '@ policy_level_email =' + RTRIM (@ EmailLevel) + ','
PRINT '@ policy_level_netsend =' + RTRIM (@ NetSendLevel) + ','
PRINT '@ policy_level_page =' + RTRIM (@ PageLevel) + ','
PRINT '@ policy_email_operator_name = ''' + RTRIM (@ EmailLeveloprid) + ''','
PRINT '@ yy_netsend_operator_name = ''' + RTRIM (@ NetSendLeveloprid) + ''','
PRINT '@ yy_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
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'