SQL Server Backup Database on Secondary Replicas

Backup Types Supported on Secondary Replicas

  • BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not supported on secondary replicas.
  • BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

  • To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.
--truncate table numbers
--select * from numbers

--verify backup and recovery mode to full then set AG


create database rep7
go


use rep7
go

create table numbers
(num int)

insert into numbers  
values (1000)
go 1000


--on secondary replica. Run this command or create a job

BACKUP DATABASE [rep7] 
TO  DISK = N'C:\backup 777\fullbackup777.bak' 
WITH  COPY_ONLY,    --<< backups on secondary replicas occurs only if you set this option (must be copy_only)
NOFORMAT, 
NOINIT,  
NAME = N'rep7-Full Database Backup'
GO

Going through the Availability Groups options, we can notice that we have distinct backup options:

The available options can be confusing, if you never tried them before. So let’s take a look on each one…

Prefer Secondary

This option is very conceptual. Basically, you can run the backup command from any replica!

Automated backups for the availability groups should occur on secondary replica (…)”

We can easily test this by running a backup command from the Primary replica:

 BACKUP DATABASE [AdventureWorks2016] TO  DISK = N’E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016.bak’ WITH STATS = 10GO 

The result:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 2.
100 percent processed.
Processed 25 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 2.
BACKUP DATABASE successfully processed 24345 pages in 39.265 seconds (4.843 MB/sec).

Yes, it worked! As it would work from any other replica, being a secondary or not! But, what is the reason behind this? As I said before, the keywords are “Automated” and “should”.

Translating this, we can understand that “Should” would mean “yes, this would be good to run in a secondary. If not, that’s ok…”.

What about the “Automated” keyword? This case is different. SQL Server assumes that when you run a BACKUP DATABASE command, like we did, you know what you are doing. Basically, you set the backup rules, so if you are doing the backup on the primary replica, you assume the risk. In other hand, an automated backup would be done by using the beloved SQL Server Maintenance Plan. Let’s analyse this:

I created a simple Maintenance Plan, with a backup task including all the databases in the instance. You can notice that there’s a warning in the bottom of the window.

“This backup type is not supported on a secondary replica and this task will fail if the task runs on a secondary replica.”

Why this? Because we are including databases that are part of an Availability Group in the plan. The problem here is simple: SQL Server does not support a regular backup to be made on Secondary replicas. In this case we are dealing with the primary one, but in case of a failover, the backup job is going to fail!

How can we solve this? We need to go to the tab “Options” and tick the “Copy-only backup” option:

For more information about this you can check the Microsoft documentation on Active Secondaries: Backup on Secondary Replicas

Going back to the “Automated” keyword, if we press the “View T-SQL” button, we will be able to explore the code generated to execute the backups. Based on my lab, I’ll get the following, for the AdventureWorks2016 database:

 DECLARE @preferredReplica INT  SET @preferredReplica = (SELECT [master].sys.Fn_hadr_backup_is_preferred_replica(‘AdventureWorks2016′))  IF ( @preferredReplica = 1 )   BEGIN       BACKUP DATABASE [AdventureWorks2016] TO DISK = N’E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_23_192837_3837451.bak’    WITH copy_only, noformat, noinit, NAME =     N’AdventureWorks2016_backup_2015_11_23_192837_3837451′, skip, rewind,     nounload, stats = 10 END  

Code starts with the declaration and value attribution of a variable called “preferredReplica”:

 DECLARE @preferredReplica INT  SET @preferredReplica = (SELECT [master].sys.Fn_hadr_backup_is_preferred_replica(‘AdventureWorks2016’))  

By running the select, present in code, I will get the following :

This matches with the configuration, as we defined that this Availability Groups would backup from the Secondary replica and, in this case, we are in the Primary replica. So the following function returns 1 if you are in the preferred replica to perform backups, and 0 if not:

 [master].sys.Fn_hadr_backup_is_preferred_replica(‘<DATABASE_NAME>’) 

Continuing with the code analysis, we have now an IF controlling the execution of the actual BACKUP DATABASE command:

 IF ( @preferredReplica = 1 )   BEGIN       <BACKUP CODE…>END  

Here is the point where we prove that the “Automation” is just conceptual. Basically, the SQL Server Maintenance Plan, is AG aware, and it has the backup task ready to interpret the backup options of the Availability Group. Knowing this, what happed if we run a simple (without IFs) BACKUP DATABASE command in the primary? It’s going to work! So, don’t be “eluded” thinking that set the backup preferences is enough… There are more things to do!

One of my suggestions is use the heavily tested and community approved scripts from Ola Hallegren, which are Availability Groups aware, and very smart and flexible!

Secondary Only

Continuing with the analysis, we can now check the second option “Secondary Only”, which the description states that the backups MUST occur on a Secondary Replica. Let’s test it:

First, connect to the primary replica, I’ll try to execute a BACKUP DATABASE command:

 BACKUP DATABASE [AdventureWorks2016] TO  DISK = N’E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016.bak’ WITH STATS = 10GO 

The output:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 3.
100 percent processed.
Processed 27 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 3.
BACKUP DATABASE successfully processed 24347 pages in 12.018 seconds (15.827 MB/sec).

Also See:

SQL Server 2019