The code for the core export job and the job backup are similar
Copy Code code as follows:
Alter PROC Dumpjob (@job VARCHAR (100))
As
DECLARE @retrun NVARCHAR (max)
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 @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 = 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 = ' BEGIN TRANSACTION '
SET @retrun = @retrun +char (+char) + ' DECLARE @ReturnCode INT '
SET @retrun = @retrun +char (+char) + ' IF not EXISTS (SELECT name from msdb.dbo.syscategories WHERE name=n ' + @categ Ory_name + ' and category_class= ' +rtrim (@category_calss_i) + ') '
SET @retrun = @retrun +char (+char) + ' BEGIN '
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_add_category @class =n ' + @category_calss + ' ', @type =n ' + @category_type + ', @name =n ' + @category_name + ' '
SET @retrun = @retrun +char (+char) + ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '
SET @retrun = @retrun +char (+char) + ' 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)
SET @retrun = @retrun +char (+char) + ' DECLARE @jobId BINARY (16) '
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name =n ' + @jobname + ' ', '
SET @retrun = @retrun +char (+char) + ' @enabled = ' +rtrim (@isenable) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_level_eventlog = ' +rtrim (@EventLogLevel) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_level_email = ' +rtrim (@EmailLevel) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_level_netsend = ' +rtrim (@NetSendLevel) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_level_page = ' +rtrim (@PageLevel) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_email_operator_name = ' ' +rtrim (@EmailLeveloprid) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_netsend_operator_name = ' ' +rtrim (@NetSendLeveloprid) + ', '
SET @retrun = @retrun +char (+char) + ' @notify_page_operator_name = ' ' +rtrim (@PageLeveloprid) + ', '
SET @retrun = @retrun +char (+char) + ' @delete_level = ' +rtrim (@delete_level) + ', '
SET @retrun = @retrun +char (+char) + ' @description =n ' + @description + ' ', '
SET @retrun = @retrun +char (+char) + ' @category_name =n ' + @category_name + ' ', '
SET @retrun = @retrun +char (+char) + ' @owner_login_name =n ' + @owner_log_name + ' ', '
SET @retrun = @retrun +char (+char) + ' @job_id = @jobId OUTPUT '
SET @retrun = @retrun +char (+char) + ' 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
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, '
SET @retrun = @retrun +char (+char) + ' @step_name =n ' + @step_name + ' ', '
SET @retrun = @retrun +char (+char) + ' @step_id = ' +rtrim (@step_id) + ', '
SET @retrun = @retrun +char (+char) + ' @cmdexec_success_code = ' +rtrim (@cmdexec_success_code) + ', '
SET @retrun = @retrun +char (+char) + ' @on_success_action = ' +rtrim (@on_success_action) + ', '
SET @retrun = @retrun +char (+char) + ' @on_success_step_id = ' +rtrim (@on_success_step_id) + ', '
SET @retrun = @retrun +char (+char) + ' @on_fail_action = ' +rtrim (@on_fail_action) + ', '
SET @retrun = @retrun +char (+char) + ' @on_fail_step_id = ' +rtrim (@on_fail_step_id) + ', '
SET @retrun = @retrun +char (+char) + ' @retry_attempts = ' +rtrim (@retry_attempts) + ', '
SET @retrun = @retrun +char (+char) + ' @retry_interval = ' +rtrim (@retry_interval) + ', '
SET @retrun = @retrun +char (+char) + ' @os_run_priority = ' +rtrim (@os_run_priority) + ', @subsystem =n ' + @subsystem + ''', '
SET @retrun = @retrun +char (+char) + ' @database_name =n ' + @database_name + ' ', '
SET @retrun = @retrun +char (+char) + ' @flags = ' +rtrim (@flags) + ', '
SET @retrun = @retrun +char (+char) + ' @command =n ' +replace (@command, ', ', ', ') + ' "'
SET @retrun = @retrun +char (+char) + ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '
FETCH NEXT from Jbcur into @step_id
End
Close Jbcur
Deallocate jbcur
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = ' +rtrim (@start_step_id)
SET @retrun = @retrun +char (+char) + ' 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
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name =n ' + @na Me+ ', '
SET @retrun = @retrun +char (+char) + ' @enabled = ' +rtrim (@enabled) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_type = ' +rtrim (@freq_type) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_interval = ' +rtrim (@freq_interval) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_subday_type = ' +rtrim (@freq_subday_type) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_subday_interval = ' +rtrim (@freq_subday_interval) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_relative_interval = ' +rtrim (@freq_relative_interval) + ', '
SET @retrun = @retrun +char (+char) + ' @freq_recurrence_factor = ' +rtrim (@freq_recurrence_factor) + ', '
SET @retrun = @retrun +char (+char) + ' @active_start_date = ' +rtrim (@active_start_date) + ', '
SET @retrun = @retrun +char (+char) + ' @active_end_date = ' +rtrim (@active_end_date) + ', '
SET @retrun = @retrun +char (+char) + ' @active_start_time = ' +rtrim (@active_start_time) + ', '
SET @retrun = @retrun +char (+char) + ' @active_end_time = ' +rtrim (@active_end_time) + ', '
SET @retrun = @retrun +char (+char) + ' @schedule_uid =n ' +rtrim (NEWID ()) + ""
SET @retrun = @retrun +char (+char) + ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '
End
SET @retrun = @retrun +char (+char) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N ' (local) '
SET @retrun = @retrun +char (+char) + ' IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback '
SET @retrun = @retrun +char (+char) + ' COMMIT TRANSACTION '
SET @retrun = @retrun +char (+char) + ' GOTO endsave '
SET @retrun = @retrun +char (+char) + ' Quitwithrollback: '
SET @retrun = @retrun +char (+char) + ' IF (@ @TRANCOUNT >0) ROLLBACK TRANSACTION '
SET @retrun = @retrun +char (+char) + ' Endsave: '
SET @retrun = @retrun +char (10) + "+char"
Select @retrun
I created a stored procedure to export the job, only with PowerShell scripts to achieve synchronization, you can powershell the script into the SQLAgent, timed to run the effect of synchronization
Here's the PowerShell code:
Copy Code code 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
There are any questions you can contact me: If the blog code can not be used or add me qq contact me, ask me. qq:32116057 FANR
