Source: https://ola.hallengren.com
Backup: SYSTEM FULL
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'SYSTEM_DATABASES',
@Directory ='H:\SqlBackup',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime =96,
@CheckSum = 'Y',
@LogToTable = 'Y'Backup: USER FULL
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'H:\SqlBackup',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 192,
@CheckSum = 'Y',
@LogToTable = 'Y'Backup: USER DIFF
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'G:\Sql_backup',
@BackupType = 'DIFF',
@Verify = 'Y',
@CleanupTime =192,
@CheckSum = 'Y',
@LogToTable = 'Y'Backup: USER LOG
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'H:\SqlBackup',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 96,
@CheckSum = 'Y',
@LogToTable = 'Y'[dbo].[DatabaseBackup] Script
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DatabaseBackup] Script Date: 8/27/2020 7:49:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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,
@MinBackupSizeForMultipleFiles int = NULL,
@MaxFileSize 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',
@Format nvarchar(max) = 'N',
@ObjectLevelRecoveryMap nvarchar(max) = 'N',
@StringDelimiter nvarchar(max) = ',',
@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: 2020-01-26 14:06:53 //--
----------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @StartTime datetime2 = SYSDATETIME()
DECLARE @SchemaName nvarchar(max) = OBJECT_SCHEMA_NAME(@@PROCID)
DECLARE @ObjectName nvarchar(max) = OBJECT_NAME(@@PROCID)
DECLARE @VersionTimestamp nvarchar(max) = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX('--// Version: ',OBJECT_DEFINITION(@@PROCID)) + LEN('--// Version: ') + 1, 19)
DECLARE @Parameters nvarchar(max)
DECLARE @HostPlatform nvarchar(max)
DECLARE @DirectorySeparator nvarchar(max)
DECLARE @Updated bit
DECLARE @Cluster nvarchar(max)
DECLARE @DefaultDirectory nvarchar(4000)
DECLARE @QueueID int
DECLARE @QueueStartTime datetime2
DECLARE @CurrentRootDirectoryID int
DECLARE @CurrentRootDirectoryPath nvarchar(4000)
DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentDatabase_sp_executesql nvarchar(max)
DECLARE @CurrentUserAccess nvarchar(max)
DECLARE @CurrentIsReadOnly bit
DECLARE @CurrentDatabaseState nvarchar(max)
DECLARE @CurrentInStandby bit
DECLARE @CurrentRecoveryModel nvarchar(max)
DECLARE @CurrentDatabaseSize bigint
DECLARE @CurrentIsEncrypted bit
DECLARE @CurrentBackupType nvarchar(max)
DECLARE @CurrentMaxTransferSize int
DECLARE @CurrentNumberOfFiles int
DECLARE @CurrentFileExtension nvarchar(max)
DECLARE @CurrentFileNumber int
DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
DECLARE @CurrentDifferentialBaseIsSnapshot bit
DECLARE @CurrentLogLSN numeric(25,0)
DECLARE @CurrentLatestBackup datetime2
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 datetime2
DECLARE @CurrentCleanupDate datetime2
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 @CurrentBackupSetID int
DECLARE @CurrentIsMirror bit
DECLARE @CurrentLastLogBackup datetime2
DECLARE @CurrentLogSizeSinceLastLogBackup float
DECLARE @CurrentAllocatedExtentPageCount bigint
DECLARE @CurrentModifiedExtentPageCount bigint
DECLARE @CurrentDatabaseContext nvarchar(max)
DECLARE @CurrentCommand nvarchar(max)
DECLARE @CurrentCommandOutput int
DECLARE @CurrentCommandType nvarchar(max)
DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY,
[Message] nvarchar(max) NOT NULL,
Severity int NOT NULL,
[State] int)
DECLARE @CurrentMessage nvarchar(max)
DECLARE @CurrentSeverity int
DECLARE @CurrentState int
DECLARE @Directories TABLE (ID int PRIMARY KEY,
DirectoryPath nvarchar(max),
Mirror bit,
Completed bit)
DECLARE @URLs TABLE (ID int 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 @CurrentBackupOutput 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 datetime2,
CleanupMode nvarchar(max),
CreateCompleted bit,
CleanupCompleted bit,
CreateOutput int,
CleanupOutput int)
DECLARE @CurrentURLs TABLE (ID int PRIMARY KEY,
DirectoryPath nvarchar(max),
Mirror bit,
DirectoryNumber int)
DECLARE @CurrentFiles TABLE ([Type] nvarchar(max),
FilePath nvarchar(max),
Mirror bit)
DECLARE @CurrentCleanupDates TABLE (CleanupDate datetime2,
Mirror bit)
DECLARE @Error int = 0
DECLARE @ReturnCode int = 0
DECLARE @EmptyLine nvarchar(max) = CHAR(9)
DECLARE @Version numeric(18,10) = 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
DECLARE @AmazonRDS bit = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @Parameters = '@Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
SET @Parameters += ', @Directory = ' + ISNULL('''' + REPLACE(@Directory,'''','''''') + '''','NULL')
SET @Parameters += ', @BackupType = ' + ISNULL('''' + REPLACE(@BackupType,'''','''''') + '''','NULL')
SET @Parameters += ', @Verify = ' + ISNULL('''' + REPLACE(@Verify,'''','''''') + '''','NULL')
SET @Parameters += ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL')
SET @Parameters += ', @CleanupMode = ' + ISNULL('''' + REPLACE(@CleanupMode,'''','''''') + '''','NULL')
SET @Parameters += ', @Compress = ' + ISNULL('''' + REPLACE(@Compress,'''','''''') + '''','NULL')
SET @Parameters += ', @CopyOnly = ' + ISNULL('''' + REPLACE(@CopyOnly,'''','''''') + '''','NULL')
SET @Parameters += ', @ChangeBackupType = ' + ISNULL('''' + REPLACE(@ChangeBackupType,'''','''''') + '''','NULL')
SET @Parameters += ', @BackupSoftware = ' + ISNULL('''' + REPLACE(@BackupSoftware,'''','''''') + '''','NULL')
SET @Parameters += ', @CheckSum = ' + ISNULL('''' + REPLACE(@CheckSum,'''','''''') + '''','NULL')
SET @Parameters += ', @BlockSize = ' + ISNULL(CAST(@BlockSize AS nvarchar),'NULL')
SET @Parameters += ', @BufferCount = ' + ISNULL(CAST(@BufferCount AS nvarchar),'NULL')
SET @Parameters += ', @MaxTransferSize = ' + ISNULL(CAST(@MaxTransferSize AS nvarchar),'NULL')
SET @Parameters += ', @NumberOfFiles = ' + ISNULL(CAST(@NumberOfFiles AS nvarchar),'NULL')
SET @Parameters += ', @MinBackupSizeForMultipleFiles = ' + ISNULL(CAST(@MinBackupSizeForMultipleFiles AS nvarchar),'NULL')
SET @Parameters += ', @MaxFileSize = ' + ISNULL(CAST(@MaxFileSize AS nvarchar),'NULL')
SET @Parameters += ', @CompressionLevel = ' + ISNULL(CAST(@CompressionLevel AS nvarchar),'NULL')
SET @Parameters += ', @Description = ' + ISNULL('''' + REPLACE(@Description,'''','''''') + '''','NULL')
SET @Parameters += ', @Threads = ' + ISNULL(CAST(@Threads AS nvarchar),'NULL')
SET @Parameters += ', @Throttle = ' + ISNULL(CAST(@Throttle AS nvarchar),'NULL')
SET @Parameters += ', @Encrypt = ' + ISNULL('''' + REPLACE(@Encrypt,'''','''''') + '''','NULL')
SET @Parameters += ', @EncryptionAlgorithm = ' + ISNULL('''' + REPLACE(@EncryptionAlgorithm,'''','''''') + '''','NULL')
SET @Parameters += ', @ServerCertificate = ' + ISNULL('''' + REPLACE(@ServerCertificate,'''','''''') + '''','NULL')
SET @Parameters += ', @ServerAsymmetricKey = ' + ISNULL('''' + REPLACE(@ServerAsymmetricKey,'''','''''') + '''','NULL')
SET @Parameters += ', @EncryptionKey = ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL')
SET @Parameters += ', @ReadWriteFileGroups = ' + ISNULL('''' + REPLACE(@ReadWriteFileGroups,'''','''''') + '''','NULL')
SET @Parameters += ', @OverrideBackupPreference = ' + ISNULL('''' + REPLACE(@OverrideBackupPreference,'''','''''') + '''','NULL')
SET @Parameters += ', @NoRecovery = ' + ISNULL('''' + REPLACE(@NoRecovery,'''','''''') + '''','NULL')
SET @Parameters += ', @URL = ' + ISNULL('''' + REPLACE(@URL,'''','''''') + '''','NULL')
SET @Parameters += ', @Credential = ' + ISNULL('''' + REPLACE(@Credential,'''','''''') + '''','NULL')
SET @Parameters += ', @MirrorDirectory = ' + ISNULL('''' + REPLACE(@MirrorDirectory,'''','''''') + '''','NULL')
SET @Parameters += ', @MirrorCleanupTime = ' + ISNULL(CAST(@MirrorCleanupTime AS nvarchar),'NULL')
SET @Parameters += ', @MirrorCleanupMode = ' + ISNULL('''' + REPLACE(@MirrorCleanupMode,'''','''''') + '''','NULL')
SET @Parameters += ', @MirrorURL = ' + ISNULL('''' + REPLACE(@MirrorURL,'''','''''') + '''','NULL')
SET @Parameters += ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')
SET @Parameters += ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL')
SET @Parameters += ', @AdaptiveCompression = ' + ISNULL('''' + REPLACE(@AdaptiveCompression,'''','''''') + '''','NULL')
SET @Parameters += ', @ModificationLevel = ' + ISNULL(CAST(@ModificationLevel AS nvarchar),'NULL')
SET @Parameters += ', @LogSizeSinceLastLogBackup = ' + ISNULL(CAST(@LogSizeSinceLastLogBackup AS nvarchar),'NULL')
SET @Parameters += ', @TimeSinceLastLogBackup = ' + ISNULL(CAST(@TimeSinceLastLogBackup AS nvarchar),'NULL')
SET @Parameters += ', @DataDomainBoostHost = ' + ISNULL('''' + REPLACE(@DataDomainBoostHost,'''','''''') + '''','NULL')
SET @Parameters += ', @DataDomainBoostUser = ' + ISNULL('''' + REPLACE(@DataDomainBoostUser,'''','''''') + '''','NULL')
SET @Parameters += ', @DataDomainBoostDevicePath = ' + ISNULL('''' + REPLACE(@DataDomainBoostDevicePath,'''','''''') + '''','NULL')
SET @Parameters += ', @DataDomainBoostLockboxPath = ' + ISNULL('''' + REPLACE(@DataDomainBoostLockboxPath,'''','''''') + '''','NULL')
SET @Parameters += ', @DirectoryStructure = ' + ISNULL('''' + REPLACE(@DirectoryStructure,'''','''''') + '''','NULL')
SET @Parameters += ', @AvailabilityGroupDirectoryStructure = ' + ISNULL('''' + REPLACE(@AvailabilityGroupDirectoryStructure,'''','''''') + '''','NULL')
SET @Parameters += ', @FileName = ' + ISNULL('''' + REPLACE(@FileName,'''','''''') + '''','NULL')
SET @Parameters += ', @AvailabilityGroupFileName = ' + ISNULL('''' + REPLACE(@AvailabilityGroupFileName,'''','''''') + '''','NULL')
SET @Parameters += ', @FileExtensionFull = ' + ISNULL('''' + REPLACE(@FileExtensionFull,'''','''''') + '''','NULL')
SET @Parameters += ', @FileExtensionDiff = ' + ISNULL('''' + REPLACE(@FileExtensionDiff,'''','''''') + '''','NULL')
SET @Parameters += ', @FileExtensionLog = ' + ISNULL('''' + REPLACE(@FileExtensionLog,'''','''''') + '''','NULL')
SET @Parameters += ', @Init = ' + ISNULL('''' + REPLACE(@Init,'''','''''') + '''','NULL')
SET @Parameters += ', @Format = ' + ISNULL('''' + REPLACE(@Format,'''','''''') + '''','NULL')
SET @Parameters += ', @ObjectLevelRecoveryMap = ' + ISNULL('''' + REPLACE(@ObjectLevelRecoveryMap,'''','''''') + '''','NULL')
SET @Parameters += ', @StringDelimiter = ' + ISNULL('''' + REPLACE(@StringDelimiter,'''','''''') + '''','NULL')
SET @Parameters += ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL')
SET @Parameters += ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','NULL')
SET @Parameters += ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
SET @Parameters += ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL')
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120)
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max))
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Platform: ' + @HostPlatform
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Parameters: ' + @Parameters
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Version: ' + @VersionTimestamp
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
SET @StartMessage = 'Source: https://ola.hallengren.com'
RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------
IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.', 16, 1
END
IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'ANSI_NULLS has to be set to ON for the stored procedure.', 16, 1
END
IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.', 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.', 16, 1
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 '%@DatabaseContext%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.', 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.', 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The table Queue is missing. Download https://ola.hallengren.com/scripts/Queue.sql.', 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The table QueueDatabase is missing. Download https://ola.hallengren.com/scripts/QueueDatabase.sql.', 16, 1
END
IF @@TRANCOUNT <> 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The transaction count is not 0.', 16, 1
END
IF @AmazonRDS = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The stored procedure DatabaseBackup is not supported on Amazon RDS.', 16, 1
END
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
SET @Databases = REPLACE(@Databases, CHAR(10), '')
SET @Databases = REPLACE(@Databases, CHAR(13), '')
WHILE CHARINDEX(@StringDelimiter + ' ', @Databases) > 0 SET @Databases = REPLACE(@Databases, @StringDelimiter + ' ', @StringDelimiter)
WHILE CHARINDEX(' ' + @StringDelimiter, @Databases) > 0 SET @Databases = REPLACE(@Databases, ' ' + @StringDelimiter, @StringDelimiter)
SET @Databases = LTRIM(RTRIM(@Databases));
WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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(@StringDelimiter, @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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 databases.name,
availability_groups.name
FROM sys.databases databases
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON databases.replica_id = dm_hadr_availability_replica_states.replica_id
INNER JOIN sys.availability_groups availability_groups ON dm_hadr_availability_replica_states.group_id = availability_groups.group_id
END
INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, AvailabilityGroup, [Order], Selected, Completed)
SELECT [name] AS DatabaseName,
RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|','')) AS DatabaseNameFS,
CASE WHEN name IN('master','msdb','model') OR is_distributor = 1 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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Databases is not supported.', 16, 1
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(@StringDelimiter + ' ', @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, @StringDelimiter + ' ', @StringDelimiter)
WHILE CHARINDEX(' ' + @StringDelimiter, @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, ' ' + @StringDelimiter, @StringDelimiter)
SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));
WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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(@StringDelimiter, @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroups is not supported.', 16, 1
END
IF (@Databases IS NULL AND @AvailabilityGroups IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'You need to specify one of the parameters @Databases and @AvailabilityGroups.', 16, 2
END
IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'You can only specify one of the parameters @Databases and @AvailabilityGroups.', 16, 3
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 16, 1
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 16, 1
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(@StringDelimiter + ' ', @Directory) > 0 SET @Directory = REPLACE(@Directory, @StringDelimiter + ' ', @StringDelimiter)
WHILE CHARINDEX(' ' + @StringDelimiter, @Directory) > 0 SET @Directory = REPLACE(@Directory, ' ' + @StringDelimiter, @StringDelimiter)
SET @Directory = LTRIM(RTRIM(@Directory));
WITH Directories (StartPosition, EndPosition, Directory) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Directory, 1), 0), LEN(@Directory) + 1) AS EndPosition,
SUBSTRING(@Directory, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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(@StringDelimiter, @Directory, EndPosition + 1), 0), LEN(@Directory) + 1) AS EndPosition,
SUBSTRING(@Directory, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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(@StringDelimiter, @MirrorDirectory, 1), 0), LEN(@MirrorDirectory) + 1) AS EndPosition,
SUBSTRING(@MirrorDirectory, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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(@StringDelimiter, @MirrorDirectory, EndPosition + 1), 0), LEN(@MirrorDirectory) + 1) AS EndPosition,
SUBSTRING(@MirrorDirectory, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @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 //--
----------------------------------------------------------------------------------------------------
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 = 'NUL' AND @HostPlatform = 'Windows')) OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' '))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 1
END
IF EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 2
END
IF (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 3
END
IF (@Directory IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 8) OR (@Directory IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 4
END
IF EXISTS (SELECT * FROM @Directories WHERE Mirror = 0 AND DirectoryPath = 'NUL') AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 0 AND DirectoryPath <> 'NUL')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 5
END
IF EXISTS (SELECT * FROM @Directories WHERE Mirror = 0 AND DirectoryPath = 'NUL') AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Directory is not supported.', 16, 6
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) = ' '))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 1
END
IF EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 2
END
IF (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 3
END
IF @BackupSoftware IN('SQLBACKUP','SQLSAFE') AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 4
END
IF @MirrorDirectory IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 8
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 5
END
IF @MirrorDirectory IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 6
END
IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 0 AND DirectoryPath = 'NUL') AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported.', 16, 7
END
IF (@BackupSoftware IS NULL AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1) AND SERVERPROPERTY('EngineEdition') <> 3)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorDirectory is not supported. Mirrored backup to disk is only available in Enterprise and Developer Edition.', 16, 8
END
----------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM @Errors WHERE Severity >= 16)
BEGIN
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentRootDirectoryID = ID,
@CurrentRootDirectoryPath = DirectoryPath
FROM @Directories
WHERE Completed = 0
AND DirectoryPath <> 'NUL'
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The directory ' + @CurrentRootDirectoryPath + ' does not exist.', 16, 1
END
UPDATE @Directories
SET Completed = 1
WHERE ID = @CurrentRootDirectoryID
SET @CurrentRootDirectoryID = NULL
SET @CurrentRootDirectoryPath = NULL
DELETE FROM @DirectoryInfo
END
END
----------------------------------------------------------------------------------------------------
--// Select URLs //--
----------------------------------------------------------------------------------------------------
SET @URL = REPLACE(@URL, CHAR(10), '')
SET @URL = REPLACE(@URL, CHAR(13), '')
WHILE CHARINDEX(@StringDelimiter + ' ', @URL) > 0 SET @URL = REPLACE(@URL, @StringDelimiter + ' ', @StringDelimiter)
WHILE CHARINDEX(' ' + @StringDelimiter, @URL) > 0 SET @URL = REPLACE(@URL, ' ' + @StringDelimiter, @StringDelimiter)
SET @URL = LTRIM(RTRIM(@URL));
WITH URLs (StartPosition, EndPosition, [URL]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @URL, 1), 0), LEN(@URL) + 1) AS EndPosition,
SUBSTRING(@URL, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @URL, 1), 0), LEN(@URL) + 1) - 1) AS [URL]
WHERE @URL IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @URL, EndPosition + 1), 0), LEN(@URL) + 1) AS EndPosition,
SUBSTRING(@URL, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @URL, EndPosition + 1), 0), LEN(@URL) + 1) - EndPosition - 1) AS [URL]
FROM URLs
WHERE EndPosition < LEN(@URL) + 1
)
INSERT INTO @URLs (ID, DirectoryPath, Mirror)
SELECT ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,
[URL],
0
FROM URLs
OPTION (MAXRECURSION 0)
SET @MirrorURL = REPLACE(@MirrorURL, CHAR(10), '')
SET @MirrorURL = REPLACE(@MirrorURL, CHAR(13), '')
WHILE CHARINDEX(@StringDelimiter + ' ', @MirrorURL) > 0 SET @MirrorURL = REPLACE(@MirrorURL, @StringDelimiter + ' ', @StringDelimiter)
WHILE CHARINDEX(' ' + @StringDelimiter ,@MirrorURL) > 0 SET @MirrorURL = REPLACE(@MirrorURL, ' ' + @StringDelimiter, @StringDelimiter)
SET @MirrorURL = LTRIM(RTRIM(@MirrorURL));
WITH URLs (StartPosition, EndPosition, [URL]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @MirrorURL, 1), 0), LEN(@MirrorURL) + 1) AS EndPosition,
SUBSTRING(@MirrorURL, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @MirrorURL, 1), 0), LEN(@MirrorURL) + 1) - 1) AS [URL]
WHERE @MirrorURL IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @MirrorURL, EndPosition + 1), 0), LEN(@MirrorURL) + 1) AS EndPosition,
SUBSTRING(@MirrorURL, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @MirrorURL, EndPosition + 1), 0), LEN(@MirrorURL) + 1) - EndPosition - 1) AS [URL]
FROM URLs
WHERE EndPosition < LEN(@MirrorURL) + 1
)
INSERT INTO @URLs (ID, DirectoryPath, Mirror)
SELECT (SELECT COUNT(*) FROM @URLs) + ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS ID,
[URL],
1
FROM URLs
OPTION (MAXRECURSION 0)
----------------------------------------------------------------------------------------------------
--// Check URLs //--
----------------------------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 0 AND DirectoryPath NOT LIKE 'https://%/%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 1
END
IF EXISTS (SELECT * FROM @URLs GROUP BY DirectoryPath HAVING COUNT(*) <> 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 2
END
IF (SELECT COUNT(*) FROM @URLs WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @URLs WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @URLs WHERE Mirror = 1) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 1 AND DirectoryPath NOT LIKE 'https://%/%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 1
END
IF EXISTS (SELECT * FROM @URLs GROUP BY DirectoryPath HAVING COUNT(*) <> 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 2
END
IF (SELECT COUNT(*) FROM @URLs WHERE Mirror = 0) <> (SELECT COUNT(*) FROM @URLs WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @URLs WHERE Mirror = 1) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
--// 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
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BackupType is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF SERVERPROPERTY('EngineEdition') = 8 AND NOT (@BackupType = 'FULL' AND @CopyOnly = 'Y')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'SQL Database Managed Instance only supports COPY_ONLY full backups.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @Verify NOT IN ('Y','N') OR @Verify IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Verify is not supported.', 16, 1
END
IF @BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND @Verify = 'Y'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Verify is not supported. Verify is not supported with encrypted backups with Idera SQL Safe Backup', 16, 2
END
IF @Verify = 'Y' AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Verify is not supported. Verify is not supported with Data Domain Boost', 16, 3
END
IF @Verify = 'Y' AND EXISTS(SELECT * FROM @Directories WHERE DirectoryPath = 'NUL')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Verify is not supported. Verify is not supported when backing up to NUL.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @CleanupTime < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported.', 16, 1
END
IF @CleanupTime IS NOT NULL AND @URL IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported on Azure Blob Storage.', 16, 2
END
IF @CleanupTime IS NOT NULL AND @HostPlatform = 'Linux'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported on Linux.', 16, 3
END
IF @CleanupTime IS NOT NULL AND EXISTS(SELECT * FROM @Directories WHERE DirectoryPath = 'NUL')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported when backing up to NUL.', 16, 4
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {DatabaseName} is not part of the directory.', 16, 5
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {BackupType} is not part of the directory.', 16, 6
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
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported if the token {CopyOnly} is not part of the directory.', 16, 7
END
----------------------------------------------------------------------------------------------------
IF @CleanupMode NOT IN('BEFORE_BACKUP','AFTER_BACKUP') OR @CleanupMode IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CleanupMode is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @Compress NOT IN ('Y','N') OR @Compress IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Compress is not supported.', 16, 1
END
IF @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') IN (3, 8) OR SERVERPROPERTY('EditionID') IN (-1534726760, 284895786))))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Compress is not supported.', 16, 2
END
IF @Compress = 'N' AND @BackupSoftware IN ('LITESPEED','SQLBACKUP','SQLSAFE') AND (@CompressionLevel IS NULL OR @CompressionLevel >= 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Compress is not supported.', 16, 3
END
IF @Compress = 'Y' AND @BackupSoftware IN ('LITESPEED','SQLBACKUP','SQLSAFE') AND @CompressionLevel = 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Compress is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @CopyOnly NOT IN ('Y','N') OR @CopyOnly IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CopyOnly is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @ChangeBackupType NOT IN ('Y','N') OR @ChangeBackupType IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ChangeBackupType is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @BackupSoftware NOT IN ('LITESPEED','SQLBACKUP','SQLSAFE','DATA_DOMAIN_BOOST')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BackupSoftware is not supported.', 16, 1
END
IF @BackupSoftware IS NOT NULL AND @HostPlatform = 'Linux'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BackupSoftware is not supported. Only native backups are supported on Linux', 16, 2
END
IF @BackupSoftware = 'LITESPEED' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_backup_database')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'LiteSpeed for SQL Server is not installed. Download https://www.quest.com/products/litespeed-for-sql-server/.', 16, 3
END
IF @BackupSoftware = 'SQLBACKUP' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'sqlbackup')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'Red Gate SQL Backup Pro is not installed. Download https://www.red-gate.com/products/dba/sql-backup/.', 16, 4
END
IF @BackupSoftware = 'SQLSAFE' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_ss_backup')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'Idera SQL Safe Backup is not installed. Download https://www.idera.com/productssolutions/sqlserver/sqlsafebackup.', 16, 5
END
IF @BackupSoftware = 'DATA_DOMAIN_BOOST' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'PC' AND [name] = 'emc_run_backup')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'EMC Data Domain Boost is not installed. Download https://www.emc.com/en-us/data-protection/data-domain.htm.', 16, 6
END
----------------------------------------------------------------------------------------------------
IF @CheckSum NOT IN ('Y','N') OR @CheckSum IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CheckSum is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @BlockSize NOT IN (512,1024,2048,4096,8192,16384,32768,65536)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BlockSize is not supported.', 16, 1
END
IF @BlockSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BlockSize is not supported. This parameter is not supported with Redgate SQL Backup Pro', 16, 2
END
IF @BlockSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BlockSize is not supported. This parameter is not supported with Idera SQL Safe', 16, 3
END
IF @BlockSize IS NOT NULL AND @URL IS NOT NULL AND @Credential IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BlockSize is not supported.', 16, 4
END
IF @BlockSize IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BlockSize is not supported. This parameter is not supported with Data Domain Boost', 16, 5
END
----------------------------------------------------------------------------------------------------
IF @BufferCount <= 0 OR @BufferCount > 2147483647
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BufferCount is not supported.', 16, 1
END
IF @BufferCount IS NOT NULL AND @BackupSoftware = 'SQLBACKUP'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BufferCount is not supported.', 16, 2
END
IF @BufferCount IS NOT NULL AND @BackupSoftware = 'SQLSAFE'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @BufferCount is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxTransferSize is not supported.', 16, 1
END
IF @MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxTransferSize is not supported.', 16, 2
END
IF @MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxTransferSize is not supported.', 16, 3
END
IF @MaxTransferSize IS NOT NULL AND @URL IS NOT NULL AND @Credential IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxTransferSize is not supported.', 16, 4
END
IF @MaxTransferSize IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxTransferSize is not supported.', 16, 5
END
----------------------------------------------------------------------------------------------------
IF @NumberOfFiles < 1 OR @NumberOfFiles > 64
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 1
END
IF @NumberOfFiles > 32 AND @BackupSoftware = 'SQLBACKUP'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 2
END
IF @NumberOfFiles < (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 3
END
IF @NumberOfFiles % (SELECT NULLIF(COUNT(*),0) FROM @Directories WHERE Mirror = 0) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 4
END
IF @URL IS NOT NULL AND @Credential IS NOT NULL AND @NumberOfFiles <> 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 5
END
IF @NumberOfFiles > 1 AND @BackupSoftware IN('SQLBACKUP','SQLSAFE') AND EXISTS(SELECT * FROM @Directories WHERE Mirror = 1)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 6
END
IF @NumberOfFiles > 32 AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 7
END
IF @NumberOfFiles < (SELECT COUNT(*) FROM @URLs WHERE Mirror = 0)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 8
END
IF @NumberOfFiles % (SELECT NULLIF(COUNT(*),0) FROM @URLs WHERE Mirror = 0) > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NumberOfFiles is not supported.', 16, 9
END
----------------------------------------------------------------------------------------------------
IF @MinBackupSizeForMultipleFiles <= 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MinBackupSizeForMultipleFiles is not supported.', 16, 1
END
IF @MinBackupSizeForMultipleFiles IS NOT NULL AND @NumberOfFiles IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MinBackupSizeForMultipleFiles is not supported. This parameter can only be used together with @NumberOfFiles.', 16, 2
END
IF @MinBackupSizeForMultipleFiles IS NOT NULL AND @BackupType = 'DIFF' AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MinBackupSizeForMultipleFiles is not supported. The column sys.dm_db_file_space_usage.modified_extent_page_count is not available in this version of SQL Server.', 16, 3
END
IF @MinBackupSizeForMultipleFiles IS NOT NULL AND @BackupType = 'LOG' AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MinBackupSizeForMultipleFiles is not supported. The column sys.dm_db_log_stats.log_since_last_log_backup_mb is not available in this version of SQL Server.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @MaxFileSize <= 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxFileSize is not supported.', 16, 1
END
IF @MaxFileSize IS NOT NULL AND @NumberOfFiles IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameters @MaxFileSize and @NumberOfFiles cannot be used together.', 16, 2
END
IF @MaxFileSize IS NOT NULL AND @BackupType = 'DIFF' AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxFileSize is not supported. The column sys.dm_db_file_space_usage.modified_extent_page_count is not available in this version of SQL Server.', 16, 3
END
IF @MaxFileSize IS NOT NULL AND @BackupType = 'LOG' AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MaxFileSize is not supported. The column sys.dm_db_log_stats.log_since_last_log_backup_mb is not available in this version of SQL Server.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF (@BackupSoftware IS NULL AND @CompressionLevel IS NOT NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CompressionLevel is not supported.', 16, 1
END
IF @BackupSoftware = 'LITESPEED' AND (@CompressionLevel < 0 OR @CompressionLevel > 8)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CompressionLevel is not supported.', 16, 2
END
IF @BackupSoftware = 'SQLBACKUP' AND (@CompressionLevel < 0 OR @CompressionLevel > 4)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CompressionLevel is not supported.', 16, 3
END
IF @BackupSoftware = 'SQLSAFE' AND (@CompressionLevel < 1 OR @CompressionLevel > 4)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CompressionLevel is not supported.', 16, 4
END
IF @CompressionLevel IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @CompressionLevel is not supported.', 16, 5
END
----------------------------------------------------------------------------------------------------
IF LEN(@Description) > 255
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Description is not supported.', 16, 1
END
IF @BackupSoftware = 'LITESPEED' AND LEN(@Description) > 128
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Description is not supported.', 16, 2
END
IF @BackupSoftware = 'DATA_DOMAIN_BOOST' AND LEN(@Description) > 254
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Description is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @Threads IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED','SQLBACKUP','SQLSAFE') OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Threads is not supported.', 16, 1
END
IF @BackupSoftware = 'LITESPEED' AND (@Threads < 1 OR @Threads > 32)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Threads is not supported.', 16, 2
END
IF @BackupSoftware = 'SQLBACKUP' AND (@Threads < 2 OR @Threads > 32)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Threads is not supported.', 16, 3
END
IF @BackupSoftware = 'SQLSAFE' AND (@Threads < 1 OR @Threads > 64)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Threads is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @Throttle < 1 OR @Throttle > 100
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Throttle is not supported.', 16, 1
END
IF @Throttle IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Throttle is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @Encrypt NOT IN('Y','N') OR @Encrypt IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Encrypt is not supported.', 16, 1
END
IF @Encrypt = 'Y' AND @BackupSoftware IS NULL AND NOT (@Version >= 12 AND (SERVERPROPERTY('EngineEdition') = 3) OR SERVERPROPERTY('EditionID') IN(-1534726760, 284895786))
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Encrypt is not supported.', 16, 2
END
IF @Encrypt = 'Y' AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Encrypt is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @BackupSoftware IS NULL AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_192','AES_256','TRIPLE_DES_3KEY') OR @EncryptionAlgorithm IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionAlgorithm is not supported.', 16, 1
END
IF @BackupSoftware = 'LITESPEED' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('RC2_40','RC2_56','RC2_112','RC2_128','TRIPLE_DES_3KEY','RC4_128','AES_128','AES_192','AES_256') OR @EncryptionAlgorithm IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionAlgorithm is not supported.', 16, 2
END
IF @BackupSoftware = 'SQLBACKUP' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_256') OR @EncryptionAlgorithm IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionAlgorithm is not supported.', 16, 3
END
IF @BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND (@EncryptionAlgorithm NOT IN('AES_128','AES_256') OR @EncryptionAlgorithm IS NULL)
OR (@EncryptionAlgorithm IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionAlgorithm is not supported.', 16, 4
END
IF @EncryptionAlgorithm IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionAlgorithm is not supported.', 16, 5
END
----------------------------------------------------------------------------------------------------
IF (NOT (@BackupSoftware IS NULL AND @Encrypt = 'Y') AND @ServerCertificate IS NOT NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerCertificate is not supported.', 16, 1
END
IF @BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerCertificate IS NULL AND @ServerAsymmetricKey IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerCertificate is not supported.', 16, 2
END
IF @BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerCertificate IS NOT NULL AND @ServerAsymmetricKey IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerCertificate is not supported.', 16, 3
END
IF @ServerCertificate IS NOT NULL AND NOT EXISTS(SELECT * FROM master.sys.certificates WHERE name = @ServerCertificate)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerCertificate is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF NOT (@BackupSoftware IS NULL AND @Encrypt = 'Y') AND @ServerAsymmetricKey IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerAsymmetricKey is not supported.', 16, 1
END
IF @BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerAsymmetricKey IS NULL AND @ServerCertificate IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerAsymmetricKey is not supported.', 16, 2
END
IF @BackupSoftware IS NULL AND @Encrypt = 'Y' AND @ServerAsymmetricKey IS NOT NULL AND @ServerCertificate IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerAsymmetricKey is not supported.', 16, 3
END
IF @ServerAsymmetricKey IS NOT NULL AND NOT EXISTS(SELECT * FROM master.sys.asymmetric_keys WHERE name = @ServerAsymmetricKey)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ServerAsymmetricKey is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @EncryptionKey IS NOT NULL AND @BackupSoftware IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionKey is not supported.', 16, 1
END
IF @EncryptionKey IS NOT NULL AND @Encrypt = 'N'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionKey is not supported.', 16, 2
END
IF @EncryptionKey IS NULL AND @Encrypt = 'Y' AND @BackupSoftware IN('LITESPEED','SQLBACKUP','SQLSAFE')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionKey is not supported.', 16, 3
END
IF @EncryptionKey IS NOT NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @EncryptionKey is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @ReadWriteFileGroups NOT IN('Y','N') OR @ReadWriteFileGroups IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ReadWriteFileGroups is not supported.', 16, 1
END
IF @ReadWriteFileGroups = 'Y' AND @BackupType = 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ReadWriteFileGroups is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @OverrideBackupPreference NOT IN('Y','N') OR @OverrideBackupPreference IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @OverrideBackupPreference is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @NoRecovery NOT IN('Y','N') OR @NoRecovery IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NoRecovery is not supported.', 16, 1
END
IF @NoRecovery = 'Y' AND @BackupType <> 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NoRecovery is not supported.', 16, 2
END
IF @NoRecovery = 'Y' AND @BackupSoftware = 'SQLSAFE'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @NoRecovery is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @URL IS NOT NULL AND @Directory IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 1
END
IF @URL IS NOT NULL AND @MirrorDirectory IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 2
END
IF @URL IS NOT NULL AND @Version < 11.03339
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 3
END
IF @URL IS NOT NULL AND @BackupSoftware IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @URL is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @Credential IS NULL AND @URL IS NOT NULL AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') = 8)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Credential is not supported.', 16, 1
END
IF @Credential IS NOT NULL AND @URL IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Credential is not supported.', 16, 2
END
IF @URL IS NOT NULL AND @Credential IS NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'SHARED ACCESS SIGNATURE')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Credential is not supported.', 16, 3
END
IF @Credential IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE name = @Credential)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Credential is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @MirrorCleanupTime < 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorCleanupTime is not supported.', 16, 1
END
IF @MirrorCleanupTime IS NOT NULL AND @MirrorDirectory IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorCleanupTime is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @MirrorCleanupMode NOT IN('BEFORE_BACKUP','AFTER_BACKUP') OR @MirrorCleanupMode IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorCleanupMode is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @MirrorURL IS NOT NULL AND @Directory IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 1
END
IF @MirrorURL IS NOT NULL AND @MirrorDirectory IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 2
END
IF @MirrorURL IS NOT NULL AND @Version < 11.03339
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 3
END
IF @MirrorURL IS NOT NULL AND @BackupSoftware IS NOT NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 4
END
IF @MirrorURL IS NOT NULL AND @URL IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @MirrorURL is not supported.', 16, 5
END
----------------------------------------------------------------------------------------------------
IF @Updateability NOT IN('READ_ONLY','READ_WRITE','ALL') OR @Updateability IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Updateability is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @AdaptiveCompression NOT IN('SIZE','SPEED')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AdaptiveCompression is not supported.', 16, 1
END
IF @AdaptiveCompression IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AdaptiveCompression is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @ModificationLevel IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 1
END
IF @ModificationLevel IS NOT NULL AND @ChangeBackupType = 'N'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 2
END
IF @ModificationLevel IS NOT NULL AND @BackupType <> 'DIFF'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @LogSizeSinceLastLogBackup IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @LogSizeSinceLastLogBackup is not supported.', 16, 1
END
IF @LogSizeSinceLastLogBackup IS NOT NULL AND @BackupType <> 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @LogSizeSinceLastLogBackup is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @TimeSinceLastLogBackup IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_backup_time')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @TimeSinceLastLogBackup is not supported.', 16, 1
END
IF @TimeSinceLastLogBackup IS NOT NULL AND @BackupType <> 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @TimeSinceLastLogBackup is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF (@TimeSinceLastLogBackup IS NOT NULL AND @LogSizeSinceLastLogBackup IS NULL) OR (@TimeSinceLastLogBackup IS NULL AND @LogSizeSinceLastLogBackup IS NOT NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameters @TimeSinceLastLogBackup and @LogSizeSinceLastLogBackup can only be used together.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @DataDomainBoostHost IS NOT NULL AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostHost is not supported.', 16, 1
END
IF @DataDomainBoostHost IS NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostHost is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @DataDomainBoostUser IS NOT NULL AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostUser is not supported.', 16, 1
END
IF @DataDomainBoostUser IS NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostUser is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @DataDomainBoostDevicePath IS NOT NULL AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostDevicePath is not supported.', 16, 1
END
IF @DataDomainBoostDevicePath IS NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostDevicePath is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @DataDomainBoostLockboxPath IS NOT NULL AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DataDomainBoostLockboxPath is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @DirectoryStructure = ''
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DirectoryStructure is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @AvailabilityGroupDirectoryStructure = ''
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupDirectoryStructure is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @FileName IS NULL OR @FileName = ''
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 1
END
IF @FileName NOT LIKE '%.{FileExtension}'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 2
END
IF (@NumberOfFiles > 1 AND @FileName NOT LIKE '%{FileNumber}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 3
END
IF @FileName LIKE '%{DirectorySeperator}%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 4
END
IF @FileName LIKE '%/%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 5
END
IF @FileName LIKE '%\%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileName is not supported.', 16, 6
END
----------------------------------------------------------------------------------------------------
IF (SERVERPROPERTY('IsHadrEnabled') = 1 AND @AvailabilityGroupFileName IS NULL)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 1
END
IF @AvailabilityGroupFileName = ''
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 2
END
IF @AvailabilityGroupFileName NOT LIKE '%.{FileExtension}'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 3
END
IF (@NumberOfFiles > 1 AND @AvailabilityGroupFileName NOT LIKE '%{FileNumber}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 4
END
IF @AvailabilityGroupFileName LIKE '%{DirectorySeperator}%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 5
END
IF @AvailabilityGroupFileName LIKE '%/%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 6
END
IF @AvailabilityGroupFileName LIKE '%\%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @AvailabilityGroupFileName is not supported.', 16, 7
END
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@DirectoryStructure,'{DirectorySeparator}',''),'{ServerName}',''),'{InstanceName}',''),'{ServiceName}',''),'{ClusterName}',''),'{AvailabilityGroupName}',''),'{DatabaseName}',''),'{BackupType}',''),'{Partial}',''),'{CopyOnly}',''),'{Description}',''),'{Year}',''),'{Month}',''),'{Day}',''),'{Hour}',''),'{Minute}',''),'{Second}',''),'{Millisecond}',''),'{Microsecond}',''),'{MajorVersion}',''),'{MinorVersion}','') AS DirectoryStructure) Temp WHERE DirectoryStructure LIKE '%{%' OR DirectoryStructure LIKE '%}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameter @DirectoryStructure contains one or more tokens that are not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@AvailabilityGroupDirectoryStructure,'{DirectorySeparator}',''),'{ServerName}',''),'{InstanceName}',''),'{ServiceName}',''),'{ClusterName}',''),'{AvailabilityGroupName}',''),'{DatabaseName}',''),'{BackupType}',''),'{Partial}',''),'{CopyOnly}',''),'{Description}',''),'{Year}',''),'{Month}',''),'{Day}',''),'{Hour}',''),'{Minute}',''),'{Second}',''),'{Millisecond}',''),'{Microsecond}',''),'{MajorVersion}',''),'{MinorVersion}','') AS AvailabilityGroupDirectoryStructure) Temp WHERE AvailabilityGroupDirectoryStructure LIKE '%{%' OR AvailabilityGroupDirectoryStructure LIKE '%}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameter @AvailabilityGroupDirectoryStructure contains one or more tokens that are not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@FileName,'{DirectorySeparator}',''),'{ServerName}',''),'{InstanceName}',''),'{ServiceName}',''),'{ClusterName}',''),'{AvailabilityGroupName}',''),'{DatabaseName}',''),'{BackupType}',''),'{Partial}',''),'{CopyOnly}',''),'{Description}',''),'{Year}',''),'{Month}',''),'{Day}',''),'{Hour}',''),'{Minute}',''),'{Second}',''),'{Millisecond}',''),'{Microsecond}',''),'{FileNumber}',''),'{NumberOfFiles}',''),'{FileExtension}',''),'{MajorVersion}',''),'{MinorVersion}','') AS [FileName]) Temp WHERE [FileName] LIKE '%{%' OR [FileName] LIKE '%}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameter @FileName contains one or more tokens that are not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@AvailabilityGroupFileName,'{DirectorySeparator}',''),'{ServerName}',''),'{InstanceName}',''),'{ServiceName}',''),'{ClusterName}',''),'{AvailabilityGroupName}',''),'{DatabaseName}',''),'{BackupType}',''),'{Partial}',''),'{CopyOnly}',''),'{Description}',''),'{Year}',''),'{Month}',''),'{Day}',''),'{Hour}',''),'{Minute}',''),'{Second}',''),'{Millisecond}',''),'{Microsecond}',''),'{FileNumber}',''),'{NumberOfFiles}',''),'{FileExtension}',''),'{MajorVersion}',''),'{MinorVersion}','') AS AvailabilityGroupFileName) Temp WHERE AvailabilityGroupFileName LIKE '%{%' OR AvailabilityGroupFileName LIKE '%}%')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The parameter @AvailabilityGroupFileName contains one or more tokens that are not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @FileExtensionFull LIKE '%.%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileExtensionFull is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @FileExtensionDiff LIKE '%.%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileExtensionDiff is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @FileExtensionLog LIKE '%.%'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @FileExtensionLog is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @Init NOT IN('Y','N') OR @Init IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Init is not supported.', 16, 1
END
IF @Init = 'Y' AND @BackupType = 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Init is not supported.', 16, 2
END
IF @Init = 'Y' AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Init is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @Format NOT IN('Y','N') OR @Format IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Format is not supported.', 16, 1
END
IF @Format = 'Y' AND @BackupType = 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Format is not supported.', 16, 2
END
IF @Format = 'Y' AND @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Format is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @ObjectLevelRecoveryMap NOT IN('Y','N') OR @ObjectLevelRecoveryMap IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ObjectLevelRecovery is not supported.', 16, 1
END
IF @ObjectLevelRecoveryMap = 'Y' AND @BackupSoftware IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ObjectLevelRecovery is not supported.', 16, 2
END
IF @ObjectLevelRecoveryMap = 'Y' AND @BackupSoftware <> 'LITESPEED'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ObjectLevelRecovery is not supported.', 16, 3
END
IF @ObjectLevelRecoveryMap = 'Y' AND @BackupType = 'LOG'
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @ObjectLevelRecovery is not supported.', 16, 4
END
----------------------------------------------------------------------------------------------------
IF @StringDelimiter IS NULL OR LEN(@StringDelimiter) > 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @StringDelimiter is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @DatabaseOrder NOT IN('DATABASE_NAME_ASC','DATABASE_NAME_DESC','DATABASE_SIZE_ASC','DATABASE_SIZE_DESC','LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC','LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DatabaseOrder is not supported.', 16, 1
END
IF @DatabaseOrder IN('LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC','LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC') AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DatabaseOrder is not supported. The column sys.dm_db_log_stats.log_since_last_log_backup_mb is not available in this version of SQL Server.', 16, 2
END
IF @DatabaseOrder IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 5
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DatabaseOrder is not supported.', 16, 3
END
----------------------------------------------------------------------------------------------------
IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 1
END
IF @DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 2
END
----------------------------------------------------------------------------------------------------
IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @LogToTable is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF @Execute NOT IN('Y','N') OR @Execute IS NULL
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The value for the parameter @Execute is not supported.', 16, 1
END
----------------------------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM @Errors)
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The documentation is available at https://ola.hallengren.com/sql-server-backup.html.', 16, 1
END
----------------------------------------------------------------------------------------------------
--// Check that selected databases and availability groups exist //--
----------------------------------------------------------------------------------------------------
SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @SelectedDatabases
WHERE DatabaseName NOT LIKE '%[%]%'
AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The following databases in the @Databases parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1
END
SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(AvailabilityGroupName) + ', '
FROM @SelectedAvailabilityGroups
WHERE AvailabilityGroupName NOT LIKE '%[%]%'
AND AvailabilityGroupName NOT IN (SELECT AvailabilityGroupName FROM @tmpAvailabilityGroups)
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The following availability groups do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1
END
----------------------------------------------------------------------------------------------------
--// Check @@SERVERNAME //--
----------------------------------------------------------------------------------------------------
IF @@SERVERNAME <> CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) AND SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The @@SERVERNAME does not match SERVERPROPERTY(''ServerName''). See ' + CASE WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server' WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/rename-a-sql-server-failover-cluster-instance' END + '.', 16, 1
END
----------------------------------------------------------------------------------------------------
--// Raise errors //--
----------------------------------------------------------------------------------------------------
DECLARE ErrorCursor CURSOR FAST_FORWARD FOR SELECT [Message], Severity, [State] FROM @Errors ORDER BY [ID] ASC
OPEN ErrorCursor
FETCH ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR('%s', @CurrentSeverity, @CurrentState, @CurrentMessage) WITH NOWAIT
RAISERROR(@EmptyLine, 10, 1) WITH NOWAIT
FETCH NEXT FROM ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState
END
CLOSE ErrorCursor
DEALLOCATE ErrorCursor
IF EXISTS (SELECT * FROM @Errors WHERE Severity >= 16)
BEGIN
SET @ReturnCode = 50000
GOTO Logging
END
----------------------------------------------------------------------------------------------------
--// Check Availability Group cluster name //--
----------------------------------------------------------------------------------------------------
IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
SELECT @Cluster = NULLIF(cluster_name,'')
FROM sys.dm_hadr_cluster
END
----------------------------------------------------------------------------------------------------
--// Update database order //--
----------------------------------------------------------------------------------------------------
IF @DatabaseOrder IN('DATABASE_SIZE_ASC','DATABASE_SIZE_DESC')
BEGIN
UPDATE tmpDatabases
SET DatabaseSize = (SELECT SUM(CAST(size AS bigint)) FROM sys.master_files WHERE [type] = 0 AND database_id = DB_ID(tmpDatabases.DatabaseName))
FROM @tmpDatabases tmpDatabases
END
IF @DatabaseOrder IN('LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC','LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC')
BEGIN
UPDATE tmpDatabases
SET LogSizeSinceLastLogBackup = (SELECT log_since_last_log_backup_mb FROM sys.dm_db_log_stats(DB_ID(tmpDatabases.DatabaseName)))
FROM @tmpDatabases tmpDatabases
END
IF @DatabaseOrder IS NULL
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, DatabaseName ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'DATABASE_NAME_ASC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'DATABASE_NAME_DESC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName DESC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'DATABASE_SIZE_ASC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'DATABASE_SIZE_DESC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize DESC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'LOG_SIZE_SINCE_LAST_LOG_BACKUP_ASC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LogSizeSinceLastLogBackup ASC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
ELSE
IF @DatabaseOrder = 'LOG_SIZE_SINCE_LAST_LOG_BACKUP_DESC'
BEGIN
WITH tmpDatabases AS (
SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LogSizeSinceLastLogBackup DESC) AS RowNumber
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
)
UPDATE tmpDatabases
SET [Order] = RowNumber
END
----------------------------------------------------------------------------------------------------
--// Update the queue //--
----------------------------------------------------------------------------------------------------
IF @DatabasesInParallel = 'Y'
BEGIN
BEGIN TRY
SELECT @QueueID = QueueID
FROM dbo.[Queue]
WHERE SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND [Parameters] = @Parameters
IF @QueueID IS NULL
BEGIN
BEGIN TRANSACTION
SELECT @QueueID = QueueID
FROM dbo.[Queue] WITH (UPDLOCK, HOLDLOCK)
WHERE SchemaName = @SchemaName
AND ObjectName = @ObjectName
AND [Parameters] = @Parameters
IF @QueueID IS NULL
BEGIN
INSERT INTO dbo.[Queue] (SchemaName, ObjectName, [Parameters])
SELECT @SchemaName, @ObjectName, @Parameters
SET @QueueID = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
END
BEGIN TRANSACTION
UPDATE [Queue]
SET QueueStartTime = SYSDATETIME(),
SessionID = @@SPID,
RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),
RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID)
FROM dbo.[Queue] [Queue]
WHERE QueueID = @QueueID
AND NOT EXISTS (SELECT *
FROM sys.dm_exec_requests
WHERE session_id = [Queue].SessionID
AND request_id = [Queue].RequestID
AND start_time = [Queue].RequestStartTime)
AND NOT EXISTS (SELECT *
FROM dbo.QueueDatabase QueueDatabase
INNER JOIN sys.dm_exec_requests ON QueueDatabase.SessionID = session_id AND QueueDatabase.RequestID = request_id AND QueueDatabase.RequestStartTime = start_time
WHERE QueueDatabase.QueueID = @QueueID)
IF @@ROWCOUNT = 1
BEGIN
INSERT INTO dbo.QueueDatabase (QueueID, DatabaseName)
SELECT @QueueID AS QueueID,
DatabaseName
FROM @tmpDatabases tmpDatabases
WHERE Selected = 1
AND NOT EXISTS (SELECT * FROM dbo.QueueDatabase WHERE DatabaseName = tmpDatabases.DatabaseName AND QueueID = @QueueID)
DELETE QueueDatabase
FROM dbo.QueueDatabase QueueDatabase
WHERE QueueID = @QueueID
AND NOT EXISTS (SELECT * FROM @tmpDatabases tmpDatabases WHERE DatabaseName = QueueDatabase.DatabaseName AND Selected = 1)
UPDATE QueueDatabase
SET DatabaseOrder = tmpDatabases.[Order]
FROM dbo.QueueDatabase QueueDatabase
INNER JOIN @tmpDatabases tmpDatabases ON QueueDatabase.DatabaseName = tmpDatabases.DatabaseName
WHERE QueueID = @QueueID
END
COMMIT TRANSACTION
SELECT @QueueStartTime = QueueStartTime
FROM dbo.[Queue]
WHERE QueueID = @QueueID
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'')
RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
SET @ReturnCode = ERROR_NUMBER()
GOTO Logging
END CATCH
END
----------------------------------------------------------------------------------------------------
--// Execute backup commands //--
----------------------------------------------------------------------------------------------------
WHILE (1 = 1)
BEGIN
IF @DatabasesInParallel = 'Y'
BEGIN
UPDATE QueueDatabase
SET DatabaseStartTime = NULL,
SessionID = NULL,
RequestID = NULL,
RequestStartTime = NULL
FROM dbo.QueueDatabase QueueDatabase
WHERE QueueID = @QueueID
AND DatabaseStartTime IS NOT NULL
AND DatabaseEndTime IS NULL
AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests WHERE session_id = QueueDatabase.SessionID AND request_id = QueueDatabase.RequestID AND start_time = QueueDatabase.RequestStartTime)
UPDATE QueueDatabase
SET DatabaseStartTime = SYSDATETIME(),
DatabaseEndTime = NULL,
SessionID = @@SPID,
RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),
RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID),
@CurrentDatabaseName = DatabaseName,
@CurrentDatabaseNameFS = (SELECT DatabaseNameFS FROM @tmpDatabases WHERE DatabaseName = QueueDatabase.DatabaseName)
FROM (SELECT TOP 1 DatabaseStartTime,
DatabaseEndTime,
SessionID,
RequestID,
RequestStartTime,
DatabaseName
FROM dbo.QueueDatabase
WHERE QueueID = @QueueID
AND (DatabaseStartTime < @QueueStartTime OR DatabaseStartTime IS NULL)
AND NOT (DatabaseStartTime IS NOT NULL AND DatabaseEndTime IS NULL)
ORDER BY DatabaseOrder ASC
) QueueDatabase
END
ELSE
BEGIN
SELECT TOP 1 @CurrentDBID = ID,
@CurrentDatabaseName = DatabaseName,
@CurrentDatabaseNameFS = DatabaseNameFS
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY [Order] ASC
END
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
SET @CurrentDatabase_sp_executesql = QUOTENAME(@CurrentDatabaseName) + '.sys.sp_executesql'
BEGIN
SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120)
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Database: ' + QUOTENAME(@CurrentDatabaseName)
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
SELECT @CurrentUserAccess = user_access_desc,
@CurrentIsReadOnly = is_read_only,
@CurrentDatabaseState = state_desc,
@CurrentInStandby = is_in_standby,
@CurrentRecoveryModel = recovery_model_desc,
@CurrentIsEncrypted = is_encrypted,
@CurrentDatabaseSize = (SELECT SUM(CAST(size AS bigint)) FROM sys.master_files WHERE [type] = 0 AND database_id = sys.databases.database_id)
FROM sys.databases
WHERE [name] = @CurrentDatabaseName
BEGIN
SET @DatabaseMessage = 'State: ' + @CurrentDatabaseState
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Standby: ' + CASE WHEN @CurrentInStandby = 1 THEN 'Yes' ELSE 'No' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'User access: ' + @CurrentUserAccess
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Recovery model: ' + @CurrentRecoveryModel
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Encrypted: ' + CASE WHEN @CurrentIsEncrypted = 1 THEN 'Yes' WHEN @CurrentIsEncrypted = 0 THEN 'No' ELSE 'N/A' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
SELECT @CurrentMaxTransferSize = CASE
WHEN @MaxTransferSize IS NOT NULL THEN @MaxTransferSize
WHEN @MaxTransferSize IS NULL AND @Compress = 'Y' AND @CurrentIsEncrypted = 1 AND @BackupSoftware IS NULL AND @Version >= 13.04446 THEN 65537
END
IF @CurrentDatabaseState = 'ONLINE'
BEGIN
IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = DB_ID(@CurrentDatabaseName) AND database_guid IS NOT NULL)
BEGIN
SET @CurrentIsDatabaseAccessible = 1
END
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0
END
END
IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name,
@CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc,
@CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc)
FROM sys.databases databases
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON databases.replica_id = dm_hadr_availability_replica_states.replica_id
INNER JOIN sys.availability_groups availability_groups ON dm_hadr_availability_replica_states.group_id = availability_groups.group_id
WHERE databases.name = @CurrentDatabaseName
END
IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1 AND @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
END
SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
FROM sys.master_files
WHERE database_id = DB_ID(@CurrentDatabaseName)
AND [type] = 0
AND [file_id] = 1
IF @CurrentDatabaseState = 'ONLINE'
BEGIN
SELECT @CurrentLogLSN = last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID(@CurrentDatabaseName)
END
IF @CurrentDatabaseState = 'ONLINE' AND EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count') AND (@CurrentAvailabilityGroupRole = 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL)
BEGIN
SET @CurrentCommand = 'SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM sys.dm_db_file_space_usage'
EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N'@ParamAllocatedExtentPageCount bigint OUTPUT, @ParamModifiedExtentPageCount bigint OUTPUT', @ParamAllocatedExtentPageCount = @CurrentAllocatedExtentPageCount OUTPUT, @ParamModifiedExtentPageCount = @CurrentModifiedExtentPageCount OUTPUT
END
SET @CurrentBackupType = @BackupType
IF @ChangeBackupType = 'Y'
BEGIN
IF @CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
BEGIN
SET @CurrentBackupType = 'DIFF'
END
IF @CurrentBackupType = 'DIFF' AND (@CurrentDatabaseName = 'master' OR @CurrentDifferentialBaseLSN IS NULL OR (@CurrentModifiedExtentPageCount * 1. / @CurrentAllocatedExtentPageCount * 100 >= @ModificationLevel))
BEGIN
SET @CurrentBackupType = 'FULL'
END
END
IF @CurrentDatabaseState = 'ONLINE' AND EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
SELECT @CurrentLastLogBackup = log_backup_time,
@CurrentLogSizeSinceLastLogBackup = log_since_last_log_backup_mb
FROM sys.dm_db_log_stats (DB_ID(@CurrentDatabaseName))
END
IF @CurrentBackupType = 'DIFF'
BEGIN
SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
FROM msdb.dbo.backupset
WHERE database_name = @CurrentDatabaseName
AND [type] = 'D'
AND checkpoint_lsn = @CurrentDifferentialBaseLSN
END
IF @ChangeBackupType = 'Y'
BEGIN
IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseIsSnapshot = 1
BEGIN
SET @CurrentBackupType = 'FULL'
END
END;
WITH CurrentDatabase AS
(
SELECT BackupSize = CASE WHEN @CurrentBackupType = 'FULL' THEN COALESCE(CAST(@CurrentAllocatedExtentPageCount AS bigint) * 8192, CAST(@CurrentDatabaseSize AS bigint) * 8192)
WHEN @CurrentBackupType = 'DIFF' THEN CAST(@CurrentModifiedExtentPageCount AS bigint) * 8192
WHEN @CurrentBackupType = 'LOG' THEN CAST(@CurrentLogSizeSinceLastLogBackup * 1024 * 1024 AS bigint)
END,
MaxNumberOfFiles = CASE WHEN @BackupSoftware IN('SQLBACKUP','DATA_DOMAIN_BOOST') THEN 32 ELSE 64 END,
CASE WHEN (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) > 0 THEN (SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) ELSE (SELECT COUNT(*) FROM @URLs WHERE Mirror = 0) END AS NumberOfDirectories,
CAST(@MinBackupSizeForMultipleFiles AS bigint) * 1024 * 1024 AS MinBackupSizeForMultipleFiles,
CAST(@MaxFileSize AS bigint) * 1024 * 1024 AS MaxFileSize
)
SELECT @CurrentNumberOfFiles = CASE WHEN @NumberOfFiles IS NULL AND @BackupSoftware = 'DATA_DOMAIN_BOOST' THEN 1
WHEN @NumberOfFiles IS NULL AND @MaxFileSize IS NULL THEN NumberOfDirectories
WHEN @NumberOfFiles = 1 THEN @NumberOfFiles
WHEN @NumberOfFiles > 1 AND (BackupSize >= MinBackupSizeForMultipleFiles OR MinBackupSizeForMultipleFiles IS NULL OR BackupSize IS NULL) THEN @NumberOfFiles
WHEN @NumberOfFiles > 1 AND (BackupSize < MinBackupSizeForMultipleFiles) THEN NumberOfDirectories
WHEN @NumberOfFiles IS NULL AND @MaxFileSize IS NOT NULL AND BackupSize IS NULL THEN NumberOfDirectories
WHEN @NumberOfFiles IS NULL AND @MaxFileSize IS NOT NULL THEN (SELECT MIN(NumberOfFilesInEachDirectory)
FROM (SELECT ((BackupSize / NumberOfDirectories) / MaxFileSize + CASE WHEN (BackupSize / NumberOfDirectories) % MaxFileSize = 0 THEN 0 ELSE 1 END) AS NumberOfFilesInEachDirectory
UNION
SELECT MaxNumberOfFiles / NumberOfDirectories) Files) * NumberOfDirectories
END
FROM CurrentDatabase
SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = DB_ID(@CurrentDatabaseName)
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = @CurrentDatabaseName)
BEGIN
SET @CurrentLogShippingRole = 'PRIMARY'
END
ELSE
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondary_databases WHERE secondary_database = @CurrentDatabaseName)
BEGIN
SET @CurrentLogShippingRole = 'SECONDARY'
END
IF @CurrentIsDatabaseAccessible IS NOT NULL
BEGIN
SET @DatabaseMessage = 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
IF @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SET @DatabaseMessage = 'Availability group: ' + ISNULL(@CurrentAvailabilityGroup,'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Availability group role: ' + ISNULL(@CurrentAvailabilityGroupRole,'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Availability group backup preference: ' + ISNULL(@CurrentAvailabilityGroupBackupPreference,'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Is preferred backup replica: ' + CASE WHEN @CurrentIsPreferredBackupReplica = 1 THEN 'Yes' WHEN @CurrentIsPreferredBackupReplica = 0 THEN 'No' ELSE 'N/A' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
IF @CurrentDatabaseMirroringRole IS NOT NULL
BEGIN
SET @DatabaseMessage = 'Database mirroring role: ' + @CurrentDatabaseMirroringRole
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
IF @CurrentLogShippingRole IS NOT NULL
BEGIN
SET @DatabaseMessage = 'Log shipping role: ' + @CurrentLogShippingRole
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
SET @DatabaseMessage = 'Differential base LSN: ' + ISNULL(CAST(@CurrentDifferentialBaseLSN AS nvarchar),'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
IF @CurrentBackupType = 'DIFF'
BEGIN
SET @DatabaseMessage = 'Differential base is snapshot: ' + CASE WHEN @CurrentDifferentialBaseIsSnapshot = 1 THEN 'Yes' WHEN @CurrentDifferentialBaseIsSnapshot = 0 THEN 'No' ELSE 'N/A' END
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
SET @DatabaseMessage = 'Last log backup LSN: ' + ISNULL(CAST(@CurrentLogLSN AS nvarchar),'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
IF @CurrentBackupType IN('DIFF','FULL') AND EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
BEGIN
SET @DatabaseMessage = 'Allocated extent page count: ' + ISNULL(CAST(@CurrentAllocatedExtentPageCount AS nvarchar) + ' (' + CAST(@CurrentAllocatedExtentPageCount * 1. * 8 / 1024 AS nvarchar) + ' MB)','N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Modified extent page count: ' + ISNULL(CAST(@CurrentModifiedExtentPageCount AS nvarchar) + ' (' + CAST(@CurrentModifiedExtentPageCount * 1. * 8 / 1024 AS nvarchar) + ' MB)','N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
IF @CurrentBackupType = 'LOG' AND EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_log_stats') AND name = 'log_since_last_log_backup_mb')
BEGIN
SET @DatabaseMessage = 'Last log backup: ' + ISNULL(CONVERT(nvarchar(19),NULLIF(@CurrentLastLogBackup,'1900-01-01'),120),'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
SET @DatabaseMessage = 'Log size since last log backup (MB): ' + ISNULL(CAST(@CurrentLogSizeSinceLastLogBackup AS nvarchar),'N/A')
RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT
END
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
IF @CurrentDatabaseState = 'ONLINE'
AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND NOT (@CurrentInStandby = 1)
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel = 'SIMPLE')
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL)
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG')
AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE')
AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY')
AND NOT (@CurrentBackupType = 'LOG' AND @LogSizeSinceLastLogBackup IS NOT NULL AND @TimeSinceLastLogBackup IS NOT NULL AND NOT(@CurrentLogSizeSinceLastLogBackup >= @LogSizeSinceLastLogBackup OR @CurrentLogSizeSinceLastLogBackup IS NULL OR DATEDIFF(SECOND,@CurrentLastLogBackup,SYSDATETIME()) >= @TimeSinceLastLogBackup OR @CurrentLastLogBackup IS NULL))
BEGIN
IF @CurrentBackupType = 'LOG' AND (@CleanupTime IS NOT NULL OR @MirrorCleanupTime IS NOT NULL)
BEGIN
SELECT @CurrentLatestBackup = MAX(backup_start_date)
FROM msdb.dbo.backupset
WHERE ([type] IN('D','I')
OR ([type] = 'L' AND last_lsn < @CurrentDifferentialBaseLSN))
AND is_damaged = 0
AND [database_name] = @CurrentDatabaseName
END
SET @CurrentDate = SYSDATETIME()
INSERT INTO @CurrentCleanupDates (CleanupDate)
SELECT @CurrentDate
IF @CurrentBackupType = 'LOG'
BEGIN
INSERT INTO @CurrentCleanupDates (CleanupDate)
SELECT @CurrentLatestBackup
END
SELECT @CurrentDirectoryStructure = CASE
WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @AvailabilityGroupDirectoryStructure
ELSE @DirectoryStructure
END
IF @CurrentDirectoryStructure IS NOT NULL
BEGIN
-- Directory structure - remove tokens that are not needed
IF @ReadWriteFileGroups = 'N' SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Partial}','')
IF @CopyOnly = 'N' SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{CopyOnly}','')
IF @Cluster IS NULL SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ClusterName}','')
IF @CurrentAvailabilityGroup IS NULL SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{AvailabilityGroupName}','')
IF SERVERPROPERTY('InstanceName') IS NULL SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{InstanceName}','')
IF @@SERVICENAME IS NULL SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ServiceName}','')
IF @Description IS NULL SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Description}','')
IF @Directory IS NULL AND @MirrorDirectory IS NULL AND @URL IS NULL AND @DefaultDirectory LIKE '%' + '.' + @@SERVICENAME + @DirectorySeparator + 'MSSQL' + @DirectorySeparator + 'Backup'
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ServerName}','')
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{InstanceName}','')
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ClusterName}','')
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{AvailabilityGroupName}','')
END
WHILE (@Updated = 1 OR @Updated IS NULL)
BEGIN
SET @Updated = 0
IF CHARINDEX('\',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'\','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('/',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'/','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('__',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'__','_')
SET @Updated = 1
END
IF CHARINDEX('--',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'--','-')
SET @Updated = 1
END
IF CHARINDEX('{DirectorySeparator}{DirectorySeparator}',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DirectorySeparator}{DirectorySeparator}','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('{DirectorySeparator}$',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DirectorySeparator}$','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('${DirectorySeparator}',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'${DirectorySeparator}','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('{DirectorySeparator}_',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DirectorySeparator}_','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('_{DirectorySeparator}',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'_{DirectorySeparator}','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('{DirectorySeparator}-',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DirectorySeparator}-','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('-{DirectorySeparator}',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'-{DirectorySeparator}','{DirectorySeparator}')
SET @Updated = 1
END
IF CHARINDEX('_$',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'_$','_')
SET @Updated = 1
END
IF CHARINDEX('$_',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'$_','_')
SET @Updated = 1
END
IF CHARINDEX('-$',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'-$','-')
SET @Updated = 1
END
IF CHARINDEX('$-',@CurrentDirectoryStructure) > 0
BEGIN
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'$-','-')
SET @Updated = 1
END
IF LEFT(@CurrentDirectoryStructure,1) = '_'
BEGIN
SET @CurrentDirectoryStructure = RIGHT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDirectoryStructure,1) = '_'
BEGIN
SET @CurrentDirectoryStructure = LEFT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF LEFT(@CurrentDirectoryStructure,1) = '-'
BEGIN
SET @CurrentDirectoryStructure = RIGHT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDirectoryStructure,1) = '-'
BEGIN
SET @CurrentDirectoryStructure = LEFT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF LEFT(@CurrentDirectoryStructure,1) = '$'
BEGIN
SET @CurrentDirectoryStructure = RIGHT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDirectoryStructure,1) = '$'
BEGIN
SET @CurrentDirectoryStructure = LEFT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 1)
SET @Updated = 1
END
IF LEFT(@CurrentDirectoryStructure,20) = '{DirectorySeparator}'
BEGIN
SET @CurrentDirectoryStructure = RIGHT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 20)
SET @Updated = 1
END
IF RIGHT(@CurrentDirectoryStructure,20) = '{DirectorySeparator}'
BEGIN
SET @CurrentDirectoryStructure = LEFT(@CurrentDirectoryStructure,LEN(@CurrentDirectoryStructure) - 20)
SET @Updated = 1
END
END
SET @Updated = NULL
-- Directory structure - replace tokens with real values
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DirectorySeparator}',@DirectorySeparator)
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ServerName}',CASE WHEN SERVERPROPERTY('EngineEdition') = 8 THEN LEFT(CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))) - 1) ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar(max)) END)
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{InstanceName}',ISNULL(CAST(SERVERPROPERTY('InstanceName') AS nvarchar(max)),''))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ServiceName}',ISNULL(@@SERVICENAME,''))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{ClusterName}',ISNULL(@Cluster,''))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{AvailabilityGroupName}',ISNULL(@CurrentAvailabilityGroup,''))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{DatabaseName}',@CurrentDatabaseNameFS)
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{BackupType}',@CurrentBackupType)
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Partial}','PARTIAL')
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{CopyOnly}','COPY_ONLY')
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Description}',LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@Description,''),'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''))))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Year}',CAST(DATEPART(YEAR,@CurrentDate) AS nvarchar))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Month}',RIGHT('0' + CAST(DATEPART(MONTH,@CurrentDate) AS nvarchar),2))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Day}',RIGHT('0' + CAST(DATEPART(DAY,@CurrentDate) AS nvarchar),2))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Hour}',RIGHT('0' + CAST(DATEPART(HOUR,@CurrentDate) AS nvarchar),2))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Minute}',RIGHT('0' + CAST(DATEPART(MINUTE,@CurrentDate) AS nvarchar),2))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Second}',RIGHT('0' + CAST(DATEPART(SECOND,@CurrentDate) AS nvarchar),2))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Millisecond}',RIGHT('00' + CAST(DATEPART(MILLISECOND,@CurrentDate) AS nvarchar),3))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{Microsecond}',RIGHT('00000' + CAST(DATEPART(MICROSECOND,@CurrentDate) AS nvarchar),6))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{MajorVersion}',ISNULL(CAST(SERVERPROPERTY('ProductMajorVersion') AS nvarchar),PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),4)))
SET @CurrentDirectoryStructure = REPLACE(@CurrentDirectoryStructure,'{MinorVersion}',ISNULL(CAST(SERVERPROPERTY('ProductMinorVersion') AS nvarchar),PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),3)))
END
INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
SELECT ROW_NUMBER() OVER (ORDER BY ID),
DirectoryPath + CASE WHEN DirectoryPath = 'NUL' THEN '' WHEN @CurrentDirectoryStructure IS NOT NULL THEN @DirectorySeparator + @CurrentDirectoryStructure ELSE '' END,
Mirror,
ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC),
0,
0
FROM @Directories
ORDER BY ID ASC
INSERT INTO @CurrentURLs (ID, DirectoryPath, Mirror, DirectoryNumber)
SELECT ROW_NUMBER() OVER (ORDER BY ID),
DirectoryPath + CASE WHEN @CurrentDirectoryStructure IS NOT NULL THEN @DirectorySeparator + @CurrentDirectoryStructure ELSE '' END,
Mirror,
ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC)
FROM @URLs
ORDER BY ID ASC
SELECT @CurrentDatabaseFileName = CASE
WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @AvailabilityGroupFileName
ELSE @FileName
END
-- File name - remove tokens that are not needed
IF @ReadWriteFileGroups = 'N' SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Partial}','')
IF @CopyOnly = 'N' SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{CopyOnly}','')
IF @Cluster IS NULL SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{ClusterName}','')
IF @CurrentAvailabilityGroup IS NULL SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{AvailabilityGroupName}','')
IF SERVERPROPERTY('InstanceName') IS NULL SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{InstanceName}','')
IF @@SERVICENAME IS NULL SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{ServiceName}','')
IF @Description IS NULL SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Description}','')
IF @CurrentNumberOfFiles = 1 SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{FileNumber}','')
IF @CurrentNumberOfFiles = 1 SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{NumberOfFiles}','')
WHILE (@Updated = 1 OR @Updated IS NULL)
BEGIN
SET @Updated = 0
IF CHARINDEX('__',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'__','_')
SET @Updated = 1
END
IF CHARINDEX('--',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'--','-')
SET @Updated = 1
END
IF CHARINDEX('_$',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'_$','_')
SET @Updated = 1
END
IF CHARINDEX('$_',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'$_','_')
SET @Updated = 1
END
IF CHARINDEX('-$',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'-$','-')
SET @Updated = 1
END
IF CHARINDEX('$-',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'$-','-')
SET @Updated = 1
END
IF CHARINDEX('_.',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'_.','.')
SET @Updated = 1
END
IF CHARINDEX('-.',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'-.','.')
SET @Updated = 1
END
IF CHARINDEX('of.',@CurrentDatabaseFileName) > 0
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'of.','.')
SET @Updated = 1
END
IF LEFT(@CurrentDatabaseFileName,1) = '_'
BEGIN
SET @CurrentDatabaseFileName = RIGHT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDatabaseFileName,1) = '_'
BEGIN
SET @CurrentDatabaseFileName = LEFT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
IF LEFT(@CurrentDatabaseFileName,1) = '-'
BEGIN
SET @CurrentDatabaseFileName = RIGHT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDatabaseFileName,1) = '-'
BEGIN
SET @CurrentDatabaseFileName = LEFT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
IF LEFT(@CurrentDatabaseFileName,1) = '$'
BEGIN
SET @CurrentDatabaseFileName = RIGHT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
IF RIGHT(@CurrentDatabaseFileName,1) = '$'
BEGIN
SET @CurrentDatabaseFileName = LEFT(@CurrentDatabaseFileName,LEN(@CurrentDatabaseFileName) - 1)
SET @Updated = 1
END
END
SET @Updated = NULL
SELECT @CurrentFileExtension = CASE
WHEN @CurrentBackupType = 'FULL' THEN @FileExtensionFull
WHEN @CurrentBackupType = 'DIFF' THEN @FileExtensionDiff
WHEN @CurrentBackupType = 'LOG' THEN @FileExtensionLog
END
-- File name - replace tokens with real values
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{ServerName}',CASE WHEN SERVERPROPERTY('EngineEdition') = 8 THEN LEFT(CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))) - 1) ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar(max)) END)
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{InstanceName}',ISNULL(CAST(SERVERPROPERTY('InstanceName') AS nvarchar(max)),''))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{ServiceName}',ISNULL(@@SERVICENAME,''))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{ClusterName}',ISNULL(@Cluster,''))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{AvailabilityGroupName}',ISNULL(@CurrentAvailabilityGroup,''))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{BackupType}',@CurrentBackupType)
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Partial}','PARTIAL')
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{CopyOnly}','COPY_ONLY')
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Description}',LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@Description,''),'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''))))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Year}',CAST(DATEPART(YEAR,@CurrentDate) AS nvarchar))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Month}',RIGHT('0' + CAST(DATEPART(MONTH,@CurrentDate) AS nvarchar),2))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Day}',RIGHT('0' + CAST(DATEPART(DAY,@CurrentDate) AS nvarchar),2))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Hour}',RIGHT('0' + CAST(DATEPART(HOUR,@CurrentDate) AS nvarchar),2))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Minute}',RIGHT('0' + CAST(DATEPART(MINUTE,@CurrentDate) AS nvarchar),2))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Second}',RIGHT('0' + CAST(DATEPART(SECOND,@CurrentDate) AS nvarchar),2))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Millisecond}',RIGHT('00' + CAST(DATEPART(MILLISECOND,@CurrentDate) AS nvarchar),3))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{Microsecond}',RIGHT('00000' + CAST(DATEPART(MICROSECOND,@CurrentDate) AS nvarchar),6))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{NumberOfFiles}',@CurrentNumberOfFiles)
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{FileExtension}',@CurrentFileExtension)
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{MajorVersion}',ISNULL(CAST(SERVERPROPERTY('ProductMajorVersion') AS nvarchar),PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),4)))
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{MinorVersion}',ISNULL(CAST(SERVERPROPERTY('ProductMinorVersion') AS nvarchar),PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),3)))
SELECT @CurrentMaxFilePathLength = CASE
WHEN EXISTS (SELECT * FROM @CurrentDirectories) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentDirectories)
WHEN EXISTS (SELECT * FROM @CurrentURLs) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentURLs)
END
+ LEN(REPLACE(REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS), '{FileNumber}', CASE WHEN @CurrentNumberOfFiles >= 1 AND @CurrentNumberOfFiles <= 9 THEN '1' WHEN @CurrentNumberOfFiles >= 10 THEN '01' END))
-- The maximum length of a backup device is 259 characters
IF @CurrentMaxFilePathLength > 259
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) END) + '...')
END
ELSE
BEGIN
SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS)
END
IF EXISTS (SELECT * FROM @CurrentDirectories WHERE Mirror = 0)
BEGIN
SET @CurrentFileNumber = 0
WHILE @CurrentFileNumber < @CurrentNumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1
SELECT @CurrentDirectoryPath = DirectoryPath
FROM @CurrentDirectories
WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 0) + 1
AND @CurrentFileNumber <= DirectoryNumber * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 0)
AND Mirror = 0
SET @CurrentFileName = REPLACE(@CurrentDatabaseFileName, '{FileNumber}', CASE WHEN @CurrentNumberOfFiles >= 1 AND @CurrentNumberOfFiles <= 9 THEN CAST(@CurrentFileNumber AS nvarchar) WHEN @CurrentNumberOfFiles >= 10 THEN RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) END)
IF @CurrentDirectoryPath = 'NUL'
BEGIN
SET @CurrentFilePath = 'NUL'
END
ELSE
BEGIN
SET @CurrentFilePath = @CurrentDirectoryPath + @DirectorySeparator + @CurrentFileName
END
INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
SELECT 'DISK', @CurrentFilePath, 0
SET @CurrentDirectoryPath = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
END
INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
SELECT 0, 0
END
IF EXISTS (SELECT * FROM @CurrentDirectories WHERE Mirror = 1)
BEGIN
SET @CurrentFileNumber = 0
WHILE @CurrentFileNumber < @CurrentNumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1
SELECT @CurrentDirectoryPath = DirectoryPath
FROM @CurrentDirectories
WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 1) + 1
AND @CurrentFileNumber <= DirectoryNumber * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentDirectories WHERE Mirror = 1)
AND Mirror = 1
SET @CurrentFileName = REPLACE(@CurrentDatabaseFileName, '{FileNumber}', CASE WHEN @CurrentNumberOfFiles > 1 AND @CurrentNumberOfFiles <= 9 THEN CAST(@CurrentFileNumber AS nvarchar) WHEN @CurrentNumberOfFiles >= 10 THEN RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END)
SET @CurrentFilePath = @CurrentDirectoryPath + @DirectorySeparator + @CurrentFileName
INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
SELECT 'DISK', @CurrentFilePath, 1
SET @CurrentDirectoryPath = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
END
INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
SELECT 1, 0
END
IF EXISTS (SELECT * FROM @CurrentURLs WHERE Mirror = 0)
BEGIN
SET @CurrentFileNumber = 0
WHILE @CurrentFileNumber < @CurrentNumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1
SELECT @CurrentDirectoryPath = DirectoryPath
FROM @CurrentURLs
WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentURLs WHERE Mirror = 0) + 1
AND @CurrentFileNumber <= DirectoryNumber * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentURLs WHERE Mirror = 0)
AND Mirror = 0
SET @CurrentFileName = REPLACE(@CurrentDatabaseFileName, '{FileNumber}', CASE WHEN @CurrentNumberOfFiles > 1 AND @CurrentNumberOfFiles <= 9 THEN CAST(@CurrentFileNumber AS nvarchar) WHEN @CurrentNumberOfFiles >= 10 THEN RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END)
SET @CurrentFilePath = @CurrentDirectoryPath + @DirectorySeparator + @CurrentFileName
INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
SELECT 'URL', @CurrentFilePath, 0
SET @CurrentDirectoryPath = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
END
INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
SELECT 0, 0
END
IF EXISTS (SELECT * FROM @CurrentURLs WHERE Mirror = 1)
BEGIN
SET @CurrentFileNumber = 0
WHILE @CurrentFileNumber < @CurrentNumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1
SELECT @CurrentDirectoryPath = DirectoryPath
FROM @CurrentURLs
WHERE @CurrentFileNumber >= (DirectoryNumber - 1) * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentURLs WHERE Mirror = 0) + 1
AND @CurrentFileNumber <= DirectoryNumber * (SELECT @CurrentNumberOfFiles / COUNT(*) FROM @CurrentURLs WHERE Mirror = 0)
AND Mirror = 1
SET @CurrentFileName = REPLACE(@CurrentDatabaseFileName, '{FileNumber}', CASE WHEN @CurrentNumberOfFiles > 1 AND @CurrentNumberOfFiles <= 9 THEN CAST(@CurrentFileNumber AS nvarchar) WHEN @CurrentNumberOfFiles >= 10 THEN RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END)
SET @CurrentFilePath = @CurrentDirectoryPath + @DirectorySeparator + @CurrentFileName
INSERT INTO @CurrentFiles ([Type], FilePath, Mirror)
SELECT 'URL', @CurrentFilePath, 1
SET @CurrentDirectoryPath = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
END
INSERT INTO @CurrentBackupSet (Mirror, VerifyCompleted)
SELECT 1, 0
END
-- Create directory
IF @HostPlatform = 'Windows'
AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
AND NOT EXISTS(SELECT * FROM @CurrentDirectories WHERE DirectoryPath = 'NUL')
BEGIN
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentDirectoryID = ID,
@CurrentDirectoryPath = DirectoryPath
FROM @CurrentDirectories
WHERE CreateCompleted = 0
ORDER BY ID ASC
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_create_subdir'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput
UPDATE @CurrentDirectories
SET CreateCompleted = 1,
CreateOutput = @CurrentCommandOutput
WHERE ID = @CurrentDirectoryID
SET @CurrentDirectoryID = NULL
SET @CurrentDirectoryPath = NULL
SET @CurrentDatabaseContext = NULL
SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
END
END
IF @CleanupMode = 'BEFORE_BACKUP'
BEGIN
INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
SELECT DATEADD(hh,-(@CleanupTime),SYSDATETIME()), 0
IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 0 OR Mirror IS NULL) AND CleanupDate IS NULL)
BEGIN
UPDATE @CurrentDirectories
SET CleanupDate = (SELECT MIN(CleanupDate)
FROM @CurrentCleanupDates
WHERE (Mirror = 0 OR Mirror IS NULL)),
CleanupMode = 'BEFORE_BACKUP'
WHERE Mirror = 0
END
END
IF @MirrorCleanupMode = 'BEFORE_BACKUP'
BEGIN
INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
SELECT DATEADD(hh,-(@MirrorCleanupTime),SYSDATETIME()), 1
IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 1 OR Mirror IS NULL) AND CleanupDate IS NULL)
BEGIN
UPDATE @CurrentDirectories
SET CleanupDate = (SELECT MIN(CleanupDate)
FROM @CurrentCleanupDates
WHERE (Mirror = 1 OR Mirror IS NULL)),
CleanupMode = 'BEFORE_BACKUP'
WHERE Mirror = 1
END
END
-- Delete old backup files, before backup
IF NOT EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateOutput <> 0 OR CreateOutput IS NULL)
AND @HostPlatform = 'Windows'
AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
AND @CurrentBackupType = @BackupType
BEGIN
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentDirectoryID = ID,
@CurrentDirectoryPath = DirectoryPath,
@CurrentCleanupDate = CleanupDate
FROM @CurrentDirectories
WHERE CleanupDate IS NOT NULL
AND CleanupMode = 'BEFORE_BACKUP'
AND CleanupCompleted = 0
ORDER BY ID ASC
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_delete_file'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_slssqlmaint'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectoryPath,'''','''''') + '" -DELEXTENSION "' + @CurrentFileExtension + '" -DELUNIT "' + CAST(DATEDIFF(mi,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + '" -DELUNITTYPE "minutes" -DELUSEAGE'' IF @ReturnCode <> 0 RAISERROR(''Error deleting LiteSpeed backup files.'', 16, 1)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'sqbutility'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.sqbutility 1032, N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''', N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + CASE WHEN @CurrentBackupType = 'FULL' THEN 'D' WHEN @CurrentBackupType = 'DIFF' THEN 'I' WHEN @CurrentBackupType = 'LOG' THEN 'L' END + ''', ''' + CAST(DATEDIFF(hh,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + 'h'', ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL') + ' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_ss_delete'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput
UPDATE @CurrentDirectories
SET CleanupCompleted = 1,
CleanupOutput = @CurrentCommandOutput
WHERE ID = @CurrentDirectoryID
SET @CurrentDirectoryID = NULL
SET @CurrentDirectoryPath = NULL
SET @CurrentCleanupDate = NULL
SET @CurrentDatabaseContext = NULL
SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
END
END
-- Perform a backup
IF NOT EXISTS (SELECT * FROM @CurrentDirectories WHERE DirectoryPath <> 'NUL' AND (CreateOutput <> 0 OR CreateOutput IS NULL)) OR @HostPlatform = 'Linux'
BEGIN
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentDatabaseContext = 'master'
SELECT @CurrentCommandType = CASE
WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP_DATABASE'
WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP_LOG'
END
SELECT @CurrentCommand = CASE
WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
END
IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand += ' READ_WRITE_FILEGROUPS'
SET @CurrentCommand += ' TO'
SELECT @CurrentCommand += ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = 0
ORDER BY FilePath ASC
IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
BEGIN
SET @CurrentCommand += ' MIRROR TO'
SELECT @CurrentCommand += ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = 1
ORDER BY FilePath ASC
END
SET @CurrentCommand += ' WITH '
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
IF @Version >= 10
BEGIN
SET @CurrentCommand += CASE WHEN @Compress = 'Y' AND (@CurrentIsEncrypted = 0 OR (@CurrentIsEncrypted = 1 AND @Version >= 13 AND @CurrentMaxTransferSize > 65536)) THEN ', COMPRESSION' ELSE ', NO_COMPRESSION' END
END
IF @CurrentBackupType = 'DIFF' SET @CurrentCommand += ', DIFFERENTIAL'
IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
BEGIN
SET @CurrentCommand += ', FORMAT'
END
IF @CopyOnly = 'Y' SET @CurrentCommand += ', COPY_ONLY'
IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand += ', NORECOVERY'
IF @Init = 'Y' SET @CurrentCommand += ', INIT'
IF @Format = 'Y' SET @CurrentCommand += ', FORMAT'
IF @BlockSize IS NOT NULL SET @CurrentCommand += ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
IF @BufferCount IS NOT NULL SET @CurrentCommand += ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)
IF @CurrentMaxTransferSize IS NOT NULL SET @CurrentCommand += ', MAXTRANSFERSIZE = ' + CAST(@CurrentMaxTransferSize AS nvarchar)
IF @Description IS NOT NULL SET @CurrentCommand += ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
IF @Encrypt = 'Y' SET @CurrentCommand += ', ENCRYPTION (ALGORITHM = ' + UPPER(@EncryptionAlgorithm) + ', '
IF @Encrypt = 'Y' AND @ServerCertificate IS NOT NULL SET @CurrentCommand += 'SERVER CERTIFICATE = ' + QUOTENAME(@ServerCertificate)
IF @Encrypt = 'Y' AND @ServerAsymmetricKey IS NOT NULL SET @CurrentCommand += 'SERVER ASYMMETRIC KEY = ' + QUOTENAME(@ServerAsymmetricKey)
IF @Encrypt = 'Y' SET @CurrentCommand += ')'
IF @URL IS NOT NULL AND @Credential IS NOT NULL SET @CurrentCommand += ', CREDENTIAL = N''' + REPLACE(@Credential,'''','''''') + ''''
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentDatabaseContext = 'master'
SELECT @CurrentCommandType = CASE
WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'xp_backup_database'
WHEN @CurrentBackupType = 'LOG' THEN 'xp_backup_log'
END
SELECT @CurrentCommand = CASE
WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_backup_database @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
WHEN @CurrentBackupType = 'LOG' THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_backup_log @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
END
SELECT @CurrentCommand += ', @filename = N''' + REPLACE(FilePath,'''','''''') + ''''
FROM @CurrentFiles
WHERE Mirror = 0
ORDER BY FilePath ASC
IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
BEGIN
SELECT @CurrentCommand += ', @mirror = N''' + REPLACE(FilePath,'''','''''') + ''''
FROM @CurrentFiles
WHERE Mirror = 1
ORDER BY FilePath ASC
END
SET @CurrentCommand += ', @with = '''
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
IF @CurrentBackupType = 'DIFF' SET @CurrentCommand += ', DIFFERENTIAL'
IF @CopyOnly = 'Y' SET @CurrentCommand += ', COPY_ONLY'
IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand += ', NORECOVERY'
IF @BlockSize IS NOT NULL SET @CurrentCommand += ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
SET @CurrentCommand += ''''
IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand += ', @read_write_filegroups = 1'
IF @CompressionLevel IS NOT NULL SET @CurrentCommand += ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)
IF @AdaptiveCompression IS NOT NULL SET @CurrentCommand += ', @adaptivecompression = ''' + CASE WHEN @AdaptiveCompression = 'SIZE' THEN 'Size' WHEN @AdaptiveCompression = 'SPEED' THEN 'Speed' END + ''''
IF @BufferCount IS NOT NULL SET @CurrentCommand += ', @buffercount = ' + CAST(@BufferCount AS nvarchar)
IF @CurrentMaxTransferSize IS NOT NULL SET @CurrentCommand += ', @maxtransfersize = ' + CAST(@CurrentMaxTransferSize AS nvarchar)
IF @Threads IS NOT NULL SET @CurrentCommand += ', @threads = ' + CAST(@Threads AS nvarchar)
IF @Init = 'Y' SET @CurrentCommand += ', @init = 1'
IF @Format = 'Y' SET @CurrentCommand += ', @format = 1'
IF @Throttle IS NOT NULL SET @CurrentCommand += ', @throttle = ' + CAST(@Throttle AS nvarchar)
IF @Description IS NOT NULL SET @CurrentCommand += ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
IF @ObjectLevelRecoveryMap = 'Y' SET @CurrentCommand += ', @olrmap = 1'
IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand += ', @cryptlevel = ' + CASE
WHEN @EncryptionAlgorithm = 'RC2_40' THEN '0'
WHEN @EncryptionAlgorithm = 'RC2_56' THEN '1'
WHEN @EncryptionAlgorithm = 'RC2_112' THEN '2'
WHEN @EncryptionAlgorithm = 'RC2_128' THEN '3'
WHEN @EncryptionAlgorithm = 'TRIPLE_DES_3KEY' THEN '4'
WHEN @EncryptionAlgorithm = 'RC4_128' THEN '5'
WHEN @EncryptionAlgorithm = 'AES_128' THEN '6'
WHEN @EncryptionAlgorithm = 'AES_192' THEN '7'
WHEN @EncryptionAlgorithm = 'AES_256' THEN '8'
END
IF @EncryptionKey IS NOT NULL SET @CurrentCommand += ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
SET @CurrentCommand += ' IF @ReturnCode <> 0 RAISERROR(''Error performing LiteSpeed backup.'', 16, 1)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'sqlbackup'
SELECT @CurrentCommand = CASE
WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
END
IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand += ' READ_WRITE_FILEGROUPS'
SET @CurrentCommand += ' TO'
SELECT @CurrentCommand += ' DISK = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = 0
ORDER BY FilePath ASC
SET @CurrentCommand += ' WITH '
IF EXISTS(SELECT * FROM @CurrentFiles WHERE Mirror = 1)
BEGIN
SET @CurrentCommand += ' MIRRORFILE' + ' = N''' + REPLACE((SELECT FilePath FROM @CurrentFiles WHERE Mirror = 1),'''','''''') + ''', '
END
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
IF @CurrentBackupType = 'DIFF' SET @CurrentCommand += ', DIFFERENTIAL'
IF @CopyOnly = 'Y' SET @CurrentCommand += ', COPY_ONLY'
IF @NoRecovery = 'Y' AND @CurrentBackupType = 'LOG' SET @CurrentCommand += ', NORECOVERY'
IF @Init = 'Y' SET @CurrentCommand += ', INIT'
IF @Format = 'Y' SET @CurrentCommand += ', FORMAT'
IF @CompressionLevel IS NOT NULL SET @CurrentCommand += ', COMPRESSION = ' + CAST(@CompressionLevel AS nvarchar)
IF @Threads IS NOT NULL SET @CurrentCommand += ', THREADCOUNT = ' + CAST(@Threads AS nvarchar)
IF @CurrentMaxTransferSize IS NOT NULL SET @CurrentCommand += ', MAXTRANSFERSIZE = ' + CAST(@CurrentMaxTransferSize AS nvarchar)
IF @Description IS NOT NULL SET @CurrentCommand += ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand += ', KEYSIZE = ' + CASE
WHEN @EncryptionAlgorithm = 'AES_128' THEN '128'
WHEN @EncryptionAlgorithm = 'AES_256' THEN '256'
END
IF @EncryptionKey IS NOT NULL SET @CurrentCommand += ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLBackup backup.'', 16, 1)'
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_ss_backup'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_ss_backup @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
SELECT @CurrentCommand += ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(FilePath,'''','''''') + ''''
FROM @CurrentFiles
WHERE Mirror = 0
ORDER BY FilePath ASC
SELECT @CurrentCommand += ', @mirrorfile = N''' + REPLACE(FilePath,'''','''''') + ''''
FROM @CurrentFiles
WHERE Mirror = 1
ORDER BY FilePath ASC
SET @CurrentCommand += ', @backuptype = ' + CASE WHEN @CurrentBackupType = 'FULL' THEN '''Full''' WHEN @CurrentBackupType = 'DIFF' THEN '''Differential''' WHEN @CurrentBackupType = 'LOG' THEN '''Log''' END
IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand += ', @readwritefilegroups = 1'
SET @CurrentCommand += ', @checksum = ' + CASE WHEN @CheckSum = 'Y' THEN '1' WHEN @CheckSum = 'N' THEN '0' END
SET @CurrentCommand += ', @copyonly = ' + CASE WHEN @CopyOnly = 'Y' THEN '1' WHEN @CopyOnly = 'N' THEN '0' END
IF @CompressionLevel IS NOT NULL SET @CurrentCommand += ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)
IF @Threads IS NOT NULL SET @CurrentCommand += ', @threads = ' + CAST(@Threads AS nvarchar)
IF @Init = 'Y' SET @CurrentCommand += ', @overwrite = 1'
IF @Description IS NOT NULL SET @CurrentCommand += ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
IF @EncryptionAlgorithm IS NOT NULL SET @CurrentCommand += ', @encryptiontype = N''' + CASE
WHEN @EncryptionAlgorithm = 'AES_128' THEN 'AES128'
WHEN @EncryptionAlgorithm = 'AES_256' THEN 'AES256'
END + ''''
IF @EncryptionKey IS NOT NULL SET @CurrentCommand += ', @encryptedbackuppassword = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
SET @CurrentCommand += ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLsafe backup.'', 16, 1)'
END
IF @BackupSoftware = 'DATA_DOMAIN_BOOST'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'emc_run_backup'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.emc_run_backup '''
SET @CurrentCommand += ' -c ' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar) END
SET @CurrentCommand += ' -l ' + CASE
WHEN @CurrentBackupType = 'FULL' THEN 'full'
WHEN @CurrentBackupType = 'DIFF' THEN 'diff'
WHEN @CurrentBackupType = 'LOG' THEN 'incr'
END
IF @NoRecovery = 'Y' SET @CurrentCommand += ' -H'
IF @CleanupTime IS NOT NULL SET @CurrentCommand += ' -y +' + CAST(@CleanupTime/24 + CASE WHEN @CleanupTime%24 > 0 THEN 1 ELSE 0 END AS nvarchar) + 'd'
IF @CheckSum = 'Y' SET @CurrentCommand += ' -k'
SET @CurrentCommand += ' -S ' + CAST(@CurrentNumberOfFiles AS nvarchar)
IF @Description IS NOT NULL SET @CurrentCommand += ' -b "' + REPLACE(@Description,'''','''''') + '"'
IF @BufferCount IS NOT NULL SET @CurrentCommand += ' -O "BUFFERCOUNT=' + CAST(@BufferCount AS nvarchar) + '"'
IF @ReadWriteFileGroups = 'Y' AND @CurrentDatabaseName <> 'master' SET @CurrentCommand += ' -O "READ_WRITE_FILEGROUPS"'
IF @DataDomainBoostHost IS NOT NULL SET @CurrentCommand += ' -a "NSR_DFA_SI_DD_HOST=' + REPLACE(@DataDomainBoostHost,'''','''''') + '"'
IF @DataDomainBoostUser IS NOT NULL SET @CurrentCommand += ' -a "NSR_DFA_SI_DD_USER=' + REPLACE(@DataDomainBoostUser,'''','''''') + '"'
IF @DataDomainBoostDevicePath IS NOT NULL SET @CurrentCommand += ' -a "NSR_DFA_SI_DEVICE_PATH=' + REPLACE(@DataDomainBoostDevicePath,'''','''''') + '"'
IF @DataDomainBoostLockboxPath IS NOT NULL SET @CurrentCommand += ' -a "NSR_DFA_SI_DD_LOCKBOX_PATH=' + REPLACE(@DataDomainBoostLockboxPath,'''','''''') + '"'
SET @CurrentCommand += ' -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"'
IF @CopyOnly = 'Y' SET @CurrentCommand += ' -a "NSR_COPY_ONLY=TRUE"'
IF SERVERPROPERTY('InstanceName') IS NULL SET @CurrentCommand += ' "MSSQL' + ':' + REPLACE(REPLACE(@CurrentDatabaseName,'''',''''''),'.','\.') + '"'
IF SERVERPROPERTY('InstanceName') IS NOT NULL SET @CurrentCommand += ' "MSSQL$' + CAST(SERVERPROPERTY('InstanceName') AS nvarchar) + ':' + REPLACE(REPLACE(@CurrentDatabaseName,'''',''''''),'.','\.') + '"'
SET @CurrentCommand += ''''
SET @CurrentCommand += ' IF @ReturnCode <> 0 RAISERROR(''Error performing Data Domain Boost backup.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput
SET @CurrentBackupOutput = @CurrentCommandOutput
END
-- Verify the backup
IF @CurrentBackupOutput = 0 AND @Verify = 'Y'
BEGIN
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentBackupSetID = ID,
@CurrentIsMirror = Mirror
FROM @CurrentBackupSet
WHERE VerifyCompleted = 0
ORDER BY ID ASC
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'RESTORE_VERIFYONLY'
SET @CurrentCommand = 'RESTORE VERIFYONLY FROM'
SELECT @CurrentCommand += ' ' + [Type] + ' = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = @CurrentIsMirror
ORDER BY FilePath ASC
SET @CurrentCommand += ' WITH '
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
IF @URL IS NOT NULL AND @Credential IS NOT NULL SET @CurrentCommand += ', CREDENTIAL = N''' + REPLACE(@Credential,'''','''''') + ''''
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_restore_verifyonly'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_restore_verifyonly'
SELECT @CurrentCommand += ' @filename = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = @CurrentIsMirror
ORDER BY FilePath ASC
SET @CurrentCommand += ', @with = '''
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
SET @CurrentCommand += ''''
IF @EncryptionKey IS NOT NULL SET @CurrentCommand += ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
SET @CurrentCommand += ' IF @ReturnCode <> 0 RAISERROR(''Error verifying LiteSpeed backup.'', 16, 1)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'sqlbackup'
SET @CurrentCommand = 'RESTORE VERIFYONLY FROM'
SELECT @CurrentCommand += ' DISK = N''' + REPLACE(FilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) <> @CurrentNumberOfFiles THEN ',' ELSE '' END
FROM @CurrentFiles
WHERE Mirror = @CurrentIsMirror
ORDER BY FilePath ASC
SET @CurrentCommand += ' WITH '
IF @CheckSum = 'Y' SET @CurrentCommand += 'CHECKSUM'
IF @CheckSum = 'N' SET @CurrentCommand += 'NO_CHECKSUM'
IF @EncryptionKey IS NOT NULL SET @CurrentCommand += ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLBackup backup.'', 16, 1)'
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_ss_verify'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_ss_verify @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
SELECT @CurrentCommand += ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY FilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(FilePath,'''','''''') + ''''
FROM @CurrentFiles
WHERE Mirror = @CurrentIsMirror
ORDER BY FilePath ASC
SET @CurrentCommand += ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLsafe backup.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput
UPDATE @CurrentBackupSet
SET VerifyCompleted = 1,
VerifyOutput = @CurrentCommandOutput
WHERE ID = @CurrentBackupSetID
SET @CurrentBackupSetID = NULL
SET @CurrentIsMirror = NULL
SET @CurrentDatabaseContext = NULL
SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
END
END
IF @CleanupMode = 'AFTER_BACKUP'
BEGIN
INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
SELECT DATEADD(hh,-(@CleanupTime),SYSDATETIME()), 0
IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 0 OR Mirror IS NULL) AND CleanupDate IS NULL)
BEGIN
UPDATE @CurrentDirectories
SET CleanupDate = (SELECT MIN(CleanupDate)
FROM @CurrentCleanupDates
WHERE (Mirror = 0 OR Mirror IS NULL)),
CleanupMode = 'AFTER_BACKUP'
WHERE Mirror = 0
END
END
IF @MirrorCleanupMode = 'AFTER_BACKUP'
BEGIN
INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror)
SELECT DATEADD(hh,-(@MirrorCleanupTime),SYSDATETIME()), 1
IF NOT EXISTS(SELECT * FROM @CurrentCleanupDates WHERE (Mirror = 1 OR Mirror IS NULL) AND CleanupDate IS NULL)
BEGIN
UPDATE @CurrentDirectories
SET CleanupDate = (SELECT MIN(CleanupDate)
FROM @CurrentCleanupDates
WHERE (Mirror = 1 OR Mirror IS NULL)),
CleanupMode = 'AFTER_BACKUP'
WHERE Mirror = 1
END
END
-- Delete old backup files, after backup
IF ((@CurrentBackupOutput = 0 AND @Verify = 'N')
OR (@CurrentBackupOutput = 0 AND @Verify = 'Y' AND NOT EXISTS (SELECT * FROM @CurrentBackupSet WHERE VerifyOutput <> 0 OR VerifyOutput IS NULL)))
AND @HostPlatform = 'Windows'
AND (@BackupSoftware <> 'DATA_DOMAIN_BOOST' OR @BackupSoftware IS NULL)
AND @CurrentBackupType = @BackupType
BEGIN
WHILE (1 = 1)
BEGIN
SELECT TOP 1 @CurrentDirectoryID = ID,
@CurrentDirectoryPath = DirectoryPath,
@CurrentCleanupDate = CleanupDate
FROM @CurrentDirectories
WHERE CleanupDate IS NOT NULL
AND CleanupMode = 'AFTER_BACKUP'
AND CleanupCompleted = 0
ORDER BY ID ASC
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
IF @BackupSoftware IS NULL
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_delete_file'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + @CurrentFileExtension + ''', ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
END
IF @BackupSoftware = 'LITESPEED'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_slssqlmaint'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectoryPath,'''','''''') + '" -DELEXTENSION "' + @CurrentFileExtension + '" -DELUNIT "' + CAST(DATEDIFF(mi,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + '" -DELUNITTYPE "minutes" -DELUSEAGE'' IF @ReturnCode <> 0 RAISERROR(''Error deleting LiteSpeed backup files.'', 16, 1)'
END
IF @BackupSoftware = 'SQLBACKUP'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'sqbutility'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.sqbutility 1032, N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''', N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', ''' + CASE WHEN @CurrentBackupType = 'FULL' THEN 'D' WHEN @CurrentBackupType = 'DIFF' THEN 'I' WHEN @CurrentBackupType = 'LOG' THEN 'L' END + ''', ''' + CAST(DATEDIFF(hh,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + 'h'', ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL') + ' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
END
IF @BackupSoftware = 'SQLSAFE'
BEGIN
SET @CurrentDatabaseContext = 'master'
SET @CurrentCommandType = 'xp_ss_delete'
SET @CurrentCommand = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,SYSDATETIME()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
END
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput
UPDATE @CurrentDirectories
SET CleanupCompleted = 1,
CleanupOutput = @CurrentCommandOutput
WHERE ID = @CurrentDirectoryID
SET @CurrentDirectoryID = NULL
SET @CurrentDirectoryPath = NULL
SET @CurrentCleanupDate = NULL
SET @CurrentDatabaseContext = NULL
SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
END
END
END
IF @CurrentDatabaseState = 'SUSPECT'
BEGIN
SET @ErrorMessage = 'The database ' + QUOTENAME(@CurrentDatabaseName) + ' is in a SUSPECT state.'
RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT
SET @Error = @@ERROR
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
END
-- Update that the database is completed
IF @DatabasesInParallel = 'Y'
BEGIN
UPDATE dbo.QueueDatabase
SET DatabaseEndTime = SYSDATETIME()
WHERE QueueID = @QueueID
AND DatabaseName = @CurrentDatabaseName
END
ELSE
BEGIN
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID
END
-- Clear variables
SET @CurrentDBID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentDatabase_sp_executesql = NULL
SET @CurrentUserAccess = NULL
SET @CurrentIsReadOnly = NULL
SET @CurrentDatabaseState = NULL
SET @CurrentInStandby = NULL
SET @CurrentRecoveryModel = NULL
SET @CurrentIsEncrypted = NULL
SET @CurrentDatabaseSize = NULL
SET @CurrentBackupType = NULL
SET @CurrentMaxTransferSize = NULL
SET @CurrentNumberOfFiles = NULL
SET @CurrentFileExtension = NULL
SET @CurrentFileNumber = NULL
SET @CurrentDifferentialBaseLSN = NULL
SET @CurrentDifferentialBaseIsSnapshot = NULL
SET @CurrentLogLSN = NULL
SET @CurrentLatestBackup = NULL
SET @CurrentDatabaseNameFS = NULL
SET @CurrentDirectoryStructure = NULL
SET @CurrentDatabaseFileName = NULL
SET @CurrentMaxFilePathLength = NULL
SET @CurrentDate = NULL
SET @CurrentCleanupDate = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentAvailabilityGroup = NULL
SET @CurrentAvailabilityGroupRole = NULL
SET @CurrentAvailabilityGroupBackupPreference = NULL
SET @CurrentIsPreferredBackupReplica = NULL
SET @CurrentDatabaseMirroringRole = NULL
SET @CurrentLogShippingRole = NULL
SET @CurrentLastLogBackup = NULL
SET @CurrentLogSizeSinceLastLogBackup = NULL
SET @CurrentAllocatedExtentPageCount = NULL
SET @CurrentModifiedExtentPageCount = NULL
SET @CurrentDatabaseContext = NULL
SET @CurrentCommand = NULL
SET @CurrentCommandOutput = NULL
SET @CurrentCommandType = NULL
SET @CurrentBackupOutput = NULL
DELETE FROM @CurrentDirectories
DELETE FROM @CurrentURLs
DELETE FROM @CurrentFiles
DELETE FROM @CurrentCleanupDates
DELETE FROM @CurrentBackupSet
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120)
RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT
RAISERROR(@EmptyLine,10,1) WITH NOWAIT
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END
----------------------------------------------------------------------------------------------------
END
GO
Comments