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

$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