sql server 主从job对比差异

---查看job的基本信息
select  a.job_id,
  a.name, a.date_created ,
  a.date_modified ,case when a.enabled='1' then N'是'
             when a.enabled='0' then N'否' end as enabled ,a.description,  
  b.step_id,b.step_name,
  b.subsystem,b.command,
  b.database_name,
  b.last_run_date
 from msdb.dbo.sysjobs a  left join msdb.dbo.sysjobsteps  b on a.job_id=b.job_id 
 where  a.enabled='1'  
 order by a.job_id,b.step_id  

---对比

select b.* ,a.*,
case when   sec_c =  pri_c then '相同' 
     when  pri_c is not null and sec_c is null then '需要新增'
 else  '差异'
end as result

from 
( 
 select   a.name  as sec_name,a.step_id as sec_stepid, a.step_name as sec_stepname,
 cast (a.command as nvarchar(1000))  as sec_command, a.database_name as sec_dbname,
 a.name+  cast(  cast( a.step_id as varchar(2))+ a.step_name+a.subsystem+a.command+ isnull(database_name,'test') as   nvarchar(2000)) as sec_c 
 from job_sec a
 ) a full join 
 (  
  select b.name as pri_name,b.step_id as pri_stepid, b.step_name as pri_stepname,
 cast (b.command as nvarchar(1000)) as pri_command,
  b.database_name as pri_dbname,
 cast(  b.name+ cast( b.step_id as varchar(2))+ b.step_name+b.subsystem+b.command+ isnull(database_name,'test') as   nvarchar(2000))    as pri_c
  from job_job b
 ) b on  sec_c=  pri_c
 order by  case when   sec_c =  pri_c then '相同' 
     when  pri_c is not null and sec_c is null then '需要新增'
 else  '差异'
end 

通过第一个sql查询job的基本信息导出为excel， 然后分别把主从库的job信息导入到表 job_pri,job_sec,然后通过第二条语句对比

原文地址：https://blog.csdn.net/woshilys/article/details/143947054

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！