/*

SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

Backup: http://ola.hallengren.com/sql-server-backup.html

Integrity Check: http://ola.hallengren.com/sql-server-integrity-check.html

Index and Statistics Maintenance: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

The solution is free: http://ola.hallengren.com/license.html

You can contact me by e-mail at ola@hallengren.com.

Last updated 2 September, 2013.

Ola Hallengren

http://ola.hallengren.com

*/

USE DBA -- Specify the database in which the objects will be created.

GO

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)

DECLARE @BackupDirectory nvarchar(max)

DECLARE @OutputFileDirectory nvarchar(max)

DECLARE @LogToTable nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @Error int

SET @CreateJobs = 'N' -- Specify whether jobs should be created.

SET @BackupDirectory = N'\\10.46.80.130\rsm_sql' -- Specify the backup root directory.

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 @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

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('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))

IF OBJECT_ID('[dbo].[DatabaseBackup]') IS NOT NULL DROP PROCEDURE [dbo].[DatabaseBackup]

IF OBJECT_ID('[dbo].[DatabaseIntegrityCheck]') IS NOT NULL DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]

IF OBJECT_ID('[dbo].[IndexOptimize]') IS NOT NULL DROP PROCEDURE [dbo].[IndexOptimize]

IF OBJECT_ID('[dbo].[CommandExecute]') IS NOT NULL DROP PROCEDURE [dbo].[CommandExecute]

IF OBJECT_ID('[dbo].[CommandLog]') IS NULL AND OBJECT_ID('[dbo].[PK_CommandLog]') IS NULL

BEGIN

CREATE TABLE [dbo].[CommandLog](

[ID] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED,

[DatabaseName] sysname NULL,

[SchemaName] sysname NULL,

[ObjectName] sysname NULL,

[ObjectType] char(2) NULL,

[IndexName] sysname NULL,

[IndexType] tinyint NULL,

[StatisticsName] sysname NULL,

[PartitionNumber] int NULL,

[ExtendedInfo] xml NULL,

[Command] nvarchar(max) NOT NULL,

[CommandType] nvarchar(60) NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NULL,

[ErrorNumber] int NULL,

[ErrorMessage] nvarchar(max) NULL

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[CommandExecute]

@Command nvarchar(max),

@CommandType nvarchar(max),

@Mode int,

@Comment nvarchar(max) = NULL,

@DatabaseName nvarchar(max) = NULL,

@SchemaName nvarchar(max) = NULL,

@ObjectName nvarchar(max) = NULL,

@ObjectType nvarchar(max) = NULL,

@IndexName nvarchar(max) = NULL,

@IndexType int = NULL,

@StatisticsName nvarchar(max) = NULL,

@PartitionNumber int = NULL,

@ExtendedInfo xml = NULL,

@LogToTable nvarchar(max),

@Execute nvarchar(max)

AS

BEGIN

----------------------------------------------------------------------------------------------------

--// Source: http://ola.hallengren.com //--

----------------------------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @ErrorMessageOriginal nvarchar(max)

DECLARE @StartTime datetime

DECLARE @EndTime datetime

DECLARE @StartTimeSec datetime

DECLARE @EndTimeSec datetime

DECLARE @ID int

DECLARE @Error int

DECLARE @ReturnCode int

SET @Error = 0

SET @ReturnCode = 0

----------------------------------------------------------------------------------------------------

--// Check core requirements //--

----------------------------------------------------------------------------------------------------

IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

BEGIN

SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO ReturnCode

END

----------------------------------------------------------------------------------------------------

--// Check input parameters //--

----------------------------------------------------------------------------------------------------

IF @Command IS NULL OR @Command = ''

BEGIN

SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60

BEGIN

SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Mode NOT IN(1,2) OR @Mode IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO ReturnCode

END

----------------------------------------------------------------------------------------------------

--// Log initial information //--

----------------------------------------------------------------------------------------------------

SET @StartTime = GETDATE()

SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)

SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Command: ' + @Command

IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment

SET @StartMessage = REPLACE(@StartMessage,'%','%%')

RAISERROR(@StartMessage,10,1) WITH NOWAIT

IF @LogToTable = 'Y'

BEGIN

INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)

VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)

END

SET @ID = SCOPE_IDENTITY()

----------------------------------------------------------------------------------------------------

--// Execute command //--

----------------------------------------------------------------------------------------------------

IF @Mode = 1 AND @Execute = 'Y'

BEGIN

EXECUTE(@Command)

SET @Error = @@ERROR

SET @ReturnCode = @Error

END

IF @Mode = 2 AND @Execute = 'Y'

BEGIN

BEGIN TRY

EXECUTE(@Command)

END TRY

BEGIN CATCH

SET @Error = ERROR_NUMBER()

SET @ReturnCode = @Error

SET @ErrorMessageOriginal = ERROR_MESSAGE()

SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

END CATCH

END

----------------------------------------------------------------------------------------------------

--// Log completing information //--

----------------------------------------------------------------------------------------------------

SET @EndTime = GETDATE()

SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)

SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)

SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)

SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '

SET @EndMessage = REPLACE(@EndMessage,'%','%%')

RAISERROR(@EndMessage,10,1) WITH NOWAIT

IF @LogToTable = 'Y'

BEGIN

UPDATE dbo.CommandLog

SET EndTime = @EndTime,

ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,

ErrorMessage = @ErrorMessageOriginal

WHERE ID = @ID

END

ReturnCode:

IF @ReturnCode <> 0

BEGIN

RETURN @ReturnCode

END

----------------------------------------------------------------------------------------------------

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseBackup]

@Databases nvarchar(max),

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = 'N',

@CleanupTime int = NULL,

@Compress nvarchar(max) = NULL,

@CopyOnly nvarchar(max) = 'N',

@ChangeBackupType nvarchar(max) = 'N',

@BackupSoftware nvarchar(max) = NULL,

@CheckSum nvarchar(max) = 'N',

@BlockSize int = NULL,

@BufferCount int = NULL,

@MaxTransferSize int = NULL,

@NumberOfFiles int = NULL,

@CompressionLevel int = NULL,

@Description nvarchar(max) = NULL,

@Threads int = NULL,

@Throttle int = NULL,

@Encrypt nvarchar(max) = 'N',

@EncryptionType nvarchar(max) = NULL,

@EncryptionKey nvarchar(max) = NULL,

@ReadWriteFileGroups nvarchar(max) = 'N',

@OverrideBackupPreference nvarchar(max) = 'N',

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'

AS

BEGIN

----------------------------------------------------------------------------------------------------

--// Source: http://ola.hallengren.com //--

----------------------------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @DatabaseMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @Cluster nvarchar(max)

DECLARE @DefaultDirectory nvarchar(4000)

DECLARE @CurrentRootDirectoryID int

DECLARE @CurrentRootDirectoryPath nvarchar(4000)

DECLARE @CurrentDBID int

DECLARE @CurrentDatabaseID int

DECLARE @CurrentDatabaseName nvarchar(max)

DECLARE @CurrentBackupType nvarchar(max)

DECLARE @CurrentFileExtension nvarchar(max)

DECLARE @CurrentFileNumber int

DECLARE @CurrentDifferentialBaseLSN numeric(25,0)

DECLARE @CurrentDifferentialBaseIsSnapshot bit

DECLARE @CurrentLogLSN numeric(25,0)

DECLARE @CurrentLatestBackup datetime

DECLARE @CurrentDatabaseNameFS nvarchar(max)

DECLARE @CurrentDirectoryID int

DECLARE @CurrentDirectoryPath nvarchar(max)

DECLARE @CurrentFilePath nvarchar(max)

DECLARE @CurrentDate datetime

DECLARE @CurrentCleanupDate datetime

DECLARE @CurrentIsDatabaseAccessible bit

DECLARE @CurrentAvailabilityGroup nvarchar(max)

DECLARE @CurrentAvailabilityGroupRole nvarchar(max)

DECLARE @CurrentAvailabilityGroupBackupPreference nvarchar(max)

DECLARE @CurrentIsPreferredBackupReplica bit

DECLARE @CurrentDatabaseMirroringRole nvarchar(max)

DECLARE @CurrentLogShippingRole nvarchar(max)

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommand03 nvarchar(max)

DECLARE @CurrentCommand04 nvarchar(max)

DECLARE @CurrentCommandOutput01 int

DECLARE @CurrentCommandOutput02 int

DECLARE @CurrentCommandOutput03 int

DECLARE @CurrentCommandOutput04 int

DECLARE @CurrentCommandType01 nvarchar(max)

DECLARE @CurrentCommandType02 nvarchar(max)

DECLARE @CurrentCommandType03 nvarchar(max)

DECLARE @CurrentCommandType04 nvarchar(max)

DECLARE @Directories TABLE (ID int PRIMARY KEY,

DirectoryPath nvarchar(max),

Completed bit)

DECLARE @DirectoryInfo TABLE (FileExists bit,

FileIsADirectory bit,

ParentDirectoryExists bit)

DECLARE @tmpDatabases TABLE (ID int IDENTITY,

DatabaseName nvarchar(max),

DatabaseNameFS nvarchar(max),

DatabaseType nvarchar(max),

Selected bit,

Completed bit,

PRIMARY KEY(Selected, Completed, ID))

DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),

DatabaseType nvarchar(max),

Selected bit)

DECLARE @CurrentDirectories TABLE (ID int PRIMARY KEY,

DirectoryPath nvarchar(max),

CreateCompleted bit,

CleanupCompleted bit,

CreateOutput int,

CleanupOutput int)

DECLARE @CurrentFiles TABLE (CurrentFilePath nvarchar(max))

DECLARE @Error int

DECLARE @ReturnCode int

SET @Error = 0

SET @ReturnCode = 0

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))

----------------------------------------------------------------------------------------------------

--// Log initial information //--

----------------------------------------------------------------------------------------------------

SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + REPLACE(@Directory,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + REPLACE(@BackupType,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + REPLACE(@Verify,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Compress = ' + ISNULL('''' + REPLACE(@Compress,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CopyOnly = ' + ISNULL('''' + REPLACE(@CopyOnly,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @ChangeBackupType = ' + ISNULL('''' + REPLACE(@ChangeBackupType,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BackupSoftware = ' + ISNULL('''' + REPLACE(@BackupSoftware,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @CheckSum = ' + ISNULL('''' + REPLACE(@CheckSum,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @BlockSize = ' + ISNULL(CAST(@BlockSize AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @BufferCount = ' + ISNULL(CAST(@BufferCount AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @MaxTransferSize = ' + ISNULL(CAST(@MaxTransferSize AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @NumberOfFiles = ' + ISNULL(CAST(@NumberOfFiles AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @CompressionLevel = ' + ISNULL(CAST(@CompressionLevel AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Description = ' + ISNULL('''' + REPLACE(@Description,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Threads = ' + ISNULL(CAST(@Threads AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Throttle = ' + ISNULL(CAST(@Throttle AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Encrypt = ' + ISNULL('''' + REPLACE(@Encrypt,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @EncryptionType = ' + ISNULL('''' + REPLACE(@EncryptionType,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @EncryptionKey = ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @ReadWriteFileGroups = ' + ISNULL('''' + REPLACE(@ReadWriteFileGroups,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @OverrideBackupPreference = ' + ISNULL('''' + REPLACE(@OverrideBackupPreference,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Source: http://ola.hallengren.com' + CHAR(13) + CHAR(10)

SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------

--// Check core requirements //--

----------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')

BEGIN

SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))

BEGIN

SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')

BEGIN

SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ReturnCode = @Error

GOTO Logging

END

----------------------------------------------------------------------------------------------------

--// Select databases //--

----------------------------------------------------------------------------------------------------

SET @Databases = REPLACE(@Databases, ', ', ',');

WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,

SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem

WHERE @Databases IS NOT NULL

UNION ALL

SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,

SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem

FROM Databases1

WHERE EndPosition < LEN(@Databases) + 1

),

Databases2 (DatabaseItem, Selected) AS

(

SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,

CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected

FROM Databases1

),

Databases3 (DatabaseItem, DatabaseType, Selected) AS

(

SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,

CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,

Selected

FROM Databases2

),

Databases4 (DatabaseName, DatabaseType, Selected) AS

(

SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,

DatabaseType,

Selected

FROM Databases3

)

INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)

SELECT DatabaseName,

DatabaseType,

Selected

FROM Databases4

OPTION (MAXRECURSION 0)

INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)

SELECT [name] AS DatabaseName,

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,

CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,

0 AS Selected,

0 AS Completed

FROM sys.databases

WHERE [name] <> 'tempdb'

AND source_database_id IS NULL

ORDER BY [name] ASC

UPDATE tmpDatabases

SET tmpDatabases.Selected = SelectedDatabases.Selected

FROM @tmpDatabases tmpDatabases

INNER JOIN @SelectedDatabases SelectedDatabases

ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')

AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)

WHERE SelectedDatabases.Selected = 1

UPDATE tmpDatabases

SET tmpDatabases.Selected = SelectedDatabases.Selected

FROM @tmpDatabases tmpDatabases

INNER JOIN @SelectedDatabases SelectedDatabases

ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')

AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)

WHERE SelectedDatabases.Selected = 0

IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')

BEGIN

SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Check database names //--

----------------------------------------------------------------------------------------------------

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @tmpDatabases

WHERE Selected = 1

AND DatabaseNameFS = ''

ORDER BY DatabaseName ASC

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

SET @ErrorMessage = ''

SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

FROM @tmpDatabases

WHERE UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases GROUP BY UPPER(DatabaseNameFS) HAVING COUNT(*) > 1)

AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)

AND DatabaseNameFS <> ''

ORDER BY DatabaseName ASC

OPTION (RECOMPILE)

IF @@ROWCOUNT > 0

BEGIN

SET @ErrorMessage = 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Select directories //--

----------------------------------------------------------------------------------------------------

IF @Directory IS NULL

BEGIN

EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultDirectory OUTPUT

INSERT INTO @Directories (ID, DirectoryPath, Completed)

SELECT 1, @DefaultDirectory, 0

END

ELSE

BEGIN

SET @Directory = REPLACE(@Directory, ', ', ',');

WITH Directories (StartPosition, EndPosition, Directory) AS

(

SELECT 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Directory, 1), 0), LEN(@Directory) + 1) AS EndPosition,

SUBSTRING(@Directory, 1, ISNULL(NULLIF(CHARINDEX(',', @Directory, 1), 0), LEN(@Directory) + 1) - 1) AS Directory

WHERE @Directory IS NOT NULL

UNION ALL

SELECT CAST(EndPosition AS int) + 1 AS StartPosition,

ISNULL(NULLIF(CHARINDEX(',', @Directory, EndPosition + 1), 0), LEN(@Directory) + 1) AS EndPosition,

SUBSTRING(@Directory, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Directory, EndPosition + 1), 0), LEN(@Directory) + 1) - EndPosition - 1) AS Directory

FROM Directories

WHERE EndPosition < LEN(@Directory) + 1

)

INSERT INTO @Directories (ID, DirectoryPath, Completed)

SELECT ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,

Directory,

0

FROM Directories

OPTION (MAXRECURSION 0)

END

----------------------------------------------------------------------------------------------------

--// Check directories //--

----------------------------------------------------------------------------------------------------

IF EXISTS(SELECT * FROM @Directories WHERE NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%') OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ') OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1)

BEGIN

SET @ErrorMessage = 'The value for the parameter @Directory is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

ELSE

BEGIN

WHILE EXISTS(SELECT * FROM @Directories WHERE Completed = 0)

BEGIN

SELECT TOP 1 @CurrentRootDirectoryID = ID,

@CurrentRootDirectoryPath = DirectoryPath

FROM @Directories

WHERE Completed = 0

ORDER BY ID ASC

INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)

EXECUTE [master].dbo.xp_fileexist @CurrentRootDirectoryPath

IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)

BEGIN

SET @ErrorMessage = 'The directory ' + @CurrentRootDirectoryPath + ' does not exist.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

UPDATE @Directories

SET Completed = 1

WHERE ID = @CurrentRootDirectoryID

SET @CurrentRootDirectoryID = NULL

SET @CurrentRootDirectoryPath = NULL

DELETE FROM @DirectoryInfo

END

END

----------------------------------------------------------------------------------------------------

--// Get default compression //--

----------------------------------------------------------------------------------------------------

IF @Compress IS NULL

BEGIN

SELECT @Compress = CASE

WHEN @BackupSoftware IS NULL AND EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'Y'

WHEN @BackupSoftware IS NULL AND NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'N'

WHEN @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel > 0) THEN 'Y'

WHEN @BackupSoftware IS NOT NULL AND @CompressionLevel = 0 THEN 'N'

END

END

----------------------------------------------------------------------------------------------------

--// Get number of files //--

----------------------------------------------------------------------------------------------------

IF @NumberOfFiles IS NULL

BEGIN

SELECT @NumberOfFiles = (SELECT COUNT(*) FROM @Directories)

END

----------------------------------------------------------------------------------------------------

--// Check input parameters //--

----------------------------------------------------------------------------------------------------

IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @BackupType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Verify NOT IN ('Y','N') OR @Verify IS NULL OR (@BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND @Verify = 'Y')

BEGIN

SET @ErrorMessage = 'The value for the parameter @Verify is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime < 0

BEGIN

SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Compress NOT IN ('Y','N') OR @Compress IS NULL OR (@Compress = 'Y' AND @BackupSoftware IS NULL AND NOT ((@Version >= 10 AND @Version < 10.5 AND SERVERPROPERTY('EngineEdition') = 3) OR (@Version >= 10.5 AND (SERVERPROPERTY('EngineEdition') = 3 OR SERVERPROPERTY('EditionID') IN (-1534726760, 284895786))))) OR (@Compress = 'N' AND @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel >= 1)) OR (@Compress = 'Y' AND @BackupSoftware IS NOT NULL AND @CompressionLevel = 0)

BEGIN

SET @ErrorMessage = 'The value for the parameter @Compress is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CopyOnly NOT IN ('Y','N') OR @CopyOnly IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @CopyOnly is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @ChangeBackupType NOT IN ('Y','N') OR @ChangeBackupType IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @ChangeBackupType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware NOT IN ('LITESPEED','SQLBACKUP','HYPERBAC','SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for the parameter @BackupSoftware is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'LITESPEED' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_backup_database')

BEGIN

SET @ErrorMessage = 'NetVault LiteSpeed for SQL Server is not installed. Download http://www.quest.com/litespeed-for-sql-server/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'SQLBACKUP' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'sqlbackup')

BEGIN

SET @ErrorMessage = 'Red Gate SQL Backup is not installed. Download http://www.red-gate.com/products/dba/sql-backup/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BackupSoftware = 'SQLSAFE' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_ss_backup')

BEGIN

SET @ErrorMessage = 'Idera SQL safe backup is not installed. Download http://www.idera.com/Products/SQL-Server/SQL-safe-backup/.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CheckSum NOT IN ('Y','N') OR @CheckSum IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @CheckSum is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BlockSize NOT IN (512,1024,2048,4096,8192,16384,32768,65536) OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for the parameter @BlockSize is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @BufferCount <= 0 OR @BufferCount > 2147483647 OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for the parameter @BufferCount is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304 OR @MaxTransferSize % 65536 > 0 OR (@MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP') OR (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for the parameter @MaxTransferSize is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @NumberOfFiles < 1 OR @NumberOfFiles > 64 OR (@NumberOfFiles > 32 AND @BackupSoftware = 'SQLBACKUP') OR @NumberOfFiles IS NULL OR @NumberOfFiles < (SELECT COUNT(*) FROM @Directories) OR @NumberOfFiles % (SELECT COUNT(*) FROM @Directories) > 0

BEGIN

SET @ErrorMessage = 'The value for the parameter @NumberOfFiles is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@BackupSoftware IS NULL AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'HYPERBAC' AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'LITESPEED' AND (@CompressionLevel < 0 OR @CompressionLevel > 8)) OR (@BackupSoftware = 'SQLBACKUP' AND (@CompressionLevel < 0 OR @CompressionLevel > 4)) OR (@BackupSoftware = 'SQLSAFE' AND (@CompressionLevel < 1 OR @CompressionLevel > 4))

BEGIN

SET @ErrorMessage = 'The value for the parameter @CompressionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF LEN(@Description) > 255 OR (@BackupSoftware = 'LITESPEED' AND LEN(@Description) > 128)

BEGIN

SET @ErrorMessage = 'The value for the parameter @Description is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Threads IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED','SQLBACKUP','SQLSAFE') OR @BackupSoftware IS NULL) OR @Threads < 2 OR @Threads > 32

BEGIN

SET @ErrorMessage = 'The value for the parameter @Threads is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Throttle IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL) OR @Throttle < 1 OR @Throttle > 100

BEGIN

SET @ErrorMessage = 'The value for the parameter @Throttle is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Encrypt NOT IN('Y','N') OR @Encrypt IS NULL OR (@Encrypt = 'Y' AND @BackupSoftware IS NULL)

BEGIN

SET @ErrorMessage = 'The value for the parameter @Encrypt is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@EncryptionType IS NOT NULL AND @BackupSoftware IS NULL) OR (@EncryptionType IS NOT NULL AND @BackupSoftware = 'HYPERBAC') OR (@EncryptionType IS NOT NULL AND @Encrypt = 'N') OR ((@EncryptionType NOT IN('RC2-40','RC2-56','RC2-112','RC2-128','3DES-168','RC4-128','AES-128','AES-192','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'LITESPEED') OR ((@EncryptionType NOT IN('AES-128','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'SQLBACKUP') OR ((@EncryptionType NOT IN('AES-128','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'SQLSAFE')

BEGIN

SET @ErrorMessage = 'The value for the parameter @EncryptionType is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@EncryptionKey IS NOT NULL AND @BackupSoftware IS NULL) OR (@EncryptionKey IS NOT NULL AND @BackupSoftware = 'HYPERBAC') OR (@EncryptionKey IS NOT NULL AND @Encrypt = 'N') OR (@EncryptionKey IS NULL AND @Encrypt = 'Y' AND @BackupSoftware IN('LITESPEED','SQLBACKUP','SQLSAFE'))

BEGIN

SET @ErrorMessage = 'The value for the parameter @EncryptionKey is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @ReadWriteFileGroups NOT IN('Y','N') OR @ReadWriteFileGroups IS NULL OR (@ReadWriteFileGroups = 'Y' AND @BackupType = 'LOG')

BEGIN

SET @ErrorMessage = 'The value for the parameter @ReadWriteFileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @OverrideBackupPreference NOT IN('Y','N') OR @OverrideBackupPreference IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @OverrideBackupPreference is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL

BEGIN

SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Error <> 0

BEGIN

SET @ErrorMessage = 'The documentation is available at http://ola.hallengren.com/sql-server-backup.html.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @ReturnCode = @Error

GOTO Logging

END

----------------------------------------------------------------------------------------------------

--// Check Availability Group cluster name //--

----------------------------------------------------------------------------------------------------

IF @Version >= 11

BEGIN

SELECT @Cluster = cluster_name

FROM sys.dm_hadr_cluster

END

----------------------------------------------------------------------------------------------------

--// Execute backup commands //--

----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)

BEGIN

SELECT TOP 1 @CurrentDBID = ID,

@CurrentDatabaseName = DatabaseName,

@CurrentDatabaseNameFS = DatabaseNameFS

FROM @tmpDatabases

WHERE Selected = 1

AND Completed = 0

ORDER BY ID ASC

SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

BEGIN

IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)

BEGIN

SET @CurrentIsDatabaseAccessible = 1

END

ELSE

BEGIN

SET @CurrentIsDatabaseAccessible = 0

END

END

ELSE

BEGIN

SET @CurrentIsDatabaseAccessible = 0

END

SELECT @CurrentDifferentialBaseLSN = differential_base_lsn

FROM sys.master_files

WHERE database_id = @CurrentDatabaseID

AND [type] = 0

AND [file_id] = 1

-- Workaround for a bug in SQL Server 2005

IF @Version >= 9 AND @Version < 10

AND EXISTS(SELECT * FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1 AND differential_base_lsn IS NOT NULL AND differential_base_guid IS NOT NULL AND differential_base_time IS NULL)

BEGIN

SET @CurrentDifferentialBaseLSN = NULL

END

SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot

FROM msdb.dbo.backupset

WHERE database_name = @CurrentDatabaseName

AND [type] = 'D'

AND checkpoint_lsn = @CurrentDifferentialBaseLSN

IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'

BEGIN

SELECT @CurrentLogLSN = last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = @CurrentDatabaseID

END

SET @CurrentBackupType = @BackupType

IF @ChangeBackupType = 'Y'

BEGIN

IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'

BEGIN

SET @CurrentBackupType = 'DIFF'

END

IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL AND @CurrentDatabaseName <> 'master'

BEGIN

SET @CurrentBackupType = 'FULL'

END

END

IF @CurrentBackupType = 'LOG'

BEGIN

SELECT @CurrentLatestBackup = MAX(backup_finish_date)

FROM msdb.dbo.backupset