SQL Server Job synchronization (combined with backup job)

The code of the core export job is similar to that of the job backup
Copy codeThe Code is as follows:
Alter PROC DumpJob (@ job VARCHAR (100 ))
AS
DECLARE @ retrun NVARCHAR (max)
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 @ jobname = @ job
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)

SET @ retrun = 'in in transaction'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'Clare @ ReturnCode int'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'If not exists (SELECT name FROM msdb. dbo. syscategories WHERE name = n''' + @ category_name + ''' AND category_class = '+ rtrim (@ category_calss_ I) + ')'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'begin'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_add_category @ class = n''' + @ category_calss + ''', @ type = n''' + @ category_type + ''', @ name = n''' + @ category_name + ''''
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'if (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO quitwithrollback'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '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)


SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'Clare @ jobId BINARY (16 )'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_add_job @ job_name = n''' + @ jobname + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ enabled =' + RTRIM (@ isenable) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_level_eventlog =' + RTRIM (@ EventLogLevel) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_level_email =' + RTRIM (@ EmailLevel) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_level_netsend =' + RTRIM (@ NetSendLevel) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_level_page =' + RTRIM (@ PageLevel) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_email_operator_name = ''' + RTRIM (@ EmailLeveloprid) + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_netsend_operator_name = ''' + RTRIM (@ NetSendLeveloprid) + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ yy_page_operator_name = ''' + RTRIM (@ PageLeveloprid) + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ delete_level =' + RTRIM (@ delete_level) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ description = n''' + @ description + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ category_name = n''' + @ category_name + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ owner_login_name = n''' + @ owner_log_name + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ job_id = @ jobId OUTPUT'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '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

SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_add_jobstep @ job_id = @ jobId ,'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ step_name = n''' + @ step_name + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ step_id =' + RTRIM (@ step_id) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ cmdexec_success_code =' + RTRIM (@ cmdexec_success_code) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ on_success_action =' + RTRIM (@ on_success_action) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ on_success_step_id =' + RTRIM (@ on_success_step_id) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ on_fail_action =' + RTRIM (@ on_fail_action) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ on_fail_step_id =' + RTRIM (@ on_fail_step_id) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ retry_attempts =' + RTRIM (@ retry_attempts) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ retry_interval =' + RTRIM (@ retry_interval) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ OS _run_priority =' + RTRIM (@ OS _run_priority) + ', @ subsystem = n''' + @ subsystem + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ database_name = n''' + @ database_name + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ flags =' + RTRIM (@ flags) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ command = n' + REPLACE (@ command ,'''', ''''') + ''''
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'if (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO quitwithrollback'

Fetch next from jbcur INTO @ step_id

END

CLOSE jbcur
DEALLOCATE jbcur

SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_update_job @ job_id = @ jobId, @ start_step_id = '+ rtrim (@ start_step_id)
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '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
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_add_jobschedule @ job_id = @ jobId, @ name = n''' + @ name + ''','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ enabled =' + RTRIM (@ enabled) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_type =' + RTRIM (@ freq_type) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_interval =' + RTRIM (@ freq_interval) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_subday_type =' + RTRIM (@ freq_subday_type) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_subday_interval =' + RTRIM (@ freq_subday_interval) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_relative_interval =' + RTRIM (@ freq_relative_interval) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ freq_recurrence_factor =' + RTRIM (@ freq_recurrence_factor) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ active_start_date =' + RTRIM (@ active_start_date) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ active_end_date =' + RTRIM (@ active_end_date) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ active_start_time =' + RTRIM (@ active_start_time) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ active_end_time =' + RTRIM (@ active_end_time) + ','
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + '@ schedule_uid = n''' + RTRIM (NEWID () + ''''
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'if (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO quitwithrollback'
END


SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'exec @ ReturnCode = msdb. dbo. sp_add_jobserver @ job_id = @ jobId, @ server_name = n'' (local )'''
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'if (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO quitwithrollback'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'commit transaction'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'Goto EndSave'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'quitwithrollback :'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'if (@ TRANCOUNT> 0) ROLLBACK transaction'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) + 'endsave :'
SET @ retrun = @ retrun + CHAR (13) + CHAR (10) +''

Select @ retrun

I created a stored procedure for exporting jobs. Only the powershell script can be used for synchronization. You can run the powershell script in the sqlagent periodically for synchronization.

Here is the powershell code:
Copy codeThe Code is as follows:
$ Server = "(local )"
$ Uid = "sa"
$ Db = "master"
$ Pwd = "fanzhouqi"
$ Mailprfname = "sina"
$ Recipients = "32116057@qq.com"
$ Subject = 'System Log'
Function execproc ($ message)
{
$ SqlConnection = New-Object System. Data. SqlClient. SqlConnection
$ CnnString = "Server = $ server; Database = $ db; User Id = $ uid; Password = $ pwd"
$ SqlConnection. ConnectionString = $ CnnString
$ CC = $ SqlConnection. CreateCommand ();

$ CC. CommandText = $ message
$ Adapter = New-Object System. Data. SqlClient. SqlDataAdapter $ CC
$ Dataset = New-Object System. Data. DataSet
# $ SqlConnection. SelectCommand = $ CC
If (-not ($ SqlConnection. State-like "Open") {$ SqlConnection. Open ()}

$ Adapter. Fill ($ dataset) | out-null
$ Dataset. Tables [0]. Rows [0] [0]
$ SqlConnection. Close ();
}
Function execsql ($ message)
{
$ SqlConnection = New-Object System. Data. SqlClient. SqlConnection
$ CnnString = "Server = fanr-pc \ sql2012; Database = $ db; User Id = $ uid; Password = $ pwd"
$ SqlConnection. ConnectionString = $ CnnString
$ CC = $ SqlConnection. CreateCommand ();
If (-not ($ SqlConnection. State-like "Open") {$ SqlConnection. Open ()}

$ Cc. CommandText = $ message
$ Cc. ExecuteNonQuery () | out-null
$ SqlConnection. Close ();
}
$ Jobscript = execproc "EXEC master... DumpJob @ job = 'backup '"
# $ Jobscript
Execsql $ jobscript

If you have any questions, contact me: if the blog Code cannot be used, add qq to contact me and ask me for help. Qq: 32116057 fanr