Job_EnableJobsThatWereEnabled

This will be referred to in the next post.

USE Admin

GO

IF OBJECT_ID('dbo.Job_EnableJobsThatWereEnabled') Is Not Null

      DROP PROC dbo.Job_EnableJobsThatWereEnabled

GO

CREATE   PROC dbo.Job_EnableJobsThatWereEnabled

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS    

      6/19/12           Paiva       Initial creation.

      DESCRIPTION

            Enables the jobs that were previously disabled

                  via Job_DisableJobsThatAreEnabled.

      USAGE

            Job_EnableJobsThatWereEnabled 1

            Job_EnableJobsThatWereEnabled

      DEBUG

            SELECT Enabled, *

            FROM msdb.dbo.JobsBUPreviouslyEnabled

*/

SET NOCOUNT ON

DECLARE @LastBUDate datetime,

            @NumEnabled smallint,

            @NumDisabled smallint,

            @Sql varchar(300)

IF OBJECT_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null                   

      BEGIN

            -- Get stats of last backkup

            SELECT @LastBUDate = Max(InsertDate),

                        @NumEnabled = Count(*)

            FROM msdb.dbo.JobsBUPreviouslyEnabled

            WHERE Enabled = 1

            SELECT @LastBUDate = Max(InsertDate),

                        @NumDisabled = Count(*)

            FROM msdb.dbo.JobsBUPreviouslyEnabled

            WHERE Enabled = 0

      END

ELSE

      BEGIN

            SET @Sql = 'Cannot enable jobs that were previously disabled because a backup was not made.'

            SET @Sql = @Sql + Char(13) + Char(10) + 'To make backup and disable jobs use < Job_DisableJobsThatAreEnabled >.'

            RaisError (@Sql, 16, 1)

            RETURN

      END

IF @Debug = 1

      BEGIN

            SET @Sql = '    Date of last backup:  ' + Convert(varchar, @LastBUDate, 120)

            SET @Sql = @Sql + Char(13) + Char(10) + ' Num Previously Enabled:  ' + Convert(varchar, @NumEnabled)

            SET @Sql = @Sql + Char(13) + Char(10) + 'Num Previously Disabled:  ' + Convert(varchar, @NumDisabled)

            Print @Sql

            RETURN

      END

-- Re-Enable the previously enabled jobs

DECLARE @JobID varchar(38),

            @MaxJobID varchar(38),

            @JobName nvarchar(256),

            @Num smallint

SELECT  @MaxJobID = Max(Convert(varchar(38), Job_ID)),

            @Num = Count(*),

            @JobID = ''

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

BEGIN TRANSACTION

WHILE @JobID < @MaxJobID

      BEGIN

            -- Get next JobID

            SELECT @JobID = Min(Convert(varchar(38), Job_ID))

            FROM msdb.dbo.JobsBUPreviouslyEnabled

            WHERE Enabled = 1

              AND Convert(varchar(38), Job_ID) > @JobID

            -- Get other attributes

            SELECT @JobName = Name

            FROM msdb.dbo.JobsBUPreviouslyEnabled

            WHERE Convert(varchar(38), Job_ID) = @JobID

            SET @Sql = 'Exec msdb.dbo.sp_update_job @Job_ID = ''' + @JobID + ''', @Enabled = 1'

            --Print @Sql     

            Exec (@Sql)

      END

      Exec dbo.DropObjectIfExists 'u', 'msdb', 'dbo', 'JobsBUPreviouslyEnabled'

COMMIT

Print 'Number of jobs that were enabled:  ' + Convert(varchar, @Num)

SELECT Name CurrentlyEnabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.sysjobs

WHERE Enabled = 1

ORDER BY Name

SELECT Name CurrentlyDisabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.sysjobs

WHERE Enabled = 0

ORDER BY Name