Compare SQL Server Agent jobs

nawarrick

Hi,

Is there a way to quickly compare SQL Server Agent jobs, either with T-SQL scripts or with another server?

Thanks in Advance,
Nigel

peter.peart

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

Matt_B

I would use this, so +1.

aspnerd

I would use this, when will it be available?

rmrussell1970

Matt_B wrote:

I would use this, so +1.

I would as well! +1

rmsterling

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

opc.three

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

RedGateThompson

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

Lxocram

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