手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >SQL Server 作业同步 (结合备份作业)
SQL Server 作业同步 (结合备份作业)
摘要:核心导出作业的代码和作业备份是相似的复制代码代码如下:alterPROCDumpJob(@jobVARCHAR(100))ASDECLARE...

核心导出作业的 代码 和 作业备份是相似的

复制代码 代码如下:

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

【SQL Server 作业同步 (结合备份作业)】相关文章:

SQL Server 2005“备份集中的数据库备份与现有的数据库不同”解决方法

SQL Server 中查看SQL句子执行所用的时间

SQL Server 2008的逻辑查询处理步骤

SQL Server 2005使用的网络协议

SQLServer CONVERT 函数测试结果

SQL Server 登录连接失败

SQL Server 2005 数据维护实务

SQL Server 2005 中做全文检索的方法分享

SQL Server 数据库备份和还原认识和总结

SQL Server的ldf和mdf文件转移

精品推荐
分类导航