SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseBackup]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DatabaseBackup] AS'

END

GO

ALTER PROCEDURE [dbo].[DatabaseBackup]

@Databases nvarchar(max) = NULL,

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = 'N',

@CleanupTime int = NULL,

@CleanupMode nvarchar(max) = 'AFTER_BACKUP',

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

@EncryptionAlgorithm nvarchar(max) = NULL,

@ServerCertificate nvarchar(max) = NULL,

@ServerAsymmetricKey nvarchar(max) = NULL,

@EncryptionKey nvarchar(max) = NULL,

@ReadWriteFileGroups nvarchar(max) = 'N',

@OverrideBackupPreference nvarchar(max) = 'N',

@NoRecovery nvarchar(max) = 'N',

@URL nvarchar(max) = NULL,

@Credential nvarchar(max) = NULL,

@MirrorDirectory nvarchar(max) = NULL,

@MirrorCleanupTime int = NULL,

@MirrorCleanupMode nvarchar(max) = 'AFTER_BACKUP',

@MirrorURL nvarchar(max) = NULL,

@AvailabilityGroups nvarchar(max) = NULL,

@Updateability nvarchar(max) = 'ALL',

@AdaptiveCompression nvarchar(max) = NULL,

@ModificationLevel int = NULL,

@LogSizeSinceLastLogBackup int = NULL,

@TimeSinceLastLogBackup int = NULL,

@DataDomainBoostHost nvarchar(max) = NULL,

@DataDomainBoostUser nvarchar(max) = NULL,

@DataDomainBoostDevicePath nvarchar(max) = NULL,

@DataDomainBoostLockboxPath nvarchar(max) = NULL,

@DirectoryStructure nvarchar(max) = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',

@AvailabilityGroupDirectoryStructure nvarchar(max) = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}',

@FileName nvarchar(max) = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',

@AvailabilityGroupFileName nvarchar(max) = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',

@FileExtensionFull nvarchar(max) = NULL,

@FileExtensionDiff nvarchar(max) = NULL,

@FileExtensionLog nvarchar(max) = NULL,

@Init nvarchar(max) = 'N',

@DatabaseOrder nvarchar(max) = NULL,

@DatabasesInParallel nvarchar(max) = 'N',

@LogToTable nvarchar(max) = 'N',

@Execute nvarchar(max) = 'Y'

AS

BEGIN

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

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

--// License: https://ola.hallengren.com/license.html //--

--// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //--

--// Version: 2018-10-28 14:45:02 //--

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

SET NOCOUNT ON

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @DatabaseMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @StartTime datetime

DECLARE @SchemaName nvarchar(max)

DECLARE @ObjectName nvarchar(max)

DECLARE @VersionTimestamp nvarchar(max)

DECLARE @Parameters nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @HostPlatform nvarchar(max)

DECLARE @DirectorySeparator nvarchar(max)

DECLARE @AmazonRDS bit

DECLARE @Updated bit

DECLARE @Cluster nvarchar(max)

DECLARE @DefaultDirectory nvarchar(4000)

DECLARE @QueueID int

DECLARE @QueueStartTime datetime

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 @CurrentDirectoryStructure nvarchar(max)

DECLARE @CurrentDatabaseFileName nvarchar(max)

DECLARE @CurrentMaxFilePathLength nvarchar(max)

DECLARE @CurrentFileName 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 @CurrentIsEncrypted bit

DECLARE @CurrentIsReadOnly bit

DECLARE @CurrentBackupSetID int

DECLARE @CurrentIsMirror bit

DECLARE @CurrentLastLogBackup datetime

DECLARE @CurrentLogSizeSinceLastLogBackup float

DECLARE @CurrentAllocatedExtentPageCount bigint

DECLARE @CurrentModifiedExtentPageCount bigint

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommand03 nvarchar(max)

DECLARE @CurrentCommand04 nvarchar(max)

DECLARE @CurrentCommand05 nvarchar(max)

DECLARE @CurrentCommand06 nvarchar(max)

DECLARE @CurrentCommand07 nvarchar(max)

DECLARE @CurrentCommandOutput01 int

DECLARE @CurrentCommandOutput02 int

DECLARE @CurrentCommandOutput03 int

DECLARE @CurrentCommandOutput04 int

DECLARE @CurrentCommandOutput05 int

DECLARE @CurrentCommandType01 nvarchar(max)

DECLARE @CurrentCommandType02 nvarchar(max)

DECLARE @CurrentCommandType03 nvarchar(max)

DECLARE @CurrentCommandType04 nvarchar(max)

DECLARE @CurrentCommandType05 nvarchar(max)

DECLARE @Directories TABLE (ID int PRIMARY KEY,

DirectoryPath nvarchar(max),

Mirror bit,

Completed bit)

DECLARE @URLs TABLE (ID int IDENTITY PRIMARY KEY,

DirectoryPath nvarchar(max),

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

AvailabilityGroup bit,

StartPosition int,

DatabaseSize bigint,

LogSizeSinceLastLogBackup float,

[Order] int,

Selected bit,

Completed bit,

PRIMARY KEY(Selected, Completed, [Order], ID))

DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY,

AvailabilityGroupName nvarchar(max),

StartPosition int,

Selected bit)

DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max),

AvailabilityGroupName nvarchar(max))

DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),

DatabaseType nvarchar(max),

AvailabilityGroup nvarchar(max),

StartPosition int,

Selected bit)

DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max),

StartPosition int,

Selected bit)

DECLARE @CurrentBackupSet TABLE (ID int IDENTITY PRIMARY KEY,

Mirror bit,

VerifyCompleted bit,

VerifyOutput int)

DECLARE @CurrentDirectories TABLE (ID int PRIMARY KEY,

DirectoryPath nvarchar(max),

Mirror bit,

DirectoryNumber int,

CleanupDate datetime,

CleanupMode nvarchar(max),

CreateCompleted bit,

CleanupCompleted bit,

CreateOutput int,

CleanupOutput int)

DECLARE @CurrentURLs TABLE (ID int PRIMARY KEY,

DirectoryPath nvarchar(max),

Mirror bit)

DECLARE @CurrentFiles TABLE ([Type] nvarchar(max),

FilePath nvarchar(max),

Mirror bit)

DECLARE @CurrentCleanupDates TABLE (CleanupDate datetime, Mirror bit)

DECLARE @DirectoryCheck bit

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

IF @Version >= 14

BEGIN

SELECT @HostPlatform = host_platform

FROM sys.dm_os_host_info

END

ELSE

BEGIN

SET @HostPlatform = 'Windows'

END

SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

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

--// Log initial information //--

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

SET @StartTime = GETDATE()

SET @SchemaName = (SELECT schemas.name FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID)

SET @ObjectName = OBJECT_NAME(@@PROCID)

SET @VersionTimestamp = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX('--// Version: ',OBJECT_DEFINITION(@@PROCID)) + LEN('--// Version: ') + 1, 19)

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

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

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

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

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

SET @Parameters = @Parameters + ', @CleanupMode = ' + ISNULL('''' + REPLACE(@CleanupMode,'''','''''') + '''','NULL')

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET @Parameters = @Parameters + ', @EncryptionAlgorithm = ' + ISNULL('''' + REPLACE(@EncryptionAlgorithm,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @ServerCertificate = ' + ISNULL('''' + REPLACE(@ServerCertificate,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @ServerAsymmetricKey = ' + ISNULL('''' + REPLACE(@ServerAsymmetricKey,'''','''''') + '''','NULL')

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

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

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

SET @Parameters = @Parameters + ', @NoRecovery = ' + ISNULL('''' + REPLACE(@NoRecovery,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @URL = ' + ISNULL('''' + REPLACE(@URL,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @Credential = ' + ISNULL('''' + REPLACE(@Credential,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @MirrorDirectory = ' + ISNULL('''' + REPLACE(@MirrorDirectory,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @MirrorCleanupTime = ' + ISNULL(CAST(@MirrorCleanupTime AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @MirrorCleanupMode = ' + ISNULL('''' + REPLACE(@MirrorCleanupMode,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @MirrorURL = ' + ISNULL('''' + REPLACE(@MirrorURL,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @AdaptiveCompression = ' + ISNULL('''' + REPLACE(@AdaptiveCompression,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @ModificationLevel = ' + ISNULL(CAST(@ModificationLevel AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @LogSizeSinceLastLogBackup = ' + ISNULL(CAST(@LogSizeSinceLastLogBackup AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @TimeSinceLastLogBackup = ' + ISNULL(CAST(@TimeSinceLastLogBackup AS nvarchar),'NULL')

SET @Parameters = @Parameters + ', @DataDomainBoostHost = ' + ISNULL('''' + REPLACE(@DataDomainBoostHost,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DataDomainBoostUser = ' + ISNULL('''' + REPLACE(@DataDomainBoostUser,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DataDomainBoostDevicePath = ' + ISNULL('''' + REPLACE(@DataDomainBoostDevicePath,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DataDomainBoostLockboxPath = ' + ISNULL('''' + REPLACE(@DataDomainBoostLockboxPath,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DirectoryStructure = ' + ISNULL('''' + REPLACE(@DirectoryStructure,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @AvailabilityGroupDirectoryStructure = ' + ISNULL('''' + REPLACE(@AvailabilityGroupDirectoryStructure,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @FileName = ' + ISNULL('''' + REPLACE(@FileName,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @AvailabilityGroupFileName = ' + ISNULL('''' + REPLACE(@AvailabilityGroupFileName,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @FileExtensionFull = ' + ISNULL('''' + REPLACE(@FileExtensionFull,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @FileExtensionDiff = ' + ISNULL('''' + REPLACE(@FileExtensionDiff,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @FileExtensionLog = ' + ISNULL('''' + REPLACE(@FileExtensionLog,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @Init = ' + ISNULL('''' + REPLACE(@Init,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL')

SET @Parameters = @Parameters + ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','NULL')

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

SET @Parameters = @Parameters + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL')

SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120)

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max))

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Platform: ' + @HostPlatform

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Parameters: ' + @Parameters

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

RAISERROR(@StartMessage,10,1) WITH NOWAIT

SET @StartMessage = 'Version: ' + @VersionTimestamp

RAISERROR(@StartMessage,10,1) WITH NOWAIT

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

RAISERROR(@StartMessage,10,1) WITH NOWAIT

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

--// Check core requirements //--

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

IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90

BEGIN

SET @ErrorMessage = 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1

BEGIN

SET @ErrorMessage = 'ANSI_NULLS has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1

BEGIN

SET @ErrorMessage = 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

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 https://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 '%@LockMessageSeverity%')

BEGIN

SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://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 https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @DatabasesInParallel = '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] = 'Queue')

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @DatabasesInParallel = '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] = 'QueueDatabase')

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @@TRANCOUNT <> 0

BEGIN

SET @ErrorMessage = 'The transaction count is not 0.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @AmazonRDS = 1

BEGIN

SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.' + 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, CHAR(10), '')

SET @Databases = REPLACE(@Databases, CHAR(13), '')

WHILE CHARINDEX(', ',@Databases) > 0 SET @Databases = REPLACE(@Databases,', ',',')

WHILE CHARINDEX(' ,',@Databases) > 0 SET @Databases = REPLACE(@Databases,' ,',',')

SET @Databases = LTRIM(RTRIM(@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, StartPosition, Selected) AS

(

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

StartPosition,

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

FROM Databases1

),

Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS

(

SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES','AVAILABILITY_GROUP_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,

CASE WHEN DatabaseItem = 'AVAILABILITY_GROUP_DATABASES' THEN 1 ELSE NULL END AvailabilityGroup,

StartPosition,

Selected

FROM Databases2

),

Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS

(

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

DatabaseType,

AvailabilityGroup,

StartPosition,

Selected

FROM Databases3

)

INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected)

SELECT DatabaseName,

DatabaseType,

AvailabilityGroup,

StartPosition,

Selected

FROM Databases4

OPTION (MAXRECURSION 0)

IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1

BEGIN

INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)

SELECT name AS AvailabilityGroupName,

0 AS Selected

FROM sys.availability_groups

INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName)

SELECT availability_databases_cluster.database_name, availability_groups.name

FROM sys.availability_databases_cluster availability_databases_cluster

INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id

END

INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, AvailabilityGroup, [Order], Selected, Completed)

SELECT [name] AS DatabaseName,

LTRIM(RTRIM(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,

NULL AS AvailabilityGroup,

0 AS [Order],

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 AvailabilityGroup = CASE WHEN EXISTS (SELECT * FROM @tmpDatabasesAvailabilityGroups WHERE DatabaseName = tmpDatabases.DatabaseName) THEN 1 ELSE 0 END

FROM @tmpDatabases tmpDatabases

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)

AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup 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)

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

WHERE SelectedDatabases.Selected = 0

UPDATE tmpDatabases

SET tmpDatabases.StartPosition = SelectedDatabases2.StartPosition

FROM @tmpDatabases tmpDatabases

INNER JOIN (SELECT tmpDatabases.DatabaseName, MIN(SelectedDatabases.StartPosition) AS StartPosition

FROM @tmpDatabases tmpDatabases

INNER JOIN @SelectedDatabases SelectedDatabases

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

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

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

WHERE SelectedDatabases.Selected = 1

GROUP BY tmpDatabases.DatabaseName) SelectedDatabases2

ON tmpDatabases.DatabaseName = SelectedDatabases2.DatabaseName

IF @Databases IS NOT NULL AND (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

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

--// Select availability groups //--

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

IF @AvailabilityGroups IS NOT NULL AND @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1

BEGIN

SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), '')

SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), '')

WHILE CHARINDEX(', ',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,', ',',')

WHILE CHARINDEX(' ,',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,' ,',',')

SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));

WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS

(

SELECT 1 AS StartPosition,

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

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

WHERE @AvailabilityGroups IS NOT NULL

UNION ALL

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

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

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

FROM AvailabilityGroups1

WHERE EndPosition < LEN(@AvailabilityGroups) + 1

),

AvailabilityGroups2 (AvailabilityGroupItem, StartPosition, Selected) AS

(

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

StartPosition,

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

FROM AvailabilityGroups1

),

AvailabilityGroups3 (AvailabilityGroupItem, StartPosition, Selected) AS

(

SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,

StartPosition,

Selected

FROM AvailabilityGroups2

),

AvailabilityGroups4 (AvailabilityGroupName, StartPosition, Selected) AS

(

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

StartPosition,

Selected

FROM AvailabilityGroups3

)

INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, StartPosition, Selected)

SELECT AvailabilityGroupName, StartPosition, Selected

FROM AvailabilityGroups4

OPTION (MAXRECURSION 0)

UPDATE tmpAvailabilityGroups

SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected

FROM @tmpAvailabilityGroups tmpAvailabilityGroups

INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups

ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')

WHERE SelectedAvailabilityGroups.Selected = 1

UPDATE tmpAvailabilityGroups

SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected

FROM @tmpAvailabilityGroups tmpAvailabilityGroups

INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups

ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')

WHERE SelectedAvailabilityGroups.Selected = 0

UPDATE tmpAvailabilityGroups

SET tmpAvailabilityGroups.StartPosition = SelectedAvailabilityGroups2.StartPosition

FROM @tmpAvailabilityGroups tmpAvailabilityGroups

INNER JOIN (SELECT tmpAvailabilityGroups.AvailabilityGroupName, MIN(SelectedAvailabilityGroups.StartPosition) AS StartPosition

FROM @tmpAvailabilityGroups tmpAvailabilityGroups

INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups

ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')

WHERE SelectedAvailabilityGroups.Selected = 1

GROUP BY tmpAvailabilityGroups.AvailabilityGroupName) SelectedAvailabilityGroups2

ON tmpAvailabilityGroups.AvailabilityGroupName = SelectedAvailabilityGroups2.AvailabilityGroupName

UPDATE tmpDatabases

SET tmpDatabases.StartPosition = tmpAvailabilityGroups.StartPosition,

tmpDatabases.Selected = 1

FROM @tmpDatabases tmpDatabases

INNER JOIN @tmpDatabasesAvailabilityGroups tmpDatabasesAvailabilityGroups ON tmpDatabases.DatabaseName = tmpDatabasesAvailabilityGroups.DatabaseName

INNER JOIN @tmpAvailabilityGroups tmpAvailabilityGroups ON tmpDatabasesAvailabilityGroups.AvailabilityGroupName = tmpAvailabilityGroups.AvailabilityGroupName

WHERE tmpAvailabilityGroups.Selected = 1

END

IF @AvailabilityGroups IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedAvailabilityGroups) OR EXISTS(SELECT * FROM @SelectedAvailabilityGroups WHERE AvailabilityGroupName IS NULL OR AvailabilityGroupName = '') OR @Version < 11 OR SERVERPROPERTY('IsHadrEnabled') = 0)

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@Databases IS NULL AND @AvailabilityGroups IS NULL)

BEGIN

SET @ErrorMessage = 'You need to specify one of the parameters @Databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)

BEGIN

SET @ErrorMessage = 'You can only specify one of the parameters @Databases and @AvailabilityGroups.' + 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 AND @URL IS NULL AND @HostPlatform = 'Windows' AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)

BEGIN

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

IF @DefaultDirectory LIKE 'http://%' OR @DefaultDirectory LIKE 'https://%'

BEGIN

SET @URL = @DefaultDirectory

END

ELSE

BEGIN

INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)

SELECT 1, @DefaultDirectory, 0, 0

END

END

IF @Directory IS NULL AND @URL IS NULL AND @HostPlatform = 'Linux'

BEGIN

INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)

SELECT 1, '.', 0, 0

END

ELSE

BEGIN

SET @Directory = REPLACE(@Directory, CHAR(10), '')

SET @Directory = REPLACE(@Directory, CHAR(13), '')

WHILE CHARINDEX(', ',@Directory) > 0 SET @Directory = REPLACE(@Directory,', ',',')

WHILE CHARINDEX(' ,',@Directory) > 0 SET @Directory = REPLACE(@Directory,' ,',',')

SET @Directory = LTRIM(RTRIM(@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, Mirror, Completed)

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

Directory,

0,

0

FROM Directories

OPTION (MAXRECURSION 0)

END

SET @MirrorDirectory = REPLACE(@MirrorDirectory, CHAR(10), '')

SET @MirrorDirectory = REPLACE(@MirrorDirectory, CHAR(13), '')

WHILE CHARINDEX(', ',@MirrorDirectory) > 0 SET @MirrorDirectory = REPLACE(@MirrorDirectory,', ',',')

WHILE CHARINDEX(' ,',@MirrorDirectory) > 0 SET @MirrorDirectory = REPLACE(@MirrorDirectory,' ,',',')

SET @MirrorDirectory = LTRIM(RTRIM(@MirrorDirectory));

WITH Directories (StartPosition, EndPosition, Directory) AS

(

SELECT 1 AS StartPosition,

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

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

WHERE @MirrorDirectory IS NOT NULL

UNION ALL

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

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

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

FROM Directories

WHERE EndPosition < LEN(@MirrorDirectory) + 1

)

INSERT INTO @Directories (ID, DirectoryPath, Mirror, Completed)

SELECT (SELECT COUNT(*) FROM @Directories) + ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,

Directory,

1,

0

FROM Directories

OPTION (MAXRECURSION 0)

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

--// Check directories //--

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

SET @DirectoryCheck = 1

IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 0 AND (NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%' OR (DirectoryPath LIKE '/%/%' AND @HostPlatform = 'Linux') OR (DirectoryPath LIKE '.' AND @HostPlatform = 'Linux')) OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ')) OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1) OR ((SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0) OR (@Directory IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 8) OR (@Directory IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST')

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

SET @DirectoryCheck = 0

END

IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 1 AND (NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%' OR (DirectoryPath LIKE '/%/%' AND @HostPlatform = 'Linux') OR (DirectoryPath LIKE '.' AND @HostPlatform = 'Linux')) OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ')) OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1) OR ((SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0) OR (@BackupSoftware IN('SQLBACKUP','SQLSAFE') AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 1) OR (@BackupSoftware IS NULL AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1) AND SERVERPROPERTY('EngineEdition') <> 3) OR (@MirrorDirectory IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 8) OR (@MirrorDirectory IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST')

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

SET @DirectoryCheck = 0

END

IF @DirectoryCheck = 1

BEGIN

WHILE (1 = 1)

BEGIN

SELECT TOP 1 @CurrentRootDirectoryID = ID,

@CurrentRootDirectoryPath = DirectoryPath

FROM @Directories

WHERE Completed = 0

ORDER BY ID ASC

IF @@ROWCOUNT = 0

BEGIN

BREAK

END

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

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

--// Select URLs //--

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

INSERT INTO @URLs (DirectoryPath, Mirror)

SELECT @URL, 0

WHERE @URL IS NOT NULL

INSERT INTO @URLs (DirectoryPath, Mirror)

SELECT @MirrorURL, 1

WHERE @MirrorURL IS NOT NULL

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

--// Get directory separator //--

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

SELECT @DirectorySeparator = CASE

WHEN @URL IS NOT NULL THEN '/'

WHEN @HostPlatform = 'Windows' THEN '\'

WHEN @HostPlatform = 'Linux' THEN '/'

END

UPDATE @Directories

SET DirectoryPath = LEFT(DirectoryPath,LEN(DirectoryPath) - 1)

WHERE RIGHT(DirectoryPath,1) = @DirectorySeparator

UPDATE @URLs

SET DirectoryPath = LEFT(DirectoryPath,LEN(DirectoryPath) - 1)

WHERE RIGHT(DirectoryPath,1) = @DirectorySeparator

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

--// Get file extension //--

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

IF @FileExtensionFull IS NULL

BEGIN

SELECT @FileExtensionFull = CASE

WHEN @BackupSoftware IS NULL THEN 'bak'

WHEN @BackupSoftware = 'LITESPEED' THEN 'bak'

WHEN @BackupSoftware = 'SQLBACKUP' THEN 'sqb'

WHEN @BackupSoftware = 'SQLSAFE' THEN 'safe'

END

END

IF @FileExtensionDiff IS NULL

BEGIN

SELECT @FileExtensionDiff = CASE

WHEN @BackupSoftware IS NULL THEN 'bak'

WHEN @BackupSoftware = 'LITESPEED' THEN 'bak'

WHEN @BackupSoftware = 'SQLBACKUP' THEN 'sqb'

WHEN @BackupSoftware = 'SQLSAFE' THEN 'safe'

END

END

IF @FileExtensionLog IS NULL

BEGIN

SELECT @FileExtensionLog = CASE

WHEN @BackupSoftware IS NULL THEN 'trn'

WHEN @BackupSoftware = 'LITESPEED' THEN 'trn'

WHEN @BackupSoftware = 'SQLBACKUP' THEN 'sqb'

WHEN @BackupSoftware = 'SQLSAFE' THEN 'safe'

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 = CASE WHEN @URL IS NOT NULL THEN 1 WHEN @BackupSoftware = 'DATA_DOMAIN_BOOST' THEN 1 ELSE (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) END

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 SERVERPROPERTY('EngineEdition') = 8 AND NOT (@BackupType = 'FULL' AND @CopyOnly = 'Y')

BEGIN

SET @ErrorMessage = 'SQL Database Managed Instance only supports COPY_ONLY full backups.' + 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') OR (@Verify = 'Y' AND @BackupSoftware = 'DATA_DOMAIN_BOOST')

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 @CleanupTime IS NOT NULL AND @URL IS NOT NULL

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime IS NOT NULL AND @HostPlatform = 'Linux'

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime IS NOT NULL AND ((@DirectoryStructure NOT LIKE '%{DatabaseName}%' OR @DirectoryStructure IS NULL) OR (SERVERPROPERTY('IsHadrEnabled') = 1 AND (@AvailabilityGroupDirectoryStructure NOT LIKE '%{DatabaseName}%' OR @AvailabilityGroupDirectoryStructure IS NULL)))

BEGIN

SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {DatabaseName} is not part of the directory.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime IS NOT NULL AND ((@DirectoryStructure NOT LIKE '%{BackupType}%' OR @DirectoryStructure IS NULL) OR (SERVERPROPERTY('IsHadrEnabled') = 1 AND (@AvailabilityGroupDirectoryStructure NOT LIKE '%{BackupType}%' OR @AvailabilityGroupDirectoryStructure IS NULL)))

BEGIN

SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {BackupType} is not part of the directory.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupTime IS NOT NULL AND @CopyOnly = 'Y' AND ((@DirectoryStructure NOT LIKE '%{CopyOnly}%' OR @DirectoryStructure IS NULL) OR (SERVERPROPERTY('IsHadrEnabled') = 1 AND (@AvailabilityGroupDirectoryStructure NOT LIKE '%{CopyOnly}%' OR @AvailabilityGroupDirectoryStructure IS NULL)))

BEGIN

SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {CopyOnly} is not part of the directory.' + CHAR(13) + CHAR(10) + ' '

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @CleanupMode NOT IN('BEFORE_BACKUP','AFTER_BACKUP') OR @CleanupMode IS NULL

BEGIN

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

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END