As a big fan of the Ola Hallengren Scripts, i normally end up using them in every shop that I’ve worked in. In my current position they have been using a house made solution, but its a little dated now and we are slowly moving things over to mighty Ola scripts. One thing i really like from the current script though, is that it has a central table that all the settings for backups (location, compression), index jobs etc are based from. I’ve found that rather handy a couple of times, so thought i would build one for Ola’s scripts, I’ve only used it at present for backups but as you can see its very easy to add any other settings you like.

First Create the Table. We have a DBA database on all instances so its easy to put it there.
 

CREATE TABLE [dbo].[olaConfig]
    (
      [Option] [varchar](255) NULL ,
      [Value] [varchar](255) NULL
    )

 
Insert the Settings you want to set (Here’s the options for Backups – Link)
 

INSERT  INTO dbo.olaConfig
        ( [Option], Value )
VALUES  ( 'BackupDirectory', -- Option - varchar(200)
          'E:\SQL_Server\Backup'  -- Value - varchar(max)
          ),
        ( 'BackupCleanupTime', -- Option - varchar(200)
          '24'  -- Value - varchar(max)
          ),
        ( 'BackupVerify', -- Option - varchar(200)
          'Y'  -- Value - varchar(max)
          ),
        ( 'BackupCompress', -- Option - varchar(200)
          'Y'  -- Value - varchar(max)
          ),
        ( 'BackupCheckSum', -- Option - varchar(200)
          'Y'  -- Value - varchar(max)
          ),
        ( 'BackupLogToTable', -- Option - varchar(200)
          'Y'  -- Value - varchar(max)
          )

 
Now instead of manually adding all parameters to the scripts in the job we can do something like
 

DECLARE @dir VARCHAR(200) = ( SELECT    value
                              FROM      dba.dbo.olaConfig
                              WHERE     [Option] = 'BackupDirectory'
                            );
DECLARE @clean INT = ( SELECT   value
                       FROM     dba.dbo.olaConfig
                       WHERE    [Option] = 'BackupCleanupTime'
                     );
DECLARE @verify VARCHAR(1) = ( SELECT   value
                               FROM     dba.dbo.olaConfig
                               WHERE    [Option] = 'BackupVerify'
                             );
DECLARE @compress VARCHAR(1) = ( SELECT value
                                 FROM   dba.dbo.olaConfig
                                 WHERE  [Option] = 'BackupCompress'
                               );
DECLARE @checksum VARCHAR(1) = ( SELECT value
                                 FROM   dba.dbo.olaConfig
                                 WHERE  [Option] = 'BackupCheckSum'
                               );
DECLARE @log VARCHAR(1) = ( SELECT  value
                            FROM    dba.dbo.olaConfig
                            WHERE   [Option] = 'BackupLogToTable'
                          );

EXECUTE [Master].[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES',
    @Directory = @dir, @BackupType = 'FULL', @Verify = @verify,
    @CleanupTime = @clean, @CheckSum = @checksum, @LogToTable = @log

 
Very simple, but rather handy i think.
 
 
 

Advertisements