Export (0) Print
Expand All

Copy-Only Backups (SQL Server)


Applies To: SQL Server 2016

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. Copy-only backups serve this purpose.

The types of copy-only backups are as follows:

  • Copy-only full backups (all recovery models)

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

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

  • 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 a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful 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 the backupset table.

You can create a copy-only backup by using SQL Server Management Studio, Transact-SQL, or PowerShell.

Using SQL Server Management Studio

  1. On the General page of the Back Up Database dialog box, select the Copy Only Backup option.

Using Transact-SQL

The essential Transact-SQL syntax is as follows:

  • For a copy-only full backup:

    BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …

    System_CAPS_ICON_note.jpg Note

    COPY_ONLY has no effect when specified with the DIFFERENTIAL option.

  • For a copy-only log backup:

    BACKUP LOG database_name TO <backup_device> … WITH COPY_ONLY …

Using PowerShell

  1. Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.

To create a full or log backup

To view copy-only backups

To set up and use the SQL Server PowerShell provider

Bookmark link 'Top' is broken in topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"f82d6918-a5a7-4af8-868e-4247f5b00c52","entity_type":"Article","locale":"en-US"}'. Rebuilding the topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"f82d6918-a5a7-4af8-868e-4247f5b00c52","entity_type":"Article","locale":"en-US"}' may solve the problem.

Backup Overview (SQL Server)
Recovery Models (SQL Server)
Copy Databases with Backup and Restore
Restore and Recovery Overview (SQL Server)

Community Additions

© 2016 Microsoft