Differential Backup After a COPY_ONLY Full: How the Restore Chain Really Works

The Scenario

A fairly ordinary backup schedule:

• 12:00 — Full backup
• 01:00 — Log backup
• 02:00 — Differential backup
• 03:00 / 04:00 — Log backups
• 05:00 — COPY_ONLY full backup
• 06:00 onward — Log backups
• Later — Differential backup, then a final log backup

Now the 12:00 full backup is corrupted. The obvious, textbook-safe restore path is: COPY_ONLY full → every log backup since. That always works, because it never needs a differential at all. But a second, less obvious sequence also restores cleanly:

COPY_ONLY full (05:00) → log backup (06:00) → differential backup (later) → log backup (later)

That should not work. A COPY_ONLY full backup is explicitly documented to not reset the differential base — meaning that "later" differential is still based on the 12:00 full, the one backup file that never enters this restore chain at all. So how does SQL Server validate it?

Two Independent Checks, Not One

It is easy to assume there is a single "backup chain" that RESTORE validates. In reality there are two separate, unrelated checks, and this scenario only makes sense once they are pulled apart.

1. Log chain continuity (FirstLSN / LastLSN)

Every log backup records a FirstLSN and LastLSN. To apply one, its FirstLSN must line up with wherever the database's redo currently stands. This check is purely sequential and has nothing to do with which full or differential backup sits underneath — it is why "COPY_ONLY full → every log since" always works on its own.

2. Differential base match (DatabaseBackupLSN + GUID)

A differential backup only applies on top of the exact full backup it was based on. That relationship is recorded, not inferred: every backup stamps a DatabaseBackupLSN (visible via RESTORE HEADERONLY, and as database_backup_lsn in msdb.dbo.backupset) plus a differential_base_guid. When a differential is restored, SQL Server compares those values against what the currently restored database is carrying — not against whichever full backup file happens to be sitting on disk.

What COPY_ONLY Really Does to the Differential Base

Every database tracks one "current differential base" pointer (visible via DBCC DBINFO as dbi_differentialBaseLSN / dbi_differentialBaseGuid). A regular full backup does two things: it copies all the data, and it resets that pointer to itself, becoming the new base for every differential taken afterward.

A COPY_ONLY full backup captures exactly the same data — but by design it does not reset that pointer. Microsoft's own description of the feature is understated but precise: it exists to take a backup "without affecting the overall backup and restore procedures for the database." Two consequences follow, and only the first one is generally well known:

• On the source database: any differential taken after a COPY_ONLY full still records the original full as its base. This is the commonly cited behavior.
• On restore: restoring that COPY_ONLY full does not make it the new base either. It carries forward whatever the base pointer was at the moment it was captured — the 12:00 full's LSN and GUID — and stamps that onto the restore target.

The second point is the whole answer. Restoring the 05:00 COPY_ONLY full does not tell the target database "your base is me." It tells it "your base is still the 12:00 full" — a backup file the restore sequence never touches, because the pointer travels as metadata inside the copy-only backup, independent of whether the original file still exists.

Proof: Real Values From a Restore Test

Theory is cheap; here are the actual numbers from reproducing this exact nine-step sequence on a SQL Server 2022 instance and querying msdb.dbo.backupset:

The COPY_ONLY full's own checkpoint_lsn is 361, its own point in time. But its database_backup_lsn — the base it records itself as carrying — is still 276, the 12:00 full. The later differential, taken well after the copy-only full existed, is stamped with the identical base LSN and the identical GUID as the 02:00 differential. It never references the copy-only backup at all.

The Restore Itself

-- Never references the corrupted 12:00 full anywhere below:

RESTORE DATABASE MyDb
  FROM DISK = 'DiffDemo_06_CopyOnlyFull.bak'
  WITH NORECOVERY;

RESTORE LOG MyDb
  FROM DISK = 'DiffDemo_07_Log.trn'
  WITH NORECOVERY;

RESTORE DATABASE MyDb                 -- the differential, based on the
  FROM DISK = 'DiffDemo_08_Diff.bak'  -- 12:00 full, restores cleanly
  WITH NORECOVERY;                    -- against the copy-only chain

RESTORE LOG MyDb
  FROM DISK = 'DiffDemo_09_Log.trn'
  WITH RECOVERY;

Correctness, not just permission, follows from how a differential backup is built: it is cumulative, holding the latest version of every page changed since the base, as of its own backup time — not as of the intermediate copy-only full. Restoring it on top of the copy-only full simply overwrites those pages with their more current state. Which specific full backup file's bytes happen to be on disk never enters into it; only the LSN and GUID bookkeeping has to line up.

The Boundary Case

Practical Takeaway

• Log restores and differential restores are validated by two unrelated mechanisms: LSN continuity for logs, base LSN/GUID matching for differentials.
• A COPY_ONLY full backup is deliberately excluded from the differential base chain in both directions: taking one does not reset the live pointer, and restoring one does not establish a new one.
• A differential backup restores against whatever the currently restored database's base pointer says, not against whichever full backup file is physically present. Losing the original full does not automatically strand a later differential, as long as a COPY_ONLY full sits between them in the restore chain.
• Query msdb.dbo.backupset (database_backup_lsn, differential_base_guid, is_copy_only) before assuming a differential is unusable — the base it actually needs may already be sitting in a full backup you still have.

The one lesson worth carrying forward: COPY_ONLY is not "a full backup that behaves slightly differently." It is a full backup that is deliberately invisible to the differential chain, in exactly the way that makes this restore sequence work.