Compare SQL Server Agent jobs

• 
  Hi there,
  
  Unfortuantely, we don't have anything in our arsenal that will do this I am afraid, however we do already have a feature request under ref SC-2456 which is to compare system databases.
  
  I am afraid to say though that currently we do not know when / if this will be implemented or not, however the more people post requesting the feature the more likely it is to be implemented.
  
  Pete

  peter.peart July 22, 2010 14:15

  - 0 +
• 
  I would use this, so +1.

  Matt_B December 06, 2011 02:20

  - 0 +
• 
  I would use this, when will it be available?

  aspnerd March 26, 2012 14:11

  - 0 +
• 

  Matt_B wrote:

  I would use this, so +1.

  
  I would as well! +1

  rmrussell1970 November 21, 2012 14:05

  - 0 +
• 
  Can someone at RedGate give us an update when SQL Data Compare will be able to compare system tables?

  rmsterling November 19, 2015 06:30

  - 0 +
• 
  Anyone know the status of SC-2456 (mentioned by peter.peart in a 2012 post on this thread)?

  opc.three February 18, 2016 16:27

  - 0 +
• 
  We are still interested in this feature is there an update.  Any status updates from this thread?
  

  RedGateThompson December 04, 2019 12:33

  - 0 +
• 
  One way to do this is to add views to the msdb databases on both nodes/servers that act as a non-system-object "proxy"
  You can then use SQL DATA Compare with the option "include views"
  and then set comparison keys in SQL DATA COMPARE tables & views to use the names instead of uuids
  and then exclude identifier/modified/version columns from comparison
  
  e.g
  
  

  USE msdb;

  GO

  CREATE OR ALTER VIEW sysjobs_proxy

  AS

  SELECT *

  FROM msdb.dbo.sysjobs AS j;

  GO

  CREATE OR ALTER VIEW sysjobsteps_proxy

  AS

  SELECT st.*,

         j.job_id AS j_job_id,

         j.name AS job_name

  FROM dbo.sysjobsteps AS st

      INNER JOIN dbo.sysjobs AS j

          ON j.job_id = st.job_id;

  GO

  CREATE OR ALTER VIEW sysjobschedules_proxy

  AS

  SELECT jsc.*,

         j.job_id AS j_job_id,

         j.name AS job_name,

         sc.name AS shedule_name,

         sc.schedule_id AS sc_schedule_id

  FROM msdb.dbo.sysjobschedules AS jsc

      INNER JOIN dbo.sysjobs AS j

          ON j.job_id = jsc.job_id

      INNER JOIN dbo.sysschedules AS sc

          ON sc.schedule_id = jsc.schedule_id;

  GO

  CREATE OR ALTER VIEW sysschedules_proxy

  AS

  SELECT *

  FROM msdb.dbo.sysschedules AS s;
  
  However - this does not yield any usable scripts to switch job-step order etc..
  It's easier to script out the job with DROP and CREATE and alter top portion
  

  
  IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name='JOBNAME'

  BEGIN

  DECLARE @jobId BINARY(16)

  SELECT @jobId=job_id FROM msdb.dbo.sysjobs WHERE name='JOBNAME'

  EXEC msdb.dbo.sp_delete_job @job_id=@jobId, @delete_unused_schedule=1

  END

  Lxocram July 07, 2021 08:06

  - 0 +

Add comment

Please sign in to leave a comment.