COPY_ONLY Full Backup Size: What Differs Between Always On Replicas

A COPY_ONLY full backup can differ in size between the primary and the secondary replica of an Always On group. The data part is the same. The log part is not. The backup messages show it, and they are the first place to look.

What the Two Replicas Reported

In a two-replica test, a database held one table with 25,600 rows of 8,000 bytes. No log backup had run. A COPY_ONLY full backup on each replica reported the same 25,944 data pages. The log pages differed: 28,629 on the primary and 2 on the secondary.

Then a log backup ran, and the same two backups ran again. The primary now reported 4 log pages and the secondary still reported 2. The size gap was gone, and the data had not changed.

This test needs two replicas, so the numbers are not reproducible on a single server. The next sections show what you can measure on one.

What a Full Backup Contains

A full backup copies the data pages. It also copies enough of the log to bring the copy to a consistent state when you restore it. The messages list both parts: one line for the data file and one for the log file. The line for the log file tells you how much log went into the backup.

COPY_ONLY changes none of that. It only keeps the backup from becoming the base of later differential backups. On a secondary replica, a COPY_ONLY full backup is the only kind of full backup you can take.

Look at the Log Part on One Server

The demo uses a database in the full recovery model and a table of wide rows. The backups go to the NUL device, so they write no file. NUL discards the data, so it is only for a test and never a backup. The first backup is not COPY_ONLY. A full recovery database needs one to start its log chain.

USE master;
GO
IF DB_ID(N'CopyOnlyLogDemo') IS NULL CREATE DATABASE CopyOnlyLogDemo;
GO
ALTER DATABASE CopyOnlyLogDemo SET RECOVERY FULL;
GO
USE CopyOnlyLogDemo;
GO
DROP TABLE IF EXISTS dbo.WideRows;
CREATE TABLE dbo.WideRows (Filler char(8000) NOT NULL);
GO
BACKUP DATABASE CopyOnlyLogDemo TO DISK = N'NUL' WITH INIT;

SET NOCOUNT ON;
INSERT INTO dbo.WideRows
SELECT TOP (3000) 'A' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

Now take a COPY_ONLY full backup. The log has about 3,000 rows of changes that no log backup has covered.

BACKUP DATABASE CopyOnlyLogDemo TO DISK = N'NUL' WITH COPY_ONLY;

The Messages tab shows lines like these.

Processed 3496 pages for database 'CopyOnlyLogDemo', file 'CopyOnlyLogDemo' on file 1.
Processed 1 pages for database 'CopyOnlyLogDemo', file 'CopyOnlyLogDemo_log' on file 1.
BACKUP DATABASE successfully processed 3497 pages in 0.041 seconds (666.194 MB/sec).

The data file line counts 3,496 pages, and the log line counts 1. On this standalone database, the unbacked log did not go into the full backup. Now back up the log, which processes the pages in the log file, and take the COPY_ONLY backup again.

BACKUP LOG CopyOnlyLogDemo TO DISK = N'NUL';
GO
BACKUP DATABASE CopyOnlyLogDemo TO DISK = N'NUL' WITH COPY_ONLY;

On one server, the full backup carries one log page both times. So a large log part is not the normal case for a standalone database. The test does not show why the primary includes more log, so no cause is claimed here. On one server, the log backup changes nothing in the full backup: 1 log page both times. The two-replica numbers above are the only place where a log backup closed a gap.

Compare Backups Fairly

Compare two backups only when the log state is the same. Take a log backup first, and then take both COPY_ONLY backups close together. Read the log line of each backup before you read the total size. The history in msdb keeps the size of each backup. A query can compare them later.

SELECT b.backup_finish_date,
       b.is_copy_only,
       CONVERT(decimal(12,2), b.backup_size / 1048576.0) AS BackupMB
FROM msdb.dbo.backupset AS b
WHERE b.database_name = N'CopyOnlyLogDemo' AND b.type = 'D'
ORDER BY b.backup_finish_date DESC, b.backup_set_id DESC;

The two COPY_ONLY backups have the same size, 29.09 MB. The first backup, taken before the insert, is 3.02 MB because the table was empty. Dates and sizes will differ on your server. Also read the log reuse wait of the database. The value AVAILABILITY_REPLICA means that a replica is holding back the log.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'CopyOnlyLogDemo';

The size gap does not by itself mean damage. Prove a backup with a test restore on another server, not with its size.

You could argue that the size difference proves the secondary backup is incomplete. The data pages say otherwise, because both backups carry 25,944 of them. The difference sits in the log part.

What to Remember

Read the messages of a COPY_ONLY full backup before you compare sizes. Data pages should match, and the log pages can differ. In the two-replica test, a log backup before the comparison removed the difference.

Keep a regular log backup job on the group, and test a restore from each replica. When you finish the demo, run the cleanup script. It removes the demo database and its backup history.

USE master;
GO
IF DB_ID(N'CopyOnlyLogDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CopyOnlyLogDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CopyOnlyLogDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'CopyOnlyLogDemo';

A smaller backup is not a damaged backup, it is a backup with less log in it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.