Database backup Verifications fail when using AG - by UCF-Sean Greenbaum

Status : 

 


5
0
Sign in
to vote
ID 811830 Comments
Status Active Workarounds
Type Bug Repros 3
Opened 12/17/2013 1:15:16 PM
Access Restriction Public

Description

I have 2 VMware VMs , each running Windows Server 2012 R2 and a standalone instance of SQL 2012 Enterprise SP1 CU7.
I used the Maintenance Plan Wizard to create Back Up Database plans. These instances have AG setup between them. One is primary and the other secondary. 

Running the plan fails on the primary, but works on the secondary. Performing a failover, and the same results. The new primary fails while the new secondary succeeds.  
I think I know why. Looking at the T-SQL code is generates:

*** Code ***
EXECUTE master.dbo.xp_create_subdir N'K:\<path>\TestDB1'
GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('TestDB1'))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [TestDB1] TO  DISK = N'K:\<path>\TestDB1\TestDB1_backup_2013_12_17_155316_7373545.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'TestDB1_backup_2013_12_17_155316_7373545', SKIP, REWIND, NOUNLOAD,  STATS = 10
END
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TestDB1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TestDB1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TestDB1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'K:\<path>\TestDB1\TestDB1_backup_2013_12_17_155316_7373545.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

*** End Code ***

As you can see, the IF statement that handles checking to see if its a preferredReplica only contains the backup command. The verify then takes place even if there was no backup performed to verify.
Sign in to post a comment.
Posted by COSDBA on 7/9/2015 at 7:31 PM
I agree that there's an issue with the code. The verify should not take place if there was no backup file created.

I should be able to use the exact same configuration within the maintenance plan wizard on each of the replicas.
Posted by MichelZ on 5/24/2014 at 3:39 AM
Same problem as:
https://connect.microsoft.com/SQLServer/feedback/details/802286/database-backup-maintenance-plan-fails-on-backup-verify-when-running-on-an-availability-group-primary-replica#tabs

Let's concentrate the efforts there.