Config Table for Ola Hallengren Maintenance Script
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.
Hi ,
Than you so much for this. Is there any way to create a second table for Index maintenance ? I want to create one more job for rebuilding indexes and wanted to have out of that job logged into a different table. Please let me know thanks.
Hi Rishi, I haven’t looked into moving the output to another table (instead of the commandlog) sorry.