核心导出作业的 代码 和 作业备份是相似的
复制代码 代码如下:
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-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
有什么问题可以联系我：如果blog 的代码没办法使用也可以 加我qq 联系我，问我要。qq：32116057 fanr