Compare SQL Server Agent jobs

nawarricknawarrick Posts: 9 New member
edited February 18, 2016 4:27PM in SQL Compare Previous Versions
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

Comments

  • 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
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • Matt_BMatt_B Posts: 12 Bronze 2
    I would use this, so +1.
  • I would use this, when will it be available?
  • Matt_B wrote:
    I would use this, so +1.

    I would as well! +1
  • Can someone at RedGate give us an update when SQL Data Compare will be able to compare system tables?
  • opc.threeopc.three Posts: 17 New member
    Anyone know the status of SC-2456 (mentioned by peter.peart in a 2012 post on this thread)?
  • We are still interested in this feature is there an update.  Any status updates from this thread?
  • LxocramLxocram Posts: 25 Bronze 3
    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
Sign In or Register to comment.