Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups

By:   |   Comments (17)   |   Related: > Availability Groups


So I am using this new technology called Availability Groups that was introduced in SQL Server 2012. I have heard I can offload my backups to a secondary replica, but I am not sure how that works or how to configure those backups.  Are there any limitations?  Check out this tip to learn more.


SQL Server 2012 AlwaysOn Availability Groups allows the offloading of CERTAIN types of backups to a replica. At the time of this writing, only transaction log backups and full backups with COPY_ONLY are supported on secondary replicas. Differential backups are not supported on secondary replicas, so if differential backups are in your plans, you may be stuck with running on the primary node only.

Below is a list of limitations taken from SQL Server Books Online:

  • BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups on secondary replicas
  • Differential backups are not supported on secondary replicas
  • BACKUP LOG supports only regular log backups on secondary replicas, the copy-only option is not supported
  • Secondary replicas must be in SYNCHRONIZED or SYNCHRONIZING state and be able to communicate with the primary

Your backup preferences, and I stress the word "preferences" are set up when configuring the availability group, or can be modified on an existing availability group. There is nothing that prevents you from running supported backup types on any of the replicas, although there is a mechanism that will take your preferences into consideration, which is discussed later in this tip.

The options for backup preferences are:

  • Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.
  • Secondary Only - Will backup on secondary with highest priority. If no secondary's are available, no backups will occur.
  • Primary - Backups will occur on the primary only, whichever instance that happens to be at the time of the backup.
  • Any Replica - Looks just at the backup priority and backups on the replica with the highest priority.

Below is how this is configured using SQL Server Management Studio. For my testing, CHAD-SQL1 and CHAD-SQL2 are set up with synchronous data movement and CHAD-SQL3 is set up with asynchronous data movement. Based on this configuration, I want backups to occur on either CHAD-SQL1 or CHAD-SQL2 since the risk of data loss is eliminated. Only as a last resort would I want to use CHAD-SQL3, which is why it has the lowest priority. You also have the ability to exclude a replica if that meets your architecture needs.

Backup Preferences for SQL Server AlwaysOn Availability Groups

Below is how the backup preferences are configured using T-SQL. You can see in the code where the backup preference is set as well as the backup priority.

 N'CHAD-SQL1' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL1.sqlchad.local:5022', 
 N'CHAD-SQL2' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL2.sqlchad.local:5022', 
 N'CHAD-SQL3' WITH (ENDPOINT_URL = N'TCP://CHAD-SQL3.sqlchad.local:5022', 

Once you have your backup preferences and priority established, then it is time to set up a SQL Server Agent Job to perform the backups. Thankfully Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings. Skeleton code for how this looks is below.

IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
  --Perform backup

Unfortunately there currently is no way to set backup preferences for full backups to one server and transaction log backups to another server. The backup preferences are for backups in general, not the backup type. This needs to be kept in the back of your mind when setting up your backups since secondary replicas only support full backups with COPY_ONLY as well as transaction log backups. The backup plan that is architected has to stay within these boundaries if you are using secondary replicas for your backups. Another thing to keep in mind if you start mixing backups from different replicas (take full backup from one node, and transaction log backups from another node) that there GUI cannot reproduce a restore chain as it looks only locally into MSDB system tables. The node that has the transaction log backups will know nothing about the full backup taken from the other node. My personal preference is to write backups from all nodes to the same file share and to put the server name, database name, and datetime stamp into the file name. That way you know where the backups originated from and can easily formulate a restore chain if needed.

Here is a script I use, and I setup a job that calls this code on every instance in the Availability Group. If it is not the preferred backup location, it will not do anything and will exit gracefully. This script can of course be further customized according to your needs.

CREATE PROCEDURE usp_BackupDatabaseAG 
 @DatabaseName SYSNAME, 
 @BackupPath VARCHAR(256),
 @BackupType VARCHAR(4)
DECLARE @FileName varchar(512) = @BackupPath + 
 CAST(@@SERVERNAME AS VARCHAR) + '_' + @DatabaseName
IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
 IF @BackupType = 'FULL'
  SET @FileName = @FileName + '_FULL_'+ 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.bak'
  SET @SQLcmd = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + 
   ' TO DISK = ''' + @FileName + ''' WITH COPY_ONLY ;'
  --PRINT @SQLcmd
 ELSE IF @BackupType = 'LOG'
  SET @FileName = @FileName + '_LOG_'+ 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
   REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.trn'
  SET @SQLcmd = 'BACKUP LOG ' + QUOTENAME(@DatabaseName) + 
   ' TO DISK = ''' + @FileName + ''' ;'
  --PRINT @SQLcmd
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, April 30, 2017 - 12:55:35 PM - Chad Churchwell Back To Top (55327)

 Raj -


You can still performa point in time restore with a copy only backup and tlogs.  copy only just bypasses resetting the differential bitmap that drives differential backups, but a copy only backup is still a valid full backup within SQL Server


Saturday, April 29, 2017 - 11:13:39 PM - Raj Back To Top (55311)

In the secondary replica, we have fullbackup with copy only and transaction log backup. 

We can not perform point in time restore with copy only full backup and all transaction log backups. There is no chain between full backups and transaction log backups right? Correct me, if I am wrong.

Tuesday, June 14, 2016 - 11:25:40 AM - Krish Back To Top (41690)



I selected the option 'PRIMARY' with my primary node having 60 priority and secondary with 50 priority.

On both the nodes i configured Full,Differential,Log Backup jobs.

Jobs are running fine on both the nodes.I hope backup action takes place through the primary only eventhough job runs on both the nodes.


Can you please let me know the safest way to verify that backup happens only on primary at any point of time.If failover happens,the backup should run on the primary node at that moment

Friday, June 10, 2016 - 6:58:36 PM - Chad Churchwell Back To Top (41661)

 Hey Krish -


It your backup preference is set to "ANY REPLICA", this specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

For reference



Friday, June 10, 2016 - 5:20:50 PM - Krish Back To Top (41660)

 If the backup preference is selected as ANY REPLICA.

Both nodes has 50-50 prority.


What happens in this situation...

(Its a 2 node configuration with synchronous commit)


Friday, June 5, 2015 - 10:15:40 AM - Wilton Picou Back To Top (37750)

Great information and good details.


Wilton Picou

Thursday, January 15, 2015 - 9:31:31 AM - Abdul Khan Back To Top (35954)

My client are using a 3rd party software which uses the @@servername to validate the licensing. I have now created a dedicated instance and renamed the servername to be the same on all 3 dones so when the 3rd party application selects the @@servername it will always return the same value and the license is validated. This is working fine, however I have justiced when i execute the following command it always returns 0:


select sys.fn_hadr_backup_is_preferred_replica('dbname')

i am using backup scripts and obvbiouosly not backing up the databases as all the replicas are returning 0 as the preferref replica for backup.

Has anyone come across this before and any suggestions.



Many thanks,



Friday, September 5, 2014 - 6:28:31 AM - Nakis Back To Top (34395)

When using CAST(@@SERVERNAME AS VARCHAR), then if we are refering to a named SQL Instance, then the output of @@SERVERNAME will be something like xxx\yyy. Therefore having the backslash (\) in the FileName will create problems.
Depending what information we wish to add in the FileName we can use:

Tuesday, February 4, 2014 - 5:39:51 AM - Ana Back To Top (29325)

What will happen if there are multiple secondaries and backup priority of all the replicas are set as same say 50 for all. How would SQL decide on which replica to perform the backup?

Tuesday, September 17, 2013 - 11:02:46 AM - Peter Back To Top (26832)

Does anyone have a sample script how to restore a database in a situation where transaction logs are taken from one node and full backups are taken from another? Like mentioned above. many thanks.

Friday, September 13, 2013 - 7:54:16 PM - Chad Churchwell Back To Top (26794)

In response to Bart, I mentioned the GUI does not build the restore chain, not that you could not restore manually.  The limitation is in the GUI when it normally rebuilds the restore chain for you

Tuesday, July 23, 2013 - 8:28:38 AM - Chad Churchwell Back To Top (25960)

If the transaction log backups are offloaded to a replica (not using the COPY_ONLY option as that is only required to offload FULL backups) it fill free space in all the log files.  Please see the following blog post


Monday, July 22, 2013 - 11:38:52 AM - SDC Back To Top (25939)

Quick question: is it sufficient to only do the log backups on one node? Specifically, does doing the backup on the one node prevent growth on all nodes? I am thinking the answer here is yes and that running log backups on all nodes would lead to an unholy mess of a log backup chain, but on the other hand notice a very large log in a DB for one of my Availability groups.


Many thanks

Monday, July 22, 2013 - 10:55:55 AM - Bart Back To Top (25938)

Your comment about mixing backups from different replicas for a restore is not true.  I am able to restore a backup with a full backup from the primary and transaction log backups from the secondary.  I even verified that I was getting the logged transactions that occurred after the full backup.

Also, realize that a "copy-only" backup is not part of a backup-chain.  Copy-only backups are stand-alone backups only.

Friday, March 15, 2013 - 9:50:42 AM - Sreekanth Back To Top (22816)

Nice! But you should have warned(While doing Full Backups on one Server and T-Log on the other) about risks involved for doing a Point in Time Restore if we loose a single T-Log Backup(s) residing on a different server :)



Tuesday, March 12, 2013 - 9:30:43 AM - Ranga Back To Top (22736) the idea of having the servername, dbname on the backups and always using the same file share!

Wednesday, February 13, 2013 - 9:54:04 AM - Shankar Back To Top (22100)

Very nice and informative article,thanks Chad...

get free sql tips
agree to terms