Home Dashboard Directory Help
Search

Database backup Verifications fail when using AG by Sean Greenbaum


Status: 

Active


3
0
Sign in
to vote
Type: Bug
ID: 811830
Opened: 12/17/2013 1:15:16 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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.
Details
Sign in to post a comment.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
sql maintenace plan.PNG 12/17/2013 94 KB