首页 / 数据库 / SQLServer / SQL Server 作业同步 （结合备份作业）

核心导出作业的 代码 和 作业备份是相似的
复制代码 代码如下:
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 = " BEGIN TRANSACTION"
SET @retrun = @retrun+CHAR（13）+CHAR（10） + "DECLARE @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.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（13）+CHAR（10） + "DECLARE @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） + " @notify_level_eventlog="+RTRIM（@EventLogLevel）+", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_level_email="+RTRIM（@EmailLevel）+", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_level_netsend="+RTRIM（@NetSendLevel）+", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_level_page="+RTRIM（@PageLevel）+", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_email_operator_name ="""+RTRIM（@EmailLeveloprid）+""", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_netsend_operator_name="""+RTRIM（@NetSendLeveloprid）+""", "
SET @retrun = @retrun+CHAR（13）+CHAR（10） + " @notify_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 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（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

我创建了一个存储过程，用来导出 作业，只有用powershell 脚本来实现同步，你可以powershell 脚本放入 sqlagent 中 定时运行起到同步的效果

一下是powershell 代码：
复制代码 代码如下:
$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-pcsql2012; 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

有什么问题可以联系我：如果blog 的代码没办法使用也可以 加我qq 联系我，问我要。qq：32116057 fanr