Backup & Recovery from First Principles, Part 4: Copy-Only Backups, the Chain-Safe Snapshot
Part 3 ended with a rule: one tool owns log backups, and everything else takes COPY_ONLY. This part explains what copy-only backups actually change, proves it from msdb, and then builds the pattern that makes them genuinely valuable: the pre-deployment safety net.
This is part 4 of the backup and recovery series (part 1, part 2). Demos ran on SQL Server 2019 (CU32) and behave identically on SQL Server 2025 (RTM).
What COPY_ONLY Actually Changes
A normal full backup has a side effect people forget: it becomes the new differential base. Every differential taken afterward is measured against it. Take an ad-hoc full backup at 2 PM to copy production to a dev box, and tonight’s differential silently bases itself on your 2 PM file, the one that is now on a dev server, or deleted. The nightly restore chain has a dependency nobody knows about.
A copy-only full backup skips that side effect. It reads the same pages and produces an equally restorable file, but it does not reset the differential base.[1] Similarly, a copy-only log backup reads log records without truncating them or advancing the chain, so the regular log backup sequence never notices.
Proof from the demo. A nightly full at 13:42:20, a copy-only full at 13:42:21, then a differential:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/* nightly chain base */ BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\co_nightly_full.bak' WITH INIT, COMPRESSION; /* ad-hoc, chain-safe */ BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\co_predeploy.bak' WITH COPY_ONLY, INIT, COMPRESSION; /* tonight's differential */ BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\co_diff_after.bak' WITH DIFFERENTIAL, INIT, COMPRESSION; SELECT [bs].[backup_set_id] , [bs].[type] , [bs].[is_copy_only] , [bs].[differential_base_lsn] , [bs].[backup_start_date] FROM [msdb].[dbo].[backupset] AS [bs] WHERE [bs].[database_name] = N'bkrec_demo' ORDER BY [bs].[backup_set_id]; |
|
1 2 3 4 5 |
type | is_copy_only | differential_base_lsn | backup_start_date -----|--------------|-----------------------|-------------------- D | 0 | NULL | 2026-07-21 13:42:20 D | 1 | NULL | 2026-07-21 13:42:21 I | 0 | 723000018761600001 | 2026-07-21 13:42:23 |
The differential’s base LSN points at the nightly full, not the copy-only that was taken between them. The copy-only was invisible to the chain, which is the entire point. (Part 2 covers how to read these LSN columns.)
One honest caveat: a copy-only full cannot serve as a base for differentials. If you restore from the copy-only, you restore it plus log backups, never it plus a differential. For the pattern below, that is exactly what we want anyway.
The Pre-Deployment Safety Net
Here is the pattern that earns copy-only backups a permanent place in your deployment runbook. Before any risky change, an application release with schema changes, a large data correction, a “quick” hotfix, take an ad-hoc copy-only full and record the time:
|
1 2 3 4 5 |
BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\co_predeploy.bak' WITH COPY_ONLY, INIT, COMPRESSION; SELECT SYSDATETIME() AS [pre_deployment_time]; |
|
1 2 3 |
pre_deployment_time --------------------------- 2026-07-21 13:42:21.3427817 |
The nightly chain is undisturbed. The regular log backups keep running. Nothing about the standing recovery strategy changed. What you gained: a restore that starts from seconds before the deployment instead of from last night’s full plus a day of log.
Now the deployment goes wrong, as our demo deployment obligingly does:
|
1 2 3 |
UPDATE [dbo].[app_config] SET [setting_val] = 'on' WHERE [setting_name] = 'feature_flag_x'; UPDATE [dbo].[app_config] SET [setting_val] = 'premium' WHERE [setting_name] = 'billing_mode'; DELETE FROM [dbo].[app_config] WHERE [setting_name] = 'api_timeout_ms'; /* oops */ |
The Rollback
Because the copy-only is a full backup like any other, you have two restore shapes, and the log chain (still intact, still owned by the nightly sequence) works with both:
Shape 1: side-by-side salvage. Restore the copy-only as a new database and pull the damaged rows across. Zero downtime for everything the deployment did not break:
|
1 2 3 4 5 6 7 8 |
RESTORE DATABASE [bkrec_demo_rollback] FROM DISK = N'C:\temp\bkrec\co_predeploy.bak' WITH MOVE N'bkrec_demo' TO N'C:\temp\bkrec\bkrec_rollback.mdf' , MOVE N'bkrec_demo_log' TO N'C:\temp\bkrec\bkrec_rollback.ldf' , RECOVERY; SELECT [setting_name], [setting_val] FROM [bkrec_demo_rollback].[dbo].[app_config]; |
|
1 2 3 4 5 6 7 8 |
/* the restored copy: pre-deployment state */ feature_flag_x off api_timeout_ms 5000 billing_mode standard /* live database: the damage */ feature_flag_x on billing_mode premium |
Shape 2: full rewind. If the deployment corrupted things beyond row-level repair, restore the copy-only WITH NORECOVERY over the database, apply the log backups taken since (the ones the nightly sequence kept producing), and STOPAT the recorded pre-deployment time. That is a standard point-in-time restore, walked through step by step in part 5, with the copy-only serving as a much closer starting point than last night’s full.
Operational Notes
- Record the timestamp with the backup.
SYSDATETIME()into the deployment ticket. A rollback target you have to guess at is barely a rollback target. - Copy-only is also the answer for “can I get a copy of prod?” Every ad-hoc full for a dev refresh, an audit, or a migration test should be
COPY_ONLY, so the real chain never acquires secret dependencies. - SSMS has a checkbox for it (“Copy-only backup” on the Back Up Database dialog), so this works fine as a manual pre-change step for teams that do not script their deployments. Better: put it in the deployment pipeline so it cannot be forgotten.
- Retention: a pre-deployment copy-only is disposable by design. Keep it until the deployment is verified good, then delete it. It is not part of the recovery chain and your restore automation should not treat it as one;
is_copy_only = 1in msdb makes filtering easy.
Next: part 5 walks the full restore sequence – full, differential, log, and the STOPAT rewind, including the errors you hit when you get the order wrong.
Does your deployment process take a safety-net backup? Comments below, or find me on Bluesky or LinkedIn.
References
- Copy-Only Backups (SQL Server) – Microsoft Learn. What copy-only changes for full and log backups, and the differential-base caveat. ↩