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'
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('TestDB1'))
IF (@preferredReplica = 1)
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
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.