SQL Server Copy-Only Backup Script for Dev Restore

calendar Apr 20, 2026 / · 6 min read · sql server backup recovery copy only backup t-sql database administration msdb sql scripts dev restore  ·
Share on: linkedin copy

A copy-only backup is a special SQL Server backup that is independent of the normal backup sequence. It does not reset the differential base or interrupt the transaction log backup chain, making it the correct choice when you need an ad-hoc backup for a development or test environment restore without affecting production recovery options.

Purpose and Overview

In a standard SQL Server backup strategy, full backups establish a new differential base and log backups form a continuous chain. If you take an unplanned full backup mid-schedule, the next differential backup becomes larger than expected because the base changed. If you take an unplanned log backup, you may break an automated restore chain that depends on log sequence numbers.

Copy-only backups solve this problem. A COPY_ONLY full backup captures all data pages but does not update the differential base. A COPY_ONLY log backup captures the log but does not mark VLFs as inactive, leaving the log chain intact for regular log backups to continue normally.

This post covers three scripts:

  1. Take a copy-only full backup of a single database
  2. Take a copy-only log backup without truncating the active log
  3. Query msdb to confirm a backup was taken with is_copy_only = 1

Code Breakdown

Script 1: Copy-Only Full Backup

1-- Copy-only full backup — does not change the differential base
2BACKUP DATABASE [YourDatabase]
3TO DISK = N'D:\Backups\YourDatabase_CopyOnly.bak'
4WITH
5    COPY_ONLY,
6    COMPRESSION,
7    STATS = 10,
8    NAME = N'YourDatabase — Copy-Only Full Backup';
9GO

The COPY_ONLY option is the only change from a standard BACKUP DATABASE statement. It flags the backup in msdb.dbo.backupset with is_copy_only = 1 and skips updating the differential base LSN stored in the database boot page. Every other aspect of the backup — data completeness, file format, restore compatibility — is identical to a normal full backup. The resulting file can be used for a complete database restore.

COMPRESSION reduces the file size and is recommended unless the destination storage is CPU-constrained. STATS = 10 prints a progress message every 10% of the backup operation, which is useful for monitoring large database backups.

Script 2: Copy-Only Log Backup

1-- Copy-only log backup — does not truncate the log or advance the log chain
2BACKUP LOG [YourDatabase]
3TO DISK = N'D:\Backups\YourDatabase_CopyOnly_Log.bak'
4WITH
5    COPY_ONLY,
6    COMPRESSION,
7    STATS = 10,
8    NAME = N'YourDatabase — Copy-Only Log Backup';
9GO

A copy-only log backup captures the current log contents without marking virtual log files (VLFs) as reusable for overwrite. The log sequence number (LSN) chain visible to subsequent scheduled log backups is not advanced, so automated log shipping or restore chains continue from where they were before the copy-only log backup was taken.

This is useful when a developer needs a current log backup to restore to a specific point in time on a test server, but the production log backup schedule must not be disrupted.

Script 3: Verify Copy-Only Status in Backup History

 1-- Confirm the backup was flagged as copy-only in msdb
 2SELECT TOP 20
 3    database_name,
 4    backup_start_date,
 5    backup_finish_date,
 6    CASE type
 7        WHEN 'D' THEN 'Full'
 8        WHEN 'I' THEN 'Differential'
 9        WHEN 'L' THEN 'Log'
10        ELSE type
11    END                                                         AS backup_type,
12    is_copy_only,
13    CAST(backup_size / 1024000.0 AS DECIMAL(18, 2))            AS uncompressed_mb,
14    CAST(compressed_backup_size / 1024000.0 AS DECIMAL(18, 2)) AS compressed_mb,
15    name                                                        AS backup_name
16FROM msdb.dbo.backupset
17WHERE database_name = N'YourDatabase'
18ORDER BY backup_finish_date DESC;

The is_copy_only column in msdb.dbo.backupset is 1 for copy-only backups and 0 for standard backups. This query verifies that the ad-hoc backup was recorded correctly and can be used to audit which backups in the history were taken outside the scheduled maintenance window.

Key Benefits and Use Cases

  • Allows developers or DBAs to take an ad-hoc full backup for dev or test restore without changing the production differential base
  • Prevents accidental enlargement of the next scheduled differential backup
  • Enables point-in-time log backups for test servers without interrupting the production log chain
  • Fully compatible with native SQL Server restore — RESTORE DATABASE treats a copy-only file identically to a regular full backup
  • The is_copy_only flag in msdb provides a clear audit trail showing which backups were taken outside the regular schedule

Performance Considerations

  • Copy-only backups are not lighter than full backups: they read every allocated data page in the database. Duration and IO load are identical to a normal full backup.
  • Do not use copy-only as a substitute for a scheduled full backup: if the copy-only flag is misused to bypass the maintenance plan, the differential base will be older than expected and differentials will grow larger over time.
  • Compression applies normally: use WITH COMPRESSION to reduce file size and write IO just as you would with any backup.
  • Differential backups after copy-only: the differential base is unchanged, so the next scheduled differential still measures changes from the last non-copy-only full backup. This is the intended behavior.

Practical Tips

To restore a copy-only backup to a development server:

1-- Restore a copy-only full backup on a dev server
2RESTORE DATABASE [YourDatabase_Dev]
3FROM DISK = N'D:\Backups\YourDatabase_CopyOnly.bak'
4WITH
5    MOVE N'YourDatabase'     TO N'D:\Data\YourDatabase_Dev.mdf',
6    MOVE N'YourDatabase_log' TO N'D:\Data\YourDatabase_Dev.ldf',
7    REPLACE,
8    STATS = 10;
9GO

To apply a copy-only log backup on top of the copy-only full backup:

 1-- First restore the full backup with NORECOVERY
 2RESTORE DATABASE [YourDatabase_Dev]
 3FROM DISK = N'D:\Backups\YourDatabase_CopyOnly.bak'
 4WITH NORECOVERY, REPLACE;
 5GO
 6
 7-- Then apply the copy-only log backup
 8RESTORE LOG [YourDatabase_Dev]
 9FROM DISK = N'D:\Backups\YourDatabase_CopyOnly_Log.bak'
10WITH RECOVERY;
11GO

When scripting this for a team workflow, store the backup file in a network share accessible to the dev server and parameterise the database and file paths as variables at the top of the script so developers can run it without editing the body of the SQL.

Conclusion

Copy-only backups are the correct tool for any ad-hoc backup taken outside the scheduled maintenance window. The COPY_ONLY option costs nothing in backup quality or file compatibility while protecting the production differential base and log chain from unintended modification. Use them as standard practice whenever creating backups for developer access, migration testing, or one-off restore requests.

References

Posts in this series