Microsoft Learn

Documentation

Copy-Only Backups (SQL Server)

Applies to: SQL Server 2008 and later versions.

A copy-only backup is a SQL Server backup that is independent of the regular sequence of backups. When you create a copy-only backup, the next regular backup operation is not affected. For example, a copy-only BACKUP LOG does not truncate the log chain. This means that a copy-only backup does not interfere with the existing backup plan, such as the ability to restore to a specific point in time.

Why Use Copy-Only Backups?

  • Create an ad-hoc backup without affecting the backup chain: If you need to perform a backup for testing, development, or for a specific operational need, a copy-only backup ensures that your standard backup and restore strategy remains intact.
  • Create a backup before making significant changes: Before performing major maintenance, upgrades, or configuration changes on your database, creating a copy-only backup provides a safe point-in-time recovery option without disrupting your regular backup schedule.
  • Generate a standalone backup: Copy-only backups are useful when you need a backup that can be restored independently of the rest of the backup history.

How to Create Copy-Only Backups

Using Transact-SQL

To create a copy-only backup using Transact-SQL, use the COPY_ONLY option in the BACKUP statement.

-- Full Copy-Only Backup
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName_CopyOnly_Full.bak'
WITH COPY_ONLY;

-- Differential Copy-Only Backup (requires a prior full backup)
BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName_CopyOnly_Diff.bak'
WITH DIFFERENTIAL, COPY_ONLY;

-- Log Copy-Only Backup
BACKUP LOG YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName_CopyOnly_Log.trn'
WITH COPY_ONLY;

Using SQL Server Management Studio (SSMS)

  1. In Object Explorer, connect to the instance of the SQL Server Database Engine that contains the database you want to back up.
  2. Expand Databases.
  3. Right-click the database you want to back up, point to Tasks, and then click Back Up....
  4. In the Back Up Database dialog box, verify the database name.
  5. Select Full, Differential, or Transaction Log as the Backup type.
  6. In the Destination section, specify the backup location (e.g., a disk file).
  7. Click the Options page.
  8. Under the Media Options section, select Do not add to existing backups if you want the backup to be the only one on the media.
  9. In the Copy-Only Backup section, check the Perform copy-only backup box.
  10. Click OK to create the copy-only backup.

Considerations

  • Copy-only backups do not affect the differential base or the log chain.
  • A copy-only full backup can be used as the starting point for new differential backups or log backups.
  • When restoring a copy-only backup, the restore process is the same as for a regular backup.
  • Make sure to clearly label your copy-only backups to distinguish them from your regular backups.

Supported Backup Types for Copy-Only

The following backup types support the COPY_ONLY option:

  • Full Database Backup
  • Differential Backup
  • Transaction Log Backup
  • File or Filegroup Backup

Note that the COPY_ONLY option is not applicable to differential or log backups that are created after a copy-only full backup. These subsequent backups are considered part of the new backup sequence initiated by the copy-only full backup.

Related Topics