SQL 2012R2 11.0.5058.0 Enterprise Edition

Backing up all databases on a sql server that hosts secondary high availability databases as well as other databases.  The other databases back up fine, but the high availability secondaries all get the same error:

BackupDiskFile::OpenMedia: Backup device 'F:\MSSQL\Backup\dbname\dbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)

  can you verify if the file exists on the server- make sure the path is correct. and your sql service account has permissions to access and write to that folder.

also,  what kind of backup are you trying to take on the secondary-- you can only take copy only full backup or log backups on secondary.

No the file does not exist.  I expect the maintenance plan to create it, just as it does for all the non-high availability databases.  It is not a security issue because all the non-secondary databases are backing up fine to the same drive/folder.  Copy-only is selected in the maintenance plan, as well as verify backup integrity, and Compress backup.

Thanks.

This can happen if your database names are illegal as Windows folder or file names.

http://blogs.msdn.com/b/sqlserverfaq/archive/2008/07/27/maintenance-plans-with-backup-database-task-fails-when-the-create-a-sub-directory-for-each-database-option-is-checked.aspx

ie this

backup database [dbname] to disk='F:\MSSQL\Backup\test.bak' with copy_only, init

may work fine.  This is especially nasty if you have a trailing space in your database name. eg

select quotename(db_name(database_id)) name
from sys.databases

Since it's hard to see, and SQL Server will ignore the trailing spaces for most purposes.

In any case, go to the "View T-SQL" on the Back Up Database Task to examine and run the TSQL generated by the plan.

David

Also, perhaps it is the verification that fails? Try without it and see (as a troubleshooting measure).

I executed the Maintenance Plan instead of the resulting job and noticed that it completed successfully without backing up the secondary databases.  "All Databases" is the option selected in the Mtc Plan Backup task.   The Job however, fails with error code 0xC002F210 with verify database integrity turned on.  It succeeds as well without backing up the secondary databases without the verify checked. I even selected the databases individually with the same results.

There are no spaces in the database names.

• Edited by AbbeyNormal 9 hours 23 minutes ago

>Also, perhaps it is the verification that fails?

Yes. How are backup preferences configured? See

Availability Group Properties/New Availability Group (Backup Preferences Page)

could be

https://connect.microsoft.com/SQLServer/feedback/details/810870/maintenance-plan-backup-verification-with-availability-groups-and-preferred-backup-locations

David

The Backup Preferences say Prefer Secondary.  None of the replicates have Exclude checked.  This server is the second secondary, which exists at another location.

What does sys.fn_hadr_backup_is_preferred_replica say? 

David