Copy-Only Backups: Protect the Differential Restore Plan

An engineer takes an extra full backup for a test environment. Later, the scheduled differential restore fails because the recovery plan still expects an older full backup. The problem is not that full backups break the transaction-log chain. The unexpected ordinary full changed the differential base.

Follow a concrete timeline

Imagine a scheduled full backup on Sunday, an ad hoc ordinary full on Tuesday, and a differential on Wednesday. The Wednesday differential is based on Tuesday's full, not Sunday's. If Tuesday's file exists only on a temporary share and disappears, a restore plan consisting of Sunday plus Wednesday is no longer sufficient.

If Tuesday's ad hoc full had been COPY_ONLY, it would not have changed the differential base. Wednesday's differential would still depend on Sunday's ordinary full. A copy-only full can be restored like a full backup, but it does not become the base for subsequent differential backups.

This is why COPY_ONLY is appropriate for many one-off full backups taken outside the managed schedule. It is not a reason to label every scheduled full copy-only. A differential strategy needs intentional ordinary full backups to establish its bases.

SELECT TOP(50) database_name,type,is_copy_only,
 backup_start_date,backup_finish_date,first_lsn,last_lsn,
 database_backup_lsn,differential_base_lsn
FROM msdb.dbo.backupset
WHERE database_name=N'YourPracticeDatabase'
ORDER BY backup_finish_date DESC;

The history query helps identify ordinary and copy-only backups and their LSN metadata. D, I, and L identify full database, differential database, and log backup types. Treat the query as investigation evidence, not an automatic restore planner that proves every media file is present.

Keep the log chain separate

Taking an ordinary full does not break an existing log backup chain. Logs remain necessary to reach the desired recovery point after the chosen data backup. Recovery-model changes and missing log media are different issues and should not be confused with the differential-base change.

A copy-only log backup also has special behavior: it preserves the normal log archive point and does not truncate the log. It is not a substitute for the routine log backups that support the established process. Taking a copy-only log repeatedly will not fix a log that needs normal backup-driven reuse.

When multiple tools perform backups, coordinate ownership. A vendor agent, maintenance job, and manual operator can all produce valid backup files while collectively creating a restore set that nobody has assembled. Centralize media inventory and retention around recoverability, not simply the success status of each independent job.

Verify the files the plan actually needs

Backup history can outlive deleted files, and a destination server may not contain the source server's msdb history. Inspect the actual backup headers and locate every stripe of a striped backup. A filename containing full or diff is not authoritative metadata. Confirm database identity, base relationships, sequence, and required encryption material.

For an ad hoc full, use a new destination name and the intended options. The example is a template for an existing practice database and a server-accessible path; substitute both deliberately. It avoids INIT so it does not instruct the reader to overwrite existing media.

BACKUP DATABASE [YourPracticeDatabase]
TO DISK=N'D:\Backups\YourPracticeDatabase_adhoc_unique_name.bak'
WITH COPY_ONLY,CHECKSUM,COMPRESSION,STATS=10;

Restore a representative scheduled full and its differential on an isolated destination after introducing an ad hoc copy-only backup. Then test the required log sequence to the target point. A successful backup or VERIFYONLY alone does not establish that the complete business recovery procedure works.

Retain backup bases for as long as dependent differentials remain part of the promised recovery window. Record which files and keys were used in a successful drill. The reliable outcome is an explicit, tested dependency chain that survives ordinary operational requests for extra backup copies.

Record the backup set position when several sets share one media file. A correct path alone does not identify the intended restore source.

Technical references: Microsoft Learn: Copy-only backups · Microsoft Learn: Backup history.