I do have SQL Server 2014 high availability setup in Prod env.
Server A – Primary, B-Secondary and C -DR.
Server A to B are synchronous commit and automatic failover.
Server A to C are asynchronous commit and manual failover.
I need to setup full-backup and diff backup. Can I do it on Server C?
I have given server C 98% priority in backup preferences – AG.
Running below query on server C but it throwing 1 which means I can't take backup on that server.
SELECT sys.fn_hadr_backup_is_preferred_replica (‘dbname’);
GO
Best Answer
You can do a copy only full, but unless C is the primary you cannot do a differential database backup.
A return value of 1 means it is "preferred" but doesn't necessarily mean you can run any type of backup on it.