SQL SERVER – Different Size of COPY_ONLY Full Backup on Primary and Secondary Replica in Always On

While preparing for a disaster recovery demo for my client, I observed an interesting behavior. In this blog, I would show a quick demo where you can learn how the full backup size can be different on the primary and secondary replica for an availability database in Always On availability group.

Here is the quick script to understand the behavior.

1. Create a Database and take a full backup and create a wide table (8KB char column)

   USE master
   GO
   CREATE DATABASE SQLAGDB
   GO
   BACKUP DATABASE SQLAGDB TO DISK = 'SQLAGDB.bak'
   GO
   USE SQLAGDB
   GO
   CREATE TABLE SQLAuthority (j CHAR(8000))
   GO
   

2. Configure AlwaysOn Availability Group using SSMS – Always On Wizard for database SQLAGDB. Makes sure it is healthy and secondary in sync.
3. Insert lots of rows in the table. You can use the below script

   -- connect to database 
   USE SQLAGDB
   GO
   -- insert 100 rows
   SET NOCOUNT ON
   GO
   INSERT INTO SQLAuthority
   VALUES ('A') 
   GO 100
   -- 
   SET NOCOUNT ON
   GO
   -- insert from same table to same table (8 times)
   -- total rows should be 100+100+200+400+800+1600+3200+6400+12800
   INSERT SQLAuthority
   SELECT *
   FROM SQLAuthority 
   GO 8
   SELECT COUNT(*) from SQLAuthority -- 25600
   

4. Take COPY_ONLY backup from both replicas.

   --primary 
   :connect sqlserver-0
   BACKUP DATABASE SQLAGDB TO DISK = '\\sqlserver-0\share\primary_before_log_backup.bak'
   WITH copy_only,format
   GO
   -- secondary 
   :connect sqlserver-1
   BACKUP DATABASE SQLAGDB TO DISK = '\\sqlserver-0\share\secondary_before_log_backup.bak'
   WITH copy_only,format
   GO
   

   Here is the output
   Connecting to sqlserver-0…
   Processed 25944 pages for database ‘SQLAGDB’, file ‘SQLAGDB’ on file 1.
   Processed 28629 pages for database ‘SQLAGDB’, file ‘SQLAGDB_log’ on file 1.
   BACKUP DATABASE successfully processed 54573 pages in 6.755 seconds (63.115 MB/sec).
   Disconnecting connection from sqlserver-0…
   Connecting to sqlserver-1…
   Processed 25944 pages for database ‘SQLAGDB’, file ‘SQLAGDB’ on file 1.
   Processed 2 pages for database ‘SQLAGDB’, file ‘SQLAGDB_log’ on file 1.
   BACKUP DATABASE successfully processed 25946 pages in 2.259 seconds (89.729 MB/sec).
   Disconnecting connection from sqlserver-1…
   Notice processed pages for ‘SQLAGDB_log’.

5. Let’s take a log backup (on any replica)

   BACKUP LOG [SQLAGDB] TO  
   DISK = N'\\sqlserver-0\share\Log-backup.trn' 
   WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
   GO
   

   Processed 28630 pages for database ‘SQLAGDB’, file ‘SQLAGDB_log’ on file 1.
   BACKUP LOG successfully processed 28630 pages in 3.533 seconds (63.309 MB/sec).

6. Now take the same full backup on both replicas.

   --primary 
   :connect sqlserver-0
   BACKUP DATABASE SQLAGDB TO DISK = '\\sqlserver-0\share\primary_after_log_backup.bak'
   WITH copy_only,format
   GO
   -- secondary 
   :connect sqlserver-1
   BACKUP DATABASE SQLAGDB TO DISK = '\\sqlserver-0\share\secondary_after_log_backup.bak'
   WITH copy_only,format
   GO
   

   and here is the output:
   Connecting to sqlserver-0…
   Processed 25944 pages for database ‘SQLAGDB’, file ‘SQLAGDB’ on file 1.
   Processed 4 pages for database ‘SQLAGDB’, file ‘SQLAGDB_log’ on file 1.
   BACKUP DATABASE successfully processed 25948 pages in 3.182 seconds (63.706 MB/sec).
   Disconnecting connection from sqlserver-0…
   Connecting to sqlserver-1…
   Processed 25944 pages for database ‘SQLAGDB’, file ‘SQLAGDB’ on file 1.
   Processed 2 pages for database ‘SQLAGDB’, file ‘SQLAGDB_log’ on file 1.
   BACKUP DATABASE successfully processed 25946 pages in 2.365 seconds (85.707 MB/sec).
   Disconnecting connection from sqlserver-1…
   

7. A picture is worth a thousand words:
   

This situation would come when we don’t perform log backup and T-Log continues to grow. From above we can understand that full backup with copy_only has different logic to take a portion of the log file on primary and secondary. Interesting enough the additional size (208 + 229 = 437) is equal to LDF size of the database file. Once log backup is taken there is no additional log file to be backed up and size became the same. I hope you learn about Different Size of COPY_ONLY Full Backup on Primary and Secondary Replica in Always On.

Reference: Pinal Dave (https://blog.sqlauthority.com)