/******************************************************************
【概要说明】
<创建时间> 2015/6/8 11:14
<中文名称> 作业迁移
<功能说明> 通过insert、update语句导入作业信息，目的是将实例A中的作业迁移到实例B中
<调用方式> 手动执行
<执行说明> 先决条件：还原前请先备份两边的msdb数据库，将实例A中的msdb还原到实例B的msdbnew
出错处理：如果出错，请还原原备份文件
注意事项：此代码适用用于数据库版本不一致，且迁移的作业比较多的情况；如果版本号相同，可直接还原数据库
【修订记录】
-------------------------------------------------------------------
<2015/6/8 11:14> : 创建
******************************************************************/
--作业Job
insert into [msdb].[dbo].[sysjobs]
SELECT a.[job_id]
,a.[originating_server_id]
,‘109Job_‘+a.[name]--作业名称加前缀区分
,a.[enabled]
,a.[description]
,a.[start_step_id]
,a.[category_id]
,suser_sid()--将所有者更新为当前登录用户
,a.[notify_level_eventlog]
,a.[notify_level_email]
,a.[notify_level_netsend]
,a.[notify_level_page]
,a.[notify_email_operator_id]
,a.[notify_netsend_operator_id]
,a.[notify_page_operator_id]
,a.[delete_level]
,a.[date_created]
,a.[date_modified]
,a.[version_number]
FROM [msdbnew].[dbo].[sysjobs] a
left join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
where a.enabled=1
and b.job_id is null
order by date_created
go
--步骤jobsteps
insert into [msdb].[dbo].[sysjobsteps]
SELECT a.[job_id]
,a.[step_id]
,a.[step_name]
,a.[subsystem]
,a.[command]
,a.[flags]
,a.[additional_parameters]
,a.[cmdexec_success_code]
,a.[on_success_action]
,a.[on_success_step_id]
,a.[on_fail_action]
,a.[on_fail_step_id]
,a.[server]
,a.[database_name]
,a.[database_user_name]
,a.[retry_attempts]
,a.[retry_interval]
,a.[os_run_priority]
,a.[output_file_name]
,a.[last_run_outcome]
,a.[last_run_duration]
,a.[last_run_retries]
,a.[last_run_date]
,a.[last_run_time]
,a.[proxy_id]
,a.[step_uid]
FROM [msdbnew].[dbo].[sysjobsteps] a
inner join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
left join [msdb].[dbo].[sysjobsteps] c
on a.job_id=c.job_id
where c.job_id is null
go
--调度schedules
insert into [msdb].[dbo].[sysschedules]
SELECT a.[schedule_uid]
,a.[originating_server_id]
,a.[name]
,suser_sid()--将所有者更新为当前登录用户
,a.[enabled]
,a.[freq_type]
,a.[freq_interval]
,a.[freq_subday_type]
,a.[freq_subday_interval]
,a.[freq_relative_interval]
,a.[freq_recurrence_factor]
,a.[active_start_date]
,a.[active_end_date]
,a.[active_start_time]
,a.[active_end_time]
,a.[date_created]
,a.[date_modified]
,a.[version_number]
FROM [msdbnew].[dbo].[sysschedules] a
inner join [msdbnew].[dbo].[sysjobschedules] b
on a.schedule_id=b.schedule_id
inner join [msdb].[dbo].[sysjobs] c
on b.job_id=c.job_id
left join [msdb].[dbo].[sysschedules] d
on a.schedule_uid=d.schedule_uid
where d.schedule_uid is null
go
--关联job_id和schedule_id
insert into [msdb].[dbo].[sysjobschedules]
SELECT c.schedule_id
,a.[job_id]
,a.[next_run_date]
,a.[next_run_time]
FROM [msdbnew].[dbo].[sysjobschedules] a
inner join [msdbnew].[dbo].[sysschedules] b
on a.schedule_id=b.schedule_id
inner join [msdb].[dbo].[sysschedules] c
on b.schedule_uid=c.schedule_uid
left join [msdb].[dbo].[sysjobschedules] d
on a.job_id=d.job_id
where d.job_id is null
go
--指定目标服务器
insert into [msdb].[dbo].[sysjobservers]
SELECT a.[job_id]
,a.[server_id]
,a.[last_run_outcome]
,a.[last_outcome_message]
,a.[last_run_date]
,a.[last_run_time]
,a.[last_run_duration]
FROM [msdbnew].[dbo].[sysjobservers] a
inner join [msdb].[dbo].[sysjobs] b
on a.job_id=b.job_id
left join [msdb].[dbo].[sysjobservers] c
on a.job_id=c.job_id
where c.job_id is null
go
/********如果对作业的所有者有特殊要求，参考下面代码进行修改********/
--Start
--原实例下获取作业所有者
select sj.name,sj.date_created,sj.date_modified,ssp.name OwnName,sj.owner_sid
from msdb.dbo.sysjobs sj
inner join sys.server_principals ssp
on sj.owner_sid=ssp.sid
where ssp.name=‘USER_JOB‘
order by sj.name
--新实例下更新需要修改的所有者
update msdb.dbo.sysjobs
set owner_sid=suser_sid(‘user_job‘)
where job_id in(
select job_id from msdbnew.dbo.sysjobs
where owner_sid=convert(varbinary,0x047EC52E6005D241932C0DC5F0827615))--原作业所有者
update [msdb].[dbo].[sysschedules]
set owner_sid=suser_sid(‘user_job‘)
where schedule_id in(
select schedule_id from [msdbnew].[dbo].[sysschedules]
where owner_sid=convert(varbinary,0x047EC52E6005D241932C0DC5F0827615))--原作业所有者
--End
--上面操作完成后，作业并不会按计划执行作业。可用下面方式中的任意一种使作业按计划运行
--1重启代理服务;2禁用作业再启用作业;3修改并保存作业
--应该可以直接执行某个语句(暂时没找到)
京公网安备 11010802041100号