Please allow it to be easy to identify what version of the maintenance solution is installed. For example, include the last updated date of the maintenance solution as a comment in the code of each procedure.
--// Source: https://ola.hallengren.com
can become
--// Source: https://ola.hallengren.com
--// Version: 2016-04-02
Code can then be used to the check the version is installed. The code can also add or update a database extended property with the version. If the incorrect version is installed, the value NULL can be stored in the extended property.
`USE [master] -- Specify the database in which the objects will be created.
SET NOCOUNT ON
DECLARE @CreateJobs nvarchar(max)
DECLARE @BackupDirectory nvarchar(max)
DECLARE @Cleanuptime int
DECLARE @OutputFileDirectory nvarchar(max)
DECLARE @LogToTable nvarchar(max)
DECLARE @Version numeric(18,10)
DECLARE @error int
DECLARE @LastUpdatedExtPropName sysname
DECLARE @LastUpdatedExtPropValue datetime
SET @CreateJobs = 'Y' -- Specify whether jobs should be created.
SET @BackupDirectory = N'C:\Backup' -- Specify the backup root directory.
SET @Cleanuptime = NULL -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
SET @OutputFileDirectory = NULL -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable = 'Y' -- Log commands to a table.
SET @error = 0
SET @LastUpdatedExtPropName = N'https://ola.hallengren.com last updated' -- set to NULL to disable setting the extended property
SET @LastUpdatedExtPropValue = '2018-04-02 00:00:00' -- set to NULL to disable stored procedure version check
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
RAISERROR('You need to be a member of the SysAdmin server role to install the solution.',16,1)
SET @error = @@error
END
IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config
CREATE TABLE #Config ([Name] nvarchar(max),
[Value] nvarchar(max))
IF @CreateJobs = 'Y' AND @OutputFileDirectory IS NULL AND SERVERPROPERTY('EngineEdition') <> 4 AND @Version < 12
BEGIN
IF @Version >= 11
BEGIN
SELECT @OutputFileDirectory = [path]
FROM sys.dm_os_server_diagnostics_log_configurations
END
ELSE
BEGIN
SELECT @OutputFileDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))
END
END
IF @CreateJobs = 'Y' AND RIGHT(@OutputFileDirectory,1) = '' AND SERVERPROPERTY('EngineEdition') <> 4
BEGIN
SET @OutputFileDirectory = LEFT(@OutputFileDirectory, LEN(@OutputFileDirectory) - 1)
END
INSERT INTO #Config ([Name], [Value])
VALUES('CreateJobs', @CreateJobs)
INSERT INTO #Config ([Name], [Value])
VALUES('BackupDirectory', @BackupDirectory)
INSERT INTO #Config ([Name], [Value])
VALUES('CleanupTime', @Cleanuptime)
INSERT INTO #Config ([Name], [Value])
VALUES('OutputFileDirectory', @OutputFileDirectory)
INSERT INTO #Config ([Name], [Value])
VALUES('LogToTable', @LogToTable)
INSERT INTO #Config ([Name], [Value])
VALUES('DatabaseName', DB_NAME(DB_ID()))
INSERT INTO #Config ([Name], [Value])
VALUES('Error', CAST(@error AS nvarchar))
INSERT INTO #Config ([Name], [Value])
VALUES('LastUpdatedExtPropName', @LastUpdatedExtPropName)
INSERT INTO #Config ([Name], [Value])
VALUES('LastUpdatedExtPropValue', CONVERT(varchar(23), @LastUpdatedExtPropValue, 120))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT 1 FROM #Config WHERE Name = 'LastUpdatedExtPropValue' AND ISDATE(value) = 1)
BEGIN
DECLARE @currentversion datetime
DECLARE @error int
DECLARE @LastUpdatedExtPropName sysname
DECLARE @LastUpdatedExtPropValue datetime
DECLARE @rowcount int
DECLARE @text nvarchar(max)
DECLARE @tempver datetime
DECLARE @VersionExtPropValue sql_variant
SELECT @LastUpdatedExtPropName = CAST(Value as sysname)
FROM #Config
WHERE [Name] = 'LastUpdatedExtPropName'
SELECT @LastUpdatedExtPropValue = CAST(Value as datetime)
FROM #Config
WHERE [Name] = 'LastUpdatedExtPropValue'
SET @currentversion = @LastUpdatedExtPropValue
IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[CommandExecute]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END
IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseBackup]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END
IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseIntegrityCheck]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END
IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[IndexOptimize]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END
IF @currentversion IS NULL
BEGIN
RAISERROR('Warning: The maintenance stored procedures installed are not current.',16,1)
SET @error = @@error
END
IF LEN(@LastUpdatedExtPropName) > 0
BEGIN
SELECT @VersionExtPropValue = [value]
FROM sys.extended_properties
WHERE class = 0 AND major_id = 0 AND minor_id = 0 AND [name] = @LastUpdatedExtPropName;
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @rowcount = 0
EXEC sys.sp_addextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;
ELSE IF @VersionExtPropValue <> @CurrentVersion OR (@VersionExtPropValue IS NULL AND NOT @CurrentVersion IS NULL) OR (NOT @VersionExtPropValue IS NULL AND @CurrentVersion IS NULL)
EXEC sys.sp_updateextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;
END
END`