Copy-Only Backups

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

• Copy-only full backups (all recovery models)

  A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.

• Copy-only log backups (full recovery model and bulk-logged recovery model only)

  A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore. For an example of this, see Example: Online Restore of a Read/Write File (Full Recovery Model).

The transaction log is never truncated after a copy-only backup. Copy-only backups are recorded in the is_copy_only column of thebackupset table.

To create a copy-only backup (Transact-SQL)

Note

Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups.

• How to: Back Up a Database (SQL Server Management Studio)

• How to: Back Up a Transaction Log (SQL Server Management Studio)

• The essential Transact-SQL syntax for a copy-only full backup is:

  BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …

  Note

  COPY_ONLY has no effect when it is specified with the DIFFERENTIAL option.

• The essential Transact-SQL syntax for a copy-only log backup is:

  BACKUP LOG database_name TO <backup_device> … WITH COPY_ONLY …

Restoring a Copy-Only Backup

Restoring a copy-only full backup is the same as restoring any full backup.

Important

When restoring a database backup onto a different server instance, see Copying Databases with Backup and Restore and Managing Metadata When Making a Database Available on Another Server Instance.

To restore a full database backup

• How to: Restore a Database Backup (Transact-SQL)

• How to: Restore a Database Backup (SQL Server Management Studio)

To restore files and filegroups

• How to: Restore Files and Filegroups (Transact-SQL)

• How to: Restore Files and Filegroups (SQL Server Management Studio)

See Also

Reference

BACKUP (Transact-SQL)

RESTORE (Transact-SQL)

Concepts

Backup Under the Full Recovery Model

Backup Under the Simple Recovery Model

Recovery Model Overview

Change History

Updated content
Revised to indicate that the SQL Server Management Studio Backup task supports creating a copy-only backups.