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)

Menu
Exit mobile version