当前位置：首页 > article >正文

sql server 主从job对比差异

article 2024/11/26 22:18:43



	---查看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,然后通过第二条语句对比

查看全文

http://www.kler.cn/a/410529.html