Export (0) Print
Expand All

Copy-Only Backups (SQL Server)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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 …

    Note 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

[Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft