| title | Copy-Only Backups (SQL Server) | Microsoft Docs | |||
|---|---|---|---|---|
| ms.custom | ||||
| ms.date | 06/13/2017 | |||
| ms.prod | sql-server-2014 | |||
| ms.reviewer | ||||
| ms.technology | backup-restore | |||
| ms.topic | conceptual | |||
| helpviewer_keywords |
|
|||
| ms.assetid | f82d6918-a5a7-4af8-868e-4247f5b00c52 | |||
| author | MikeRayMSFT | |||
| ms.author | mikeray | |||
| manager | craigg |
A copy-only backup is a [!INCLUDEssNoVersion] backup that is independent of the sequence of conventional [!INCLUDEssNoVersion] 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 [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell.
- On the General page of the Back Up Database dialog box, select the Copy Only Backup option.
The essential [!INCLUDEtsql] syntax is as follows:
-
For a copy-only full backup:
BACKUP DATABASE database_name TO <backup_device*>* ... WITH COPY_ONLY ...
[!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 ...
Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.
Backup Overview (SQL Server)
Recovery Models (SQL Server)
Copy Databases with Backup and Restore
Restore and Recovery Overview (SQL Server)