none
BACKUP LOG suddenly failed with Msg 35250, Level 16, State 11 The connection to the primary replica is not active. The command cannot be processed.

    Question

  • I have AlwaysOn SQL 2012 Enterprise set-up using Windows Failover Clustering Services (not FCI), and have 1 Primary node (P), 1 Synchronous Commit Auto Failover (SC), and 1 Asynchronous Commit Manual Failover (AC) node.  It is set up to prefer secondary, with the highest priority given to AC node.

    I am using Ola Hollengren's scripts for Database Maintenance jobs, including a native BACKUP LOG job for the transaction logs of all user databases on a 1 minute schedule.  His scripts already consider AlwaysOn, and although the job is set-up on all 3 nodes, only ever runs on AC node.

    The job has been running successfully since initial set-up almost 1 year ago, but suddenly yesterday morning started to fail with the following error, only on 1 of the 13 databases in my availability group:

    Date and time: 2014-06-08 09:36:11
    Command: BACKUP LOG [my_db] TO DISK = N'E:\MSSQL\\Transaction Dumps\my_db\MySQLCL$MySQLAG_my_db_20140608_093610_U_LOG.trn' WITH CHECKSUM, COMPRESSION
    Msg 35250, Level 16, State 11, Server AC, Line 1
    The connection to the primary replica is not active.  The command cannot be processed.
    Msg 3013, Level 16, State 1, Server AC, Line 1
    BACKUP LOG is terminating abnormally.
    Outcome: Failed
    Duration: 00:01:00

    The other 12 databases continued to backup successfully.

    Checking the Availability Group dashboard, windows event logs, and SQL Server error logs, including Failover Cluster events showed no issues.

    However, monitoring software (Idera SQLdm) showed blocked sessions on P node.  When I ran sp_who2, it showed that a background process was being blocked by another background process with an HADR BACKUP LOCK.

    Since both processes were background processes, I was unable to kill either process.  I temporarily disabled the transaction log backup job, but the blocked process was still active.

    I ran DBCC CHECKDB (my_db) WITH all_errormsgs, no_infomsgs, data_purity on both P and AC nodes, with no errors.  However, on AC node, it also showed 1 transaction rolled forward and 0 transactions rolled back.  This also had the effect of releasing the blocked background process, but another background process was now blocking with the same HADR BACKUP LOCK.

    I tried to restart SQL Server Agent on AC node, which did not immediately seem to work.  However, after a few minutes, I noticed that the block had disappeared.  I re-enabled the transaction log backup job on AC and it started working normally again.  The error has not occurred again, but I am at a loss as to what happened, and how to prevent it from happening again.

    Any help would be greatly appreciated.


    Diane

    Monday, June 09, 2014 7:12 PM

All replies

  • Hi Dinane Sithoo,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, June 11, 2014 8:54 AM
    Moderator
  • Thanks Sofiya for keeping me updated!  I look forward to any response.

    Diane

    Wednesday, June 11, 2014 3:44 PM
  • Hi Dinane Sithoo,

    I'm an SQL engineer from Microsoft. After reading your post, I have two comments:

    1, is this "HADR BACKUP LOCK" the correct lock name, or is it the "HADR BACKUP BULK LOCK"?

    2, if the error is reported when the primary database received a backup request, there should be a failure for backing up on the secondary (because prefer secondary).

    Best regards,

    Thursday, June 12, 2014 3:33 AM
  • Thanks again for your response. To answer your questions: 1. It showed in sp_who2 as HADR BACKUP LOCK, not as HADR BACKUP BULK LOCK. 2. Yes, the error occurred on the secondary AC node. The block was on the primary P node.

    Diane

    Thursday, June 12, 2014 4:04 AM
  • Hi Diane

    which column of sp_who2 have you found "HADR BACKUP LOCK." in?

    Can you determine the wait_resource now?

    Best regards,

    Thursday, June 12, 2014 9:53 AM
  • I believe it was the "Command" column.  Unfortunately (or maybe fortunately), the error has not occurred again for me to be able to see the wait_resource, and monitoring when the job runs shows no locks anymore.

    Diane

    Thursday, June 12, 2014 4:32 PM
  • Hi Diane,

    Could you please post your backup script for more investigation?

    Friday, June 13, 2014 5:37 AM
  • Was there any connectivity issues in your environment during that specific time?

    Please validate that from your internal network team on this.

    Also is this a frequent issues or occasional one.


    Santosh Singh

    Friday, June 13, 2014 7:33 PM
  • Hi, since the issue was gone, maybe we cannot find a "real" root cause any more. 

    Here, I only want to list my steps to do if I encounter this issue. 

    Since there is a blocking occurred, we can use 

    DBCC inputbuffer()

    to check the detail codes executed with each sessions, and determine why they are blocked, and then stop log backup, wait for several minutes and check whether the blocking is gone. Sometimes, it need sometimes to rollback the transaction or roll forward the transactions.

    From the error message:

    The connection to the primary replica is not active.  The command cannot be processed.

    It indicates the connection between the primary DB and the secondary DB is wrong. You said you checked Alwayson Group dashboard and there is no error message, there is one column named "Connection State", I think I will check and make sure it's "Connected".

    Last thing, want to confirm why do you prefer to backup log on an asynchronous replica rather than a sync one.


    彼节者有间,而刀刃者无厚;以无厚入有间,恢恢乎其于游刃必有余地矣!

    Monday, June 16, 2014 9:33 AM
  • Just using Ola Hollengren's script for database backup (last revised April 30, 2013), with a few minor alterations to allow for folder and file name changes.

    Here is the job:

    USE [msdb]
    GO
    
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MaintJob: DatabaseBackup - USER_DATABASES - LOG', 
    		@enabled=1, 
    		@notify_level_eventlog=2, 
    		@notify_level_email=2, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'Source: http://ola.hallengren.com', 
    		@category_name=N'Database Maintenance', 
    		@owner_login_name=N'MyDomain\MySQLService', 
    		@notify_email_operator_name=N'MyEmailGroup', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DatabaseBackup - USER_DATABASES - LOG', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'CmdExec', 
    		@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''E:\MSSQL'', @BackupType = ''LOG'', @Verify = ''Y'', @CleanupTime = 72, @Compress = ''Y'', @CheckSum = ''Y'', @LogToTable = ''Y''" -b', 
    		@output_file_name=N'C:\MSSQL\SQLJobOutput\Transaction Dumps\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DatabaseBackup - USER_DATABASES - LOG Schedule', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=4, 
    		@freq_subday_interval=1, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20130715, 
    		@active_end_date=99991231, 
    		@active_start_time=0, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    GO


    Diane

    Monday, June 16, 2014 6:50 PM
  • Here is the underlying CommandLog table:

    USE [master]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[CommandLog](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[DatabaseName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[SchemaName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[ObjectName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[ObjectType] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[IndexName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[IndexType] [tinyint] NULL,
    	[StatisticsName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[PartitionNumber] [int] NULL,
    	[ExtendedInfo] [xml] NULL,
    	[Command] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[CommandType] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[StartTime] [datetime] NOT NULL,
    	[EndTime] [datetime] NULL,
    	[ErrorNumber] [int] NULL,
    	[ErrorMessage] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER AUTHORIZATION ON [dbo].[CommandLog] TO  SCHEMA OWNER 
    GO


    Diane


    Monday, June 16, 2014 6:56 PM
  • Here is the underlying CommandExecute stored procedure:

    USE [master]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[CommandExecute]
    
    @Command nvarchar(max),
    @CommandType nvarchar(max),
    @Mode int,
    @Comment nvarchar(max) = NULL,
    @DatabaseName nvarchar(max) = NULL,
    @SchemaName nvarchar(max) = NULL,
    @ObjectName nvarchar(max) = NULL,
    @ObjectType nvarchar(max) = NULL,
    @IndexName nvarchar(max) = NULL,
    @IndexType int = NULL,
    @StatisticsName nvarchar(max) = NULL,
    @PartitionNumber int = NULL,
    @ExtendedInfo xml = NULL,
    @LogToTable nvarchar(max),
    @Execute nvarchar(max)
    
    AS
    
    BEGIN
    
      ----------------------------------------------------------------------------------------------------
      --// Source: http://ola.hallengren.com                                                          //--
      ----------------------------------------------------------------------------------------------------
    
      SET NOCOUNT ON
    
      DECLARE @StartMessage nvarchar(max)
      DECLARE @EndMessage nvarchar(max)
      DECLARE @ErrorMessage nvarchar(max)
      DECLARE @ErrorMessageOriginal nvarchar(max)
    
      DECLARE @StartTime datetime
      DECLARE @EndTime datetime
    
      DECLARE @StartTimeSec datetime
      DECLARE @EndTimeSec datetime
    
      DECLARE @ID int
    
      DECLARE @Error int
      DECLARE @ReturnCode int
    
      SET @Error = 0
      SET @ReturnCode = 0
    
      ----------------------------------------------------------------------------------------------------
      --// Check core requirements                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
      BEGIN
        SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Error <> 0
      BEGIN
        SET @ReturnCode = @Error
        GOTO ReturnCode
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Check input parameters                                                                     //--
      ----------------------------------------------------------------------------------------------------
    
      IF @Command IS NULL OR @Command = ''
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Mode NOT IN(1,2) OR @Mode IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Execute NOT IN('Y','N') OR @Execute IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Error <> 0
      BEGIN
        SET @ReturnCode = @Error
        GOTO ReturnCode
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Log initial information                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      SET @StartTime = GETDATE()
      SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)
    
      SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Command: ' + @Command
      IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment
      SET @StartMessage = REPLACE(@StartMessage,'%','%%')
      RAISERROR(@StartMessage,10,1) WITH NOWAIT
    
      IF @LogToTable = 'Y'
      BEGIN
        INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
        VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
      END
    
      SET @ID = SCOPE_IDENTITY()
    
      ----------------------------------------------------------------------------------------------------
      --// Execute command                                                                            //--
      ----------------------------------------------------------------------------------------------------
    
      IF @Mode = 1 AND @Execute = 'Y'
      BEGIN
        EXECUTE(@Command)
        SET @Error = @@ERROR
        SET @ReturnCode = @Error
      END
    
      IF @Mode = 2 AND @Execute = 'Y'
      BEGIN
        BEGIN TRY
          EXECUTE(@Command)
        END TRY
        BEGIN CATCH
          SET @Error = ERROR_NUMBER()
          SET @ReturnCode = @Error
          SET @ErrorMessageOriginal = ERROR_MESSAGE()
          SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
          RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        END CATCH
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Log completing information                                                                 //--
      ----------------------------------------------------------------------------------------------------
    
      SET @EndTime = GETDATE()
      SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)
    
      SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)
      SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)
      SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '
      SET @EndMessage = REPLACE(@EndMessage,'%','%%')
      RAISERROR(@EndMessage,10,1) WITH NOWAIT
    
      IF @LogToTable = 'Y'
      BEGIN
        UPDATE dbo.CommandLog
        SET EndTime = @EndTime,
            ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,
            ErrorMessage = @ErrorMessageOriginal
        WHERE ID = @ID
      END
    
      ReturnCode:
      IF @ReturnCode <> 0
      BEGIN
        RETURN @ReturnCode
      END
    
      ----------------------------------------------------------------------------------------------------
    
    END
    
    GO
    
    ALTER AUTHORIZATION ON [dbo].[CommandExecute] TO  SCHEMA OWNER 
    GO
    
    
    
    

    stored procedure:


    Diane


    Monday, June 16, 2014 7:00 PM
  • Here is part 1 of the DatabaseBackup stored procedure:

    USE [master]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[DatabaseBackup]
    
    @Databases nvarchar(max),
    @Directory nvarchar(max) = NULL,
    @MirrorDirectory nvarchar(4000) = NULL,
    @BackupType nvarchar(max),
    @Verify nvarchar(max) = 'N',
    @CleanupTime int = NULL,
    @Compress nvarchar(max) = NULL,
    @CopyOnly nvarchar(max) = 'N',
    @ChangeBackupType nvarchar(max) = 'N',
    @BackupSoftware nvarchar(max) = NULL,
    @CheckSum nvarchar(max) = 'N',
    @BlockSize int = NULL,
    @BufferCount int = NULL,
    @MaxTransferSize int = NULL,
    @NumberOfFiles int = NULL,
    @CompressionLevel int = NULL,
    @Description nvarchar(max) = NULL,
    @Threads int = NULL,
    @Throttle int = NULL,
    @Encrypt nvarchar(max) = 'N',
    @EncryptionType nvarchar(max) = NULL,
    @EncryptionKey nvarchar(max) = NULL,
    @ReadWriteFileGroups nvarchar(max) = 'N',
    @RetryWrites nvarchar(max) = NULL,
    @LogToTable nvarchar(max) = 'N',
    @Execute nvarchar(max) = 'Y'
    
    AS
    
    BEGIN
    
      ----------------------------------------------------------------------------------------------------
      --// Source: http://ola.hallengren.com                                                          //--
      ----------------------------------------------------------------------------------------------------
    
      SET NOCOUNT ON
    
      DECLARE @StartMessage nvarchar(max)
      DECLARE @EndMessage nvarchar(max)
      DECLARE @DatabaseMessage nvarchar(max)
      DECLARE @ErrorMessage nvarchar(max)
    
      DECLARE @Version numeric(18,10)
    
      DECLARE @Cluster nvarchar(max)
    
      DECLARE @DefaultDirectory nvarchar(4000)
    
      DECLARE @CurrentRootDirectoryID int
      DECLARE @CurrentRootDirectoryPath nvarchar(4000)
    
      DECLARE @CurrentDBID int
      DECLARE @CurrentDatabaseID int
      DECLARE @CurrentDatabaseName nvarchar(max)
      DECLARE @CurrentBackupType nvarchar(max)
      DECLARE @CurrentFileExtension nvarchar(max)
      DECLARE @CurrentFileNumber int
      DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
      DECLARE @CurrentDifferentialBaseIsSnapshot bit
      DECLARE @CurrentLogLSN numeric(25,0)
      DECLARE @CurrentLatestBackup datetime
      DECLARE @CurrentDatabaseNameFS nvarchar(max)
      DECLARE @CurrentDatabaseType nvarchar(max)
      DECLARE @CurrentDirectoryID int
      DECLARE @CurrentDirectoryPath nvarchar(max)
      DECLARE @CurrentFilePath nvarchar(max)
      DECLARE @CurrentMirrorFilePath nvarchar(max)
      DECLARE @CurrentDate datetime
      DECLARE @CurrentCleanupDate datetime
      DECLARE @CurrentIsDatabaseAccessible bit
      DECLARE @CurrentAvailabilityGroup nvarchar(max)
      DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
      DECLARE @CurrentIsPreferredBackupReplica bit
      DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
      DECLARE @CurrentLogShippingRole nvarchar(max)
    
      DECLARE @CurrentCommand01 nvarchar(max)
      DECLARE @CurrentCommand02 nvarchar(max)
      DECLARE @CurrentCommand03 nvarchar(max)
      DECLARE @CurrentCommand04 nvarchar(max)
    
      DECLARE @CurrentCommandOutput01 int
      DECLARE @CurrentCommandOutput02 int
      DECLARE @CurrentCommandOutput03 int
      DECLARE @CurrentCommandOutput04 int
    
      DECLARE @CurrentCommandType01 nvarchar(max)
      DECLARE @CurrentCommandType02 nvarchar(max)
      DECLARE @CurrentCommandType03 nvarchar(max)
      DECLARE @CurrentCommandType04 nvarchar(max)
    
      DECLARE @Directories TABLE (ID int PRIMARY KEY,
                                  DirectoryPath nvarchar(max),
                                  Completed bit)
    
      DECLARE @DirectoryInfo TABLE (FileExists bit,
                                    FileIsADirectory bit,
                                    ParentDirectoryExists bit)
    
      DECLARE @MirrorDirectoryInfo TABLE (FileExists bit,
                                    FileIsADirectory bit,
                                    ParentDirectoryExists bit)
    
      DECLARE @tmpDatabases TABLE (ID int IDENTITY,
                                   DatabaseName nvarchar(max),
                                   DatabaseNameFS nvarchar(max),
                                   DatabaseType nvarchar(max),
                                   Selected bit,
                                   Completed bit,
                                   PRIMARY KEY(Selected, Completed, ID))
    
      DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
                                        DatabaseType nvarchar(max),
                                        Selected bit)
    
      DECLARE @CurrentDirectories TABLE (ID int PRIMARY KEY,
                                         DirectoryPath nvarchar(max),
                                         CreateCompleted bit,
                                         CleanupCompleted bit,
                                         CreateOutput int,
                                         CleanupOutput int)
    
      DECLARE @CurrentFiles TABLE (CurrentFilePath nvarchar(max))
    
      DECLARE @Error int
      DECLARE @ReturnCode int
    
      SET @Error = 0
      SET @ReturnCode = 0
    
      SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
    
      ----------------------------------------------------------------------------------------------------
      --// Log initial information                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + REPLACE(@Directory,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @MirrorDirectory = ' + ISNULL('''' + REPLACE(@MirrorDirectory,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + REPLACE(@BackupType,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + REPLACE(@Verify,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @Compress = ' + ISNULL('''' + REPLACE(@Compress,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @CopyOnly = ' + ISNULL('''' + REPLACE(@CopyOnly,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @ChangeBackupType = ' + ISNULL('''' + REPLACE(@ChangeBackupType,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @BackupSoftware = ' + ISNULL('''' + REPLACE(@BackupSoftware,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @CheckSum = ' + ISNULL('''' + REPLACE(@CheckSum,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @BlockSize = ' + ISNULL(CAST(@BlockSize AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @BufferCount = ' + ISNULL(CAST(@BufferCount AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @MaxTransferSize = ' + ISNULL(CAST(@MaxTransferSize AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @NumberOfFiles = ' + ISNULL(CAST(@NumberOfFiles AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @CompressionLevel = ' + ISNULL(CAST(@CompressionLevel AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @Description = ' + ISNULL('''' + REPLACE(@Description,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @Threads = ' + ISNULL(CAST(@Threads AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @Throttle = ' + ISNULL(CAST(@Throttle AS nvarchar),'NULL')
      SET @StartMessage = @StartMessage + ', @Encrypt = ' + ISNULL('''' + REPLACE(@Encrypt,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @EncryptionType = ' + ISNULL('''' + REPLACE(@EncryptionType,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @EncryptionKey = ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @ReadWriteFileGroups = ' + ISNULL('''' + REPLACE(@ReadWriteFileGroups,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @RetryWrites = ' + ISNULL('''' + REPLACE(@RetryWrites,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
      SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
      SET @StartMessage = @StartMessage + 'Source: http://ola.hallengren.com' + CHAR(13) + CHAR(10)
      SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
      RAISERROR(@StartMessage,10,1) WITH NOWAIT
    
      ----------------------------------------------------------------------------------------------------
      --// Check core requirements                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')
      BEGIN
        SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
      BEGIN
        SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download http://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
      BEGIN
        SET @ErrorMessage = 'The table CommandLog is missing. Download http://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Error <> 0
      BEGIN
        SET @ReturnCode = @Error
        GOTO Logging
      END;
    
      ----------------------------------------------------------------------------------------------------
      --// Select databases                                                                           //--
      ----------------------------------------------------------------------------------------------------
    
      WITH Databases1 (DatabaseItems) AS
      (
      SELECT REPLACE(@Databases, ', ', ',') AS DatabaseItems
      ),
      Databases2 (DatabaseItem, String, [Continue]) AS
      (
      SELECT CASE WHEN CHARINDEX(',', DatabaseItems) = 0 THEN @Databases ELSE SUBSTRING(DatabaseItems, 1, CHARINDEX(',', DatabaseItems) - 1) END AS DatabaseItem,
             CASE WHEN CHARINDEX(',', DatabaseItems) = 0 THEN '' ELSE SUBSTRING(DatabaseItems, CHARINDEX(',', DatabaseItems) + 1, LEN(DatabaseItems)) END AS String,
             CASE WHEN CHARINDEX(',', DatabaseItems) = 0 THEN 0 ELSE 1 END [Continue]
      FROM Databases1
      WHERE @Databases IS NOT NULL
      UNION ALL
      SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS DatabaseItem,
             CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,
             CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]
      FROM Databases2
      WHERE [Continue] = 1
      ),
      Databases3 (DatabaseItem, Selected) AS
      (
      SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
             CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
      FROM Databases2
      ),
      Databases4 (DatabaseItem, DatabaseType, Selected) AS
      (
      SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
             CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
             Selected
      FROM Databases3
      ),
      Databases5 (DatabaseName, DatabaseType, Selected) AS
      (
      SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
             DatabaseType,
             Selected
      FROM Databases4
      )
      INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
      SELECT DatabaseName,
             DatabaseType,
             Selected
      FROM Databases5
    
      INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
      SELECT [name] AS DatabaseName,
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
             CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
             0 AS Selected,
             0 AS Completed
      FROM sys.databases
      WHERE [name] <> 'tempdb'
      AND source_database_id IS NULL
      ORDER BY [name] ASC
    
      UPDATE tmpDatabases
      SET tmpDatabases.Selected = SelectedDatabases.Selected
      FROM @tmpDatabases tmpDatabases
      INNER JOIN @SelectedDatabases SelectedDatabases
      ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
      AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
      WHERE SelectedDatabases.Selected = 1
    
      UPDATE tmpDatabases
      SET tmpDatabases.Selected = SelectedDatabases.Selected
      FROM @tmpDatabases tmpDatabases
      INNER JOIN @SelectedDatabases SelectedDatabases
      ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
      AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
      WHERE SelectedDatabases.Selected = 0
    
      IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END;
    
      ----------------------------------------------------------------------------------------------------
      --// Check database names                                                                       //--
      ----------------------------------------------------------------------------------------------------
    
      SET @ErrorMessage = ''
      SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
      FROM @tmpDatabases
      WHERE Selected = 1
      AND DatabaseNameFS = ''
      ORDER BY DatabaseName ASC
      IF @@ROWCOUNT > 0
      BEGIN
        SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      SET @ErrorMessage = ''
      SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
      FROM @tmpDatabases
      WHERE UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases GROUP BY UPPER(DatabaseNameFS) HAVING COUNT(*) > 1)
      AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)
      AND DatabaseNameFS <> ''
      ORDER BY DatabaseName ASC
      OPTION (RECOMPILE)
      IF @@ROWCOUNT > 0
      BEGIN
        SET @ErrorMessage = 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Select directories                                                                         //--
      ----------------------------------------------------------------------------------------------------
    
      IF @Directory IS NULL
      BEGIN
        EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultDirectory OUTPUT
    
        INSERT INTO @Directories (ID, DirectoryPath, Completed)
        SELECT 1, @DefaultDirectory, 0
      END
      ELSE
      BEGIN
        WITH Directory AS
        (
        SELECT REPLACE(@Directory, ', ', ',') AS DirectoryName
        ),
        Directories AS
        (
        SELECT CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN DirectoryName ELSE SUBSTRING(DirectoryName, 1, CHARINDEX(',', DirectoryName) - 1) END AS Directory,
               CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN '' ELSE SUBSTRING(DirectoryName, CHARINDEX(',', DirectoryName) + 1, LEN(DirectoryName)) END AS String,
               1 AS [ID],
             CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN 0 ELSE 1 END [Continue]
        FROM Directory
        UNION ALL
        SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS Directory,
               CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,
               [ID] + 1  AS [ID],
               CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]
        FROM Directories
        WHERE [Continue] = 1
        )
        INSERT INTO @Directories (ID, DirectoryPath, Completed)
        SELECT ID, Directory, 0
        FROM Directories
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Check directories                                                                          //--
      ----------------------------------------------------------------------------------------------------
    
      IF EXISTS(SELECT * FROM @Directories WHERE NOT (DirectoryPath LIKE '_:' OR DirectoryPath LIKE '_:\%' OR DirectoryPath LIKE '\\%\%') OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ' ' OR RIGHT(DirectoryPath,1) = ' ') OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) <> 1)
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Directory is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
      ELSE
      BEGIN
        WHILE EXISTS(SELECT * FROM @Directories WHERE Completed = 0)
        BEGIN
          SELECT TOP 1 @CurrentRootDirectoryID = ID,
                       @CurrentRootDirectoryPath = DirectoryPath
          FROM @Directories
          WHERE Completed = 0
          ORDER BY ID ASC
    
          INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
          EXECUTE [master].dbo.xp_fileexist @CurrentRootDirectoryPath
    
          IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
          BEGIN
            SET @ErrorMessage = 'The directory ' + @CurrentRootDirectoryPath + ' does not exist.' + CHAR(13) + CHAR(10) + ' '
            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
            SET @Error = @@ERROR
          END
    
          UPDATE @Directories
          SET Completed = 1
          WHERE ID = @CurrentRootDirectoryID
    
          SET @CurrentRootDirectoryID = NULL
          SET @CurrentRootDirectoryPath = NULL
    
          DELETE FROM @DirectoryInfo
        END
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Get default compression                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      IF @Compress IS NULL
      BEGIN
        SELECT @Compress = CASE
        WHEN @BackupSoftware IS NULL AND EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'Y'
        WHEN @BackupSoftware IS NULL AND NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'backup compression default' AND value_in_use = 1) THEN 'N'
        WHEN @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel > 0)  THEN 'Y'
        WHEN @BackupSoftware IS NOT NULL AND @CompressionLevel = 0  THEN 'N'
        END
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Get number of files                                                                        //--
      ----------------------------------------------------------------------------------------------------
    
      IF @NumberOfFiles IS NULL
      BEGIN
        SELECT @NumberOfFiles = (SELECT COUNT(*) FROM @Directories)
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Check mirror directory                                                                         //--
      ----------------------------------------------------------------------------------------------------
    
      IF @MirrorDirectory IS NOT NULL 
    	IF (@BackupSoftware <> 'SQLSAFE' OR @BackupSoftware IS NULL OR @NumberOfFiles > 1 OR (NOT (@MirrorDirectory LIKE '_:' OR @MirrorDirectory LIKE '_:\%' OR @MirrorDirectory LIKE '\\%\%') OR LEFT(@MirrorDirectory,1) = ' ' OR RIGHT(@MirrorDirectory,1) = ' '))
    	  BEGIN
    		SET @ErrorMessage = 'The value for the parameter @MirrorDirectory is not supported.' + CHAR(13) + CHAR(10) + ' '
    		RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    		SET @Error = @@ERROR
    	  END
    	  ELSE
    	  BEGIN
    		INSERT INTO @MirrorDirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
    		EXECUTE [master].dbo.xp_fileexist @MirrorDirectory
    
    		IF NOT EXISTS (SELECT * FROM @MirrorDirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
    		BEGIN
    			SET @ErrorMessage = 'The mirror directory ' + @MirrorDirectory + ' does not exist.' + CHAR(13) + CHAR(10) + ' '
    			RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    			SET @Error = @@ERROR
    		END
    		DELETE FROM @MirrorDirectoryInfo
    	  END
    
      ----------------------------------------------------------------------------------------------------
      --// Check input parameters                                                                     //--
      ----------------------------------------------------------------------------------------------------
    
      IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @BackupType is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Verify NOT IN ('Y','N') OR @Verify IS NULL OR (@BackupSoftware = 'SQLSAFE' AND @Encrypt = 'Y' AND @Verify = 'Y')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Verify is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @CleanupTime < 0
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Compress NOT IN ('Y','N') OR @Compress IS NULL OR (@Compress = 'Y' AND @BackupSoftware IS NULL AND NOT ((@Version >= 10 AND @Version < 10.5 AND SERVERPROPERTY('EngineEdition') = 3) OR (@Version >= 10.5 AND (SERVERPROPERTY('EngineEdition') = 3 OR SERVERPROPERTY('EditionID') IN (-1534726760, 284895786))))) OR (@Compress = 'N' AND @BackupSoftware IS NOT NULL AND (@CompressionLevel IS NULL OR @CompressionLevel >= 1)) OR (@Compress = 'Y' AND @BackupSoftware IS NOT NULL AND @CompressionLevel = 0)
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Compress is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @CopyOnly NOT IN ('Y','N') OR @CopyOnly IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @CopyOnly is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @ChangeBackupType NOT IN ('Y','N') OR @ChangeBackupType IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @ChangeBackupType is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BackupSoftware NOT IN ('LITESPEED','SQLBACKUP','HYPERBAC','SQLSAFE')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @BackupSoftware is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BackupSoftware = 'LITESPEED' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_backup_database')
      BEGIN
        SET @ErrorMessage = 'NetVault LiteSpeed for SQL Server is not installed. Download http://www.quest.com/litespeed-for-sql-server/.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BackupSoftware = 'SQLBACKUP' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'sqlbackup')
      BEGIN
        SET @ErrorMessage = 'Red Gate SQL Backup is not installed. Download http://www.red-gate.com/products/dba/sql-backup/.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BackupSoftware = 'SQLSAFE' AND NOT EXISTS (SELECT * FROM [master].sys.objects WHERE [type] = 'X' AND [name] = 'xp_ss_backup')
      BEGIN
        SET @ErrorMessage = 'Idera SQL safe backup is not installed. Download http://www.idera.com/Products/SQL-Server/SQL-safe-backup/.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @CheckSum NOT IN ('Y','N') OR @CheckSum IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @CheckSum is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BlockSize NOT IN (512,1024,2048,4096,8192,16384,32768,65536) OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BlockSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @BlockSize is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @BufferCount <= 0 OR @BufferCount > 2147483647 OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLBACKUP') OR (@BufferCount IS NOT NULL AND @BackupSoftware = 'SQLSAFE')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @BufferCount is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304 OR @MaxTransferSize % 65536 > 0 OR (@MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP') OR (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @MaxTransferSize is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @NumberOfFiles < 1 OR @NumberOfFiles > 64 OR (@NumberOfFiles > 32 AND @BackupSoftware = 'SQLBACKUP') OR @NumberOfFiles IS NULL OR @NumberOfFiles < (SELECT COUNT(*) FROM @Directories) OR @NumberOfFiles % (SELECT COUNT(*) FROM @Directories) > 0
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @NumberOfFiles is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF (@BackupSoftware IS NULL AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'HYPERBAC' AND @CompressionLevel IS NOT NULL) OR (@BackupSoftware = 'LITESPEED' AND (@CompressionLevel < 0 OR @CompressionLevel > 8)) OR (@BackupSoftware = 'SQLBACKUP' AND (@CompressionLevel < 0 OR @CompressionLevel > 4)) OR (@BackupSoftware = 'SQLSAFE' AND (@CompressionLevel < 1 OR @CompressionLevel > 6))
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @CompressionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF LEN(@Description) > 255 OR (@BackupSoftware = 'LITESPEED' AND LEN(@Description) > 128)
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Description is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Threads IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED','SQLBACKUP','SQLSAFE') OR @BackupSoftware IS NULL) OR @Threads < 2 OR @Threads > 32
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Threads is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Throttle IS NOT NULL AND (@BackupSoftware NOT IN('LITESPEED') OR @BackupSoftware IS NULL) OR @Throttle < 1 OR @Throttle > 100
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Throttle is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Encrypt NOT IN('Y','N') OR @Encrypt IS NULL OR (@Encrypt = 'Y' AND @BackupSoftware IS NULL)
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Encrypt is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF (@EncryptionType IS NOT NULL AND @BackupSoftware IS NULL) OR (@EncryptionType IS NOT NULL AND @BackupSoftware = 'HYPERBAC') OR (@EncryptionType IS NOT NULL AND @Encrypt = 'N') OR ((@EncryptionType NOT IN('RC2-40','RC2-56','RC2-112','RC2-128','3DES-168','RC4-128','AES-128','AES-192','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'LITESPEED') OR ((@EncryptionType NOT IN('AES-128','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'SQLBACKUP') OR ((@EncryptionType NOT IN('AES-128','AES-256') OR @EncryptionType IS NULL) AND @Encrypt = 'Y' AND @BackupSoftware = 'SQLSAFE')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @EncryptionType is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF (@EncryptionKey IS NOT NULL AND @BackupSoftware IS NULL) OR (@EncryptionKey IS NOT NULL AND @BackupSoftware = 'HYPERBAC') OR (@EncryptionKey IS NOT NULL AND @Encrypt = 'N') OR (@EncryptionKey IS NULL AND @Encrypt = 'Y' AND @BackupSoftware IN('LITESPEED','SQLBACKUP','SQLSAFE'))
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @EncryptionKey is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @ReadWriteFileGroups NOT IN('Y','N') OR @ReadWriteFileGroups IS NULL OR (@ReadWriteFileGroups = 'Y' AND @BackupType = 'LOG')
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @ReadWriteFileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Execute NOT IN('Y','N') OR @Execute IS NULL
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF (@BackupSoftware <> 'SQLSAFE' OR @BackupSoftware IS NULL) AND (LEN(@RetryWrites) > 0 OR @RetryWrites IS NOT NULL)
      BEGIN
        SET @ErrorMessage = 'The value for the parameter @RetryWrites is not supported.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
      END
    
      IF @Error <> 0
      BEGIN
        SET @ErrorMessage = 'The documentation is available at http://ola.hallengren.com/sql-server-backup.html.' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @ReturnCode = @Error
        GOTO Logging
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Check Availability Group cluster name                                                      //--
      ----------------------------------------------------------------------------------------------------
    
      IF @Version >= 11
      BEGIN
        SELECT @Cluster = cluster_name
        FROM sys.dm_hadr_cluster
      END
    


    Diane

    Monday, June 16, 2014 7:03 PM
  • And here is part 2 of the stored procedure:

     ----------------------------------------------------------------------------------------------------
      --// Execute backup commands                                                                    //--
      ----------------------------------------------------------------------------------------------------
    
      WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
      BEGIN
    
        SELECT TOP 1 @CurrentDBID = ID,
                     @CurrentDatabaseName = DatabaseName,
                     @CurrentDatabaseNameFS = DatabaseNameFS,
    				 @CurrentDatabaseType = DatabaseType
        FROM @tmpDatabases
        WHERE Selected = 1
        AND Completed = 0
        ORDER BY ID ASC
    
        SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
    
        IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
        BEGIN
          IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
          BEGIN
            SET @CurrentIsDatabaseAccessible = 1
          END
          ELSE
          BEGIN
            SET @CurrentIsDatabaseAccessible = 0
          END
        END
        ELSE
        BEGIN
          SET @CurrentIsDatabaseAccessible = 0
        END
    
        SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
        FROM sys.master_files
        WHERE database_id = @CurrentDatabaseID
        AND [type] = 0
        AND [file_id] = 1
    
        -- Workaround for a bug in SQL Server 2005
        IF @Version >= 9 AND @Version < 10
        AND EXISTS(SELECT * FROM sys.master_files WHERE database_id = @CurrentDatabaseID AND [type] = 0 AND [file_id] = 1 AND differential_base_lsn IS NOT NULL AND differential_base_guid IS NOT NULL AND differential_base_time IS NULL)
        BEGIN
          SET @CurrentDifferentialBaseLSN = NULL
        END
    
        SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
        FROM msdb.dbo.backupset
        WHERE database_name = @CurrentDatabaseName
        AND [type] = 'D'
        AND checkpoint_lsn = @CurrentDifferentialBaseLSN
    
        IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
        BEGIN
          SELECT @CurrentLogLSN = last_log_backup_lsn
          FROM sys.database_recovery_status
          WHERE database_id = @CurrentDatabaseID
        END
    
        SET @CurrentBackupType = @BackupType
    
        IF @ChangeBackupType = 'Y'
        BEGIN
          IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') <> 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
          BEGIN
            SET @CurrentBackupType = 'DIFF'
          END
          IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL AND @CurrentDatabaseName <> 'master'
          BEGIN
            SET @CurrentBackupType = 'FULL'
          END
        END
    
        IF @CurrentBackupType = 'LOG'
        BEGIN
          SELECT @CurrentLatestBackup = MAX(backup_finish_date)
          FROM msdb.dbo.backupset
          WHERE [type] IN('D','I')
          AND is_damaged = 0
          AND database_name = @CurrentDatabaseName
        END
    
        IF @Version >= 11 AND @Cluster IS NOT NULL
        BEGIN
          SELECT @CurrentAvailabilityGroup = availability_groups.name,
                 @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
          FROM sys.databases databases
          INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
          INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
          INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
          WHERE databases.name = @CurrentDatabaseName
        END
    
        IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
        BEGIN
          SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
        END
    
        SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
        FROM sys.database_mirroring
        WHERE database_id = @CurrentDatabaseID
    
        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
    
        -- Set database message
        SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
        IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
        IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
        IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is preferred backup replica: ' + CASE WHEN @CurrentIsPreferredBackupReplica = 1 THEN 'Yes' WHEN @CurrentIsPreferredBackupReplica = 0 THEN 'No' ELSE 'N/A' END + CHAR(13) + CHAR(10)
        IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
        IF @CurrentLogShippingRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Log shipping role: ' + @CurrentLogShippingRole + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Differential base LSN: ' + ISNULL(CAST(@CurrentDifferentialBaseLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Differential base is snapshot: ' + CASE WHEN @CurrentDifferentialBaseIsSnapshot = 1 THEN 'Yes' WHEN @CurrentDifferentialBaseIsSnapshot = 0 THEN 'No' ELSE 'N/A' END + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = @DatabaseMessage + 'Last log backup LSN: ' + ISNULL(CAST(@CurrentLogLSN AS nvarchar),'N/A') + CHAR(13) + CHAR(10)
        SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
        RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
    
        IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
        AND NOT (DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
        AND DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 0
        AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') = 'SIMPLE' OR @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 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 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')
        BEGIN
    
          -- Set variables
          SET @CurrentDate = GETDATE()
    
          IF @CleanupTime IS NULL OR (@CurrentBackupType = 'LOG' AND @CurrentLatestBackup IS NULL) OR @CurrentBackupType <> @BackupType
          BEGIN
            SET @CurrentCleanupDate = NULL
          END
          ELSE
          IF @CurrentBackupType = 'LOG'
          BEGIN
            SET @CurrentCleanupDate = (SELECT MIN([Date]) FROM(SELECT DATEADD(hh,-(@CleanupTime),@CurrentDate) AS [Date] UNION SELECT @CurrentLatestBackup AS [Date]) Dates)
          END
          ELSE
          BEGIN
            SET @CurrentCleanupDate = DATEADD(hh,-(@CleanupTime),@CurrentDate)
          END
    
          SELECT @CurrentFileExtension = CASE
          WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'FULL' THEN 'bak'
          WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'DIFF' THEN 'bak'
          WHEN @BackupSoftware IS NULL AND @CurrentBackupType = 'LOG' THEN 'trn'
          WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'FULL' THEN 'bak'
          WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'DIFF' THEN 'bak'
          WHEN @BackupSoftware = 'LITESPEED' AND @CurrentBackupType = 'LOG' THEN 'trn'
          WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'FULL' THEN 'sqb'
          WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'DIFF' THEN 'sqb'
          WHEN @BackupSoftware = 'SQLBACKUP' AND @CurrentBackupType = 'LOG' THEN 'sqb'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'FULL' AND @Encrypt = 'N' THEN 'hbc'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'DIFF' AND @Encrypt = 'N' THEN 'hbc'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'LOG' AND @Encrypt = 'N' THEN 'hbc'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'FULL' AND @Encrypt = 'Y' THEN 'hbe'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'DIFF' AND @Encrypt = 'Y' THEN 'hbe'
          WHEN @BackupSoftware = 'HYPERBAC' AND @CurrentBackupType = 'LOG' AND @Encrypt = 'Y' THEN 'hbe'
          WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'FULL' THEN 'safe'
          WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'DIFF' THEN 'safe'
          WHEN @BackupSoftware = 'SQLSAFE' AND @CurrentBackupType = 'LOG' THEN 'safe'
          END
    
          INSERT INTO @CurrentDirectories (ID, DirectoryPath, CreateCompleted, CleanupCompleted)
          SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentBackupType = 'LOG' THEN '\Transaction Dumps\' + @CurrentDatabaseNameFS ELSE '' END, 0, 0
          FROM @Directories
          ORDER BY ID ASC
    
          SET @CurrentFileNumber = 0
    	  SET @CurrentMirrorFilePath = NULL
    
          WHILE @CurrentFileNumber < @NumberOfFiles
          BEGIN
            SET @CurrentFileNumber = @CurrentFileNumber + 1
    
            SELECT @CurrentDirectoryPath = DirectoryPath
            FROM @CurrentDirectories
            WHERE @CurrentFileNumber >= (ID - 1) * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories) + 1
            AND @CurrentFileNumber <= ID * (SELECT @NumberOfFiles / COUNT(*) FROM @CurrentDirectories)
    
            SET @CurrentFilePath = @CurrentDirectoryPath + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '_' + @CurrentDatabaseType + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '.' + @CurrentFileExtension
    
            IF LEN(@CurrentFilePath) > 257
            BEGIN
              SET @CurrentFilePath = @CurrentDirectoryPath + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 257 - LEN(@CurrentFilePath) - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 257 - LEN(@CurrentFilePath) - 3) END) + '...' + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '_' + @CurrentDatabaseType + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '.' + @CurrentFileExtension
            END
    
    		IF @CurrentFileNumber = 1 AND LEN(@MirrorDirectory) > 0
    		BEGIN
    			SET @CurrentMirrorFilePath = @MirrorDirectory + CASE WHEN RIGHT(@MirrorDirectory,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentBackupType = 'LOG' THEN '\Transaction Dumps\' + @CurrentDatabaseNameFS ELSE '' END + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '_' + @CurrentDatabaseType + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '.' + @CurrentFileExtension
    
    			IF LEN(@CurrentFilePath) > 257
    			BEGIN
    			  SET @CurrentMirrorFilePath = @MirrorDirectory + CASE WHEN RIGHT(@MirrorDirectory,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentBackupType = 'LOG' THEN '\Transaction Dumps\' + @CurrentDatabaseNameFS ELSE '' END + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 257 - LEN(@CurrentFilePath) - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 257 - LEN(@CurrentFilePath) - 3) END) + '...' + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '_' + @CurrentDatabaseType + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '.' + @CurrentFileExtension
    			END
    		END
    
            INSERT INTO @CurrentFiles (CurrentFilePath)
            SELECT @CurrentFilePath
    
            SET @CurrentDirectoryPath = NULL
            SET @CurrentFilePath = NULL
          END
    
          -- Create directory
          WHILE EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateCompleted = 0)
          BEGIN
            SELECT TOP 1 @CurrentDirectoryID = ID,
                         @CurrentDirectoryPath = DirectoryPath
            FROM @CurrentDirectories
            WHERE CreateCompleted = 0
            ORDER BY ID ASC
    
            SET @CurrentCommandType01 = 'xp_create_subdir'
            SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'
            EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
            SET @Error = @@ERROR
            IF @Error <> 0 SET @CurrentCommandOutput01 = @Error
            IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01
    
            UPDATE @CurrentDirectories
            SET CreateCompleted = 1,
                CreateOutput = @CurrentCommandOutput01
            WHERE ID = @CurrentDirectoryID
    
            SET @CurrentDirectoryID = NULL
            SET @CurrentDirectoryPath = NULL
    
            SET @CurrentCommand01 = NULL
    
            SET @CurrentCommandOutput01 = NULL
    
            SET @CurrentCommandType01 = NULL
          END
    
          -- Perform a backup
          IF NOT EXISTS (SELECT * FROM @CurrentDirectories WHERE CreateOutput <> 0 OR CreateOutput IS NULL)
          BEGIN
            IF @BackupSoftware IS NULL
            BEGIN
              SELECT @CurrentCommandType02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP_DATABASE'
              WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP_LOG'
              END
    
              SELECT @CurrentCommand02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
              WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
              END
    
              IF @ReadWriteFileGroups = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ' READ_WRITE_FILEGROUPS'
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' TO'
    
              SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'
              IF @Compress = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COMPRESSION'
              IF @Compress = 'N' AND @Version >= 10 SET @CurrentCommand02 = @CurrentCommand02 + ', NO_COMPRESSION'
              IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'
              IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'
              IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
              IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)
              IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)
              IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
            END
    
            IF @BackupSoftware = 'LITESPEED'
            BEGIN
              SELECT @CurrentCommandType02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'xp_backup_database'
              WHEN @CurrentBackupType = 'LOG' THEN 'xp_backup_log'
              END
    
              SELECT @CurrentCommand02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_database @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
              WHEN @CurrentBackupType = 'LOG' THEN 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_backup_log @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
              END
    
              SELECT @CurrentCommand02 = @CurrentCommand02 + ', @filename = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand02 = @CurrentCommand02 + ', @with = '''
              IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'
              IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'
              IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'
              IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
              SET @CurrentCommand02 = @CurrentCommand02 + ''''
              IF @ReadWriteFileGroups = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', @read_write_filegroups = 1'
              IF @CompressionLevel IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @compressionlevel = ' + CAST(@CompressionLevel AS nvarchar)
              IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @buffercount = ' + CAST(@BufferCount AS nvarchar)
              IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @maxtransfersize = ' + CAST(@MaxTransferSize AS nvarchar)
              IF @Threads IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @threads = ' + CAST(@Threads AS nvarchar)
              IF @Throttle IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @throttle = ' + CAST(@Throttle AS nvarchar)
              IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
    
              IF @EncryptionType IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @cryptlevel = ' + CASE
              WHEN @EncryptionType = 'RC2-40' THEN '0'
              WHEN @EncryptionType = 'RC2-56' THEN '1'
              WHEN @EncryptionType = 'RC2-112' THEN '2'
              WHEN @EncryptionType = 'RC2-128' THEN '3'
              WHEN @EncryptionType = '3DES-168' THEN '4'
              WHEN @EncryptionType = 'RC4-128' THEN '5'
              WHEN @EncryptionType = 'AES-128' THEN '6'
              WHEN @EncryptionType = 'AES-192' THEN '7'
              WHEN @EncryptionType = 'AES-256' THEN '8'
              END
    
              IF @EncryptionKey IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
              SET @CurrentCommand02 = @CurrentCommand02 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing LiteSpeed backup.'', 16, 1)'
            END
    
            IF @BackupSoftware = 'SQLBACKUP'
            BEGIN
              SET @CurrentCommandType02 = 'sqlbackup'
    
              SELECT @CurrentCommand02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
              WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
              END
    
              IF @ReadWriteFileGroups = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ' READ_WRITE_FILEGROUPS'
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' TO'
    
              SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'
              IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'
              IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'
              IF @CompressionLevel IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', COMPRESSION = ' + CAST(@CompressionLevel AS nvarchar)
              IF @Threads IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', THREADCOUNT = ' + CAST(@Threads AS nvarchar)
              IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)
              IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
    
              IF @EncryptionType IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', KEYSIZE = ' + CASE
              WHEN @EncryptionType = 'AES-128' THEN '128'
              WHEN @EncryptionType = 'AES-256' THEN '256'
              END
    
              IF @EncryptionKey IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
              SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand02,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLBackup backup.'', 16, 1)'
            END
    
            IF @BackupSoftware = 'HYPERBAC'
            BEGIN
              SET @CurrentCommandType02 = 'BACKUP_DATABASE'
    
              SELECT @CurrentCommand02 = CASE
              WHEN @CurrentBackupType IN('DIFF','FULL') THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
              WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
              END
    
              IF @ReadWriteFileGroups = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ' READ_WRITE_FILEGROUPS'
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' TO'
    
              SELECT @CurrentCommand02 = @CurrentCommand02 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand02 = @CurrentCommand02 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand02 = @CurrentCommand02 + 'NO_CHECKSUM'
              IF @CurrentBackupType = 'DIFF' SET @CurrentCommand02 = @CurrentCommand02 + ', DIFFERENTIAL'
              IF @CopyOnly = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', COPY_ONLY'
              IF @BlockSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BLOCKSIZE = ' + CAST(@BlockSize AS nvarchar)
              IF @BufferCount IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', BUFFERCOUNT = ' + CAST(@BufferCount AS nvarchar)
              IF @MaxTransferSize IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', MAXTRANSFERSIZE = ' + CAST(@MaxTransferSize AS nvarchar)
              IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', DESCRIPTION = N''' + REPLACE(@Description,'''','''''') + ''''
            END
    
            IF @BackupSoftware = 'SQLSAFE'
            BEGIN
              SET @CurrentCommandType02 = 'xp_ss_backup'
    
              SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_backup @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
    
              SELECT @CurrentCommand02 = @CurrentCommand02 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
    		  IF @CurrentMirrorFilePath IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @mirrorfile = N''' + @CurrentMirrorFilePath + ''''
              SET @CurrentCommand02 = @CurrentCommand02 + ', @backuptype = ' + CASE WHEN @CurrentBackupType = 'FULL' THEN '''Full''' WHEN @CurrentBackupType = 'DIFF' THEN '''Differential''' WHEN @CurrentBackupType = 'LOG' THEN '''Log''' END
              IF @ReadWriteFileGroups = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ', @readwritefilegroups = 1'
              SET @CurrentCommand02 = @CurrentCommand02 + ', @checksum = ' + CASE WHEN @CheckSum = 'Y' THEN '1' WHEN @CheckSum = 'N' THEN '0' END
              SET @CurrentCommand02 = @CurrentCommand02 + ', @copyonly = ' + CASE WHEN @CopyOnly = 'Y' THEN '1' WHEN @CopyOnly = 'N' THEN '0' END
              IF @CompressionLevel IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @compressionlevel = ' + CASE WHEN @CompressionLevel = 5 THEN N'ispeed' WHEN @CompressionLevel = 6 THEN N'isize' ELSE CAST(@CompressionLevel AS nvarchar) END
              IF @RetryWrites IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @retrywrites  = N''' + @RetryWrites + ''''
              IF @Threads IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @threads = ' + CAST(@Threads AS nvarchar)
              IF @Description IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @desc = N''' + REPLACE(@Description,'''','''''') + ''''
    
              IF @EncryptionType IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @encryptiontype = N''' + CASE
              WHEN @EncryptionType = 'AES-128' THEN 'AES128'
              WHEN @EncryptionType = 'AES-256' THEN 'AES256'
              END + ''''
    
              IF @EncryptionKey IS NOT NULL SET @CurrentCommand02 = @CurrentCommand02 + ', @encryptedbackuppassword = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
              SET @CurrentCommand02 = @CurrentCommand02 + ' IF @ReturnCode <> 0 RAISERROR(''Error performing SQLsafe backup.'', 16, 1)'
            END
    
            EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @Command = @CurrentCommand02, @CommandType = @CurrentCommandType02, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
            SET @Error = @@ERROR
            IF @Error <> 0 SET @CurrentCommandOutput02 = @Error
            IF @CurrentCommandOutput02 <> 0 SET @ReturnCode = @CurrentCommandOutput02
          END
    
          -- Verify the backup
          IF @CurrentCommandOutput02 = 0 AND @Verify = 'Y'
          BEGIN
            IF @BackupSoftware IS NULL
            BEGIN
              SET @CurrentCommandType03 = 'RESTORE_VERIFYONLY'
    
              SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
    
              SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand03 = @CurrentCommand03 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
            END
    
            IF @BackupSoftware = 'LITESPEED'
            BEGIN
              SET @CurrentCommandType03 = 'xp_restore_verifyonly'
    
              SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_restore_verifyonly'
    
              SELECT @CurrentCommand03 = @CurrentCommand03 + ' @filename = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand03 = @CurrentCommand03 + ', @with = '''
              IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
              SET @CurrentCommand03 = @CurrentCommand03 + ''''
              IF @EncryptionKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', @encryptionkey = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
    
              SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying LiteSpeed backup.'', 16, 1)'
            END
    
            IF @BackupSoftware = 'SQLBACKUP'
            BEGIN
              SET @CurrentCommandType03 = 'sqlbackup'
    
              SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
    
              SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand03 = @CurrentCommand03 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
              IF @EncryptionKey IS NOT NULL SET @CurrentCommand03 = @CurrentCommand03 + ', PASSWORD = N''' + REPLACE(@EncryptionKey,'''','''''') + ''''
    
              SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.sqlbackup N''-SQL "' + REPLACE(@CurrentCommand03,'''','''''') + '"''' + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLBackup backup.'', 16, 1)'
            END
    
            IF @BackupSoftware = 'HYPERBAC'
            BEGIN
              SET @CurrentCommandType03 = 'RESTORE_VERIFYONLY'
    
              SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM'
    
              SELECT @CurrentCommand03 = @CurrentCommand03 + ' DISK = N''' + REPLACE(CurrentFilePath,'''','''''') + '''' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) <> @NumberOfFiles THEN ',' ELSE '' END
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand03 = @CurrentCommand03 + ' WITH '
              IF @CheckSum = 'Y' SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM'
              IF @CheckSum = 'N' SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM'
            END
    
            IF @BackupSoftware = 'SQLSAFE'
            BEGIN
              SET @CurrentCommandType03 = 'xp_ss_verify'
    
              SET @CurrentCommand03 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_verify @database = N''' + REPLACE(@CurrentDatabaseName,'''','''''') + ''''
    
              SELECT @CurrentCommand03 = @CurrentCommand03 + ', ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY CurrentFilePath ASC) = 1 THEN '@filename' ELSE '@backupfile' END + ' = N''' + REPLACE(CurrentFilePath,'''','''''') + ''''
              FROM @CurrentFiles
              ORDER BY CurrentFilePath ASC
    
              SET @CurrentCommand03 = @CurrentCommand03 + ' IF @ReturnCode <> 0 RAISERROR(''Error verifying SQLsafe backup.'', 16, 1)'
            END
    
            EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03, @CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
            SET @Error = @@ERROR
            IF @Error <> 0 SET @CurrentCommandOutput03 = @Error
            IF @CurrentCommandOutput03 <> 0 SET @ReturnCode = @CurrentCommandOutput03
          END
    
          -- Delete old backup files
          IF (@CurrentCommandOutput02 = 0 AND @Verify = 'N' AND @CurrentCleanupDate IS NOT NULL)
          OR (@CurrentCommandOutput02 = 0 AND @Verify = 'Y' AND @CurrentCommandOutput03 = 0 AND @CurrentCleanupDate IS NOT NULL)
          BEGIN
            WHILE EXISTS (SELECT * FROM @CurrentDirectories WHERE CleanupCompleted = 0)
            BEGIN
              SELECT TOP 1 @CurrentDirectoryID = ID,
                           @CurrentDirectoryPath = DirectoryPath
              FROM @CurrentDirectories
              WHERE CleanupCompleted = 0
              ORDER BY ID ASC
    
              IF @BackupSoftware IS NULL
              BEGIN
                SET @CurrentCommandType04 = 'xp_delete_file'
    
                SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].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 @CurrentCommandType04 = 'xp_slssqlmaint'
    
                SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_slssqlmaint N''-MAINTDEL -DELFOLDER "' + REPLACE(@CurrentDirectoryPath,'''','''''') + '" -DELEXTENSION "' + @CurrentFileExtension + '" -DELUNIT "' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + '" -DELUNITTYPE "minutes" -DELUSEAGE'' IF @ReturnCode <> 0 RAISERROR(''Error deleting LiteSpeed backup files.'', 16, 1)'
              END
    
              IF @BackupSoftware = 'SQLBACKUP'
              BEGIN
                SET @CurrentCommandType04 = 'sqbutility'
    
                SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].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,GETDATE()) + 1 AS nvarchar) + 'h'', ' + ISNULL('''' + REPLACE(@EncryptionKey,'''','''''') + '''','NULL') + ' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLBackup backup files.'', 16, 1)'
              END
    
              IF @BackupSoftware = 'HYPERBAC'
              BEGIN
                SET @CurrentCommandType04 = 'xp_delete_file'
    
                SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].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 = 'SQLSAFE'
              BEGIN
                SET @CurrentCommandType04 = 'xp_ss_delete'
    
                SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_ss_delete @filename = N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + '\*.' + @CurrentFileExtension + ''', @age = ''' + CAST(DATEDIFF(mi,@CurrentCleanupDate,GETDATE()) + 1 AS nvarchar) + 'Minutes'' IF @ReturnCode <> 0 RAISERROR(''Error deleting SQLsafe backup files.'', 16, 1)'
              END
    
              EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
              SET @Error = @@ERROR
              IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
              IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
    
              UPDATE @CurrentDirectories
              SET CleanupCompleted = 1,
                  CleanupOutput = @CurrentCommandOutput04
              WHERE ID = @CurrentDirectoryID
    
              SET @CurrentDirectoryID = NULL
              SET @CurrentDirectoryPath = NULL
    
              SET @CurrentCommand04 = NULL
    
              SET @CurrentCommandOutput04 = NULL
    
              SET @CurrentCommandType04 = NULL
            END
          END
        END
    
        -- Update that the database is completed
        UPDATE @tmpDatabases
        SET Completed = 1
        WHERE Selected = 1
        AND Completed = 0
        AND ID = @CurrentDBID
    
        -- Clear variables
        SET @CurrentDBID = NULL
        SET @CurrentDatabaseID = NULL
        SET @CurrentDatabaseName = NULL
        SET @CurrentBackupType = NULL
        SET @CurrentFileExtension = NULL
        SET @CurrentFileNumber = NULL
        SET @CurrentDifferentialBaseLSN = NULL
        SET @CurrentDifferentialBaseIsSnapshot = NULL
        SET @CurrentLogLSN = NULL
        SET @CurrentLatestBackup = NULL
        SET @CurrentDatabaseNameFS = NULL
        SET @CurrentDatabaseType = NULL
        SET @CurrentDate = NULL
        SET @CurrentCleanupDate = NULL
        SET @CurrentIsDatabaseAccessible = NULL
        SET @CurrentAvailabilityGroup = NULL
        SET @CurrentAvailabilityGroupRole = NULL
        SET @CurrentIsPreferredBackupReplica = NULL
        SET @CurrentDatabaseMirroringRole = NULL
        SET @CurrentLogShippingRole = NULL
    
        SET @CurrentCommand02 = NULL
        SET @CurrentCommand03 = NULL
    
        SET @CurrentCommandOutput02 = NULL
        SET @CurrentCommandOutput03 = NULL
    
        SET @CurrentCommandType02 = NULL
        SET @CurrentCommandType03 = NULL
    
        DELETE FROM @CurrentDirectories
        DELETE FROM @CurrentFiles
    
      END
    
      ----------------------------------------------------------------------------------------------------
      --// Log completing information                                                                 //--
      ----------------------------------------------------------------------------------------------------
    
      Logging:
      SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
      SET @EndMessage = REPLACE(@EndMessage,'%','%%')
      RAISERROR(@EndMessage,10,1) WITH NOWAIT
    
      IF @ReturnCode <> 0
      BEGIN
        RETURN @ReturnCode
      END
    
      ----------------------------------------------------------------------------------------------------
    
    END
    
    GO
    
    ALTER AUTHORIZATION ON [dbo].[DatabaseBackup] TO  SCHEMA OWNER 
    GO


    Diane

    Monday, June 16, 2014 7:05 PM
  • Not that we could see.  As well, this only occurred on 1 database, so I don't think it would be network-related, otherwise, all the databases should have failed to be backed-up. 

    So far, it has only happened once, lasting for 15-20 minutes, until I resolved it via the steps above.


    Diane

    Monday, June 16, 2014 7:08 PM
  • Hi and thanks for the reply!

    I will certainly try to run more troubleshooting with DBCC INPUTBUFFER if it happens again. and try to view the wait stats.

    I do believe that the database showed it was connected on the dashboard, but I will try to remember to specifically look for that particular column next time.

    The reason we do asynchronous replica backup is because it is the node which is located at our head office site, where we have all our backup servers and media stored.  We also do many restores from the backups to our development and test servers, which are also located at head office, so it made sense to locate the backups there in order to minimize traffic across our T3 line.  Backups occur every 1 minute, and our SLA allows us to have a few minutes data loss if for some reason the primary and secondary synch nodes went down, and only the asynchronous node was available.


    Diane

    Monday, June 16, 2014 7:14 PM
  • Hi Diane,

    By further investigation, I suppose what you found in the "command" column is "HADR_BACKUP_LOCK_HOLDER".

    http://blogs.msdn.com/b/psssql/archive/2012/05/17/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases.aspx

    The wait type should be HADR_BACKUP_BULK_LOCK and HADR_BACKUP_QUEUE and the database lock LCK_M_S is needed. The backup can be blocked if it cannot get this lock. The root cause cannot be identified now because the issue does not reproduce.

    I think you can ignore this error now. If it reproduces in the future, it is recommended to get result of “sys.dm_exec_requests”, run “DBCC STACKDUMP” and open a support case for us.

    If you cannot determine your answer here or on your
    own, consider opening a support case with us. Visit this link to see the
    various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Wednesday, June 18, 2014 5:53 AM
  • Thanks for the reply!

    Yes, I suppose the actual command could be that, it may be that the command was truncated in my output.  If the issue occurs again, I will try these steps to get more info.  So far, it's been 10 days without issue, and before that, it has never come up since server deployment a year ago.  We will continue to monitor if it occurs again. 

    If this does occur again, what would you suggest to work-around it?  I'm not sure if running the DBCC CHECKDB  command or restarting the SQL Server Agent service on the node running the backup were the right steps, although the situation resolved itself shortly after the restart of the service.


    Diane

    Wednesday, June 18, 2014 3:53 PM
  • Not sure, if we have two backup sets clashing here for log.

    We need to check more on the process going into blocking mode.

    Thanks for sharing details with us.


    Santosh Singh

    Wednesday, June 18, 2014 5:53 PM