About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, June 18, 2012

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








No comments:

Post a Comment