| title | Copy-Only Backups | Microsoft Docs | |||
|---|---|---|---|---|
| description | A copy-only backup is a SQL Server backup that is independent of the sequence of SQL Server backups. It does not affect how later backups are restored. | |||
| ms.custom | ||||
| ms.date | 01/30/2019 | |||
| ms.prod | sql | |||
| ms.prod_service | backup-restore | |||
| ms.reviewer | ||||
| ms.technology | backup-restore | |||
| ms.topic | conceptual | |||
| helpviewer_keywords |
|
|||
| ms.assetid | f82d6918-a5a7-4af8-868e-4247f5b00c52 | |||
| author | MashaMSFT | |||
| ms.author | mathoma | |||
| monikerRange | =azuresqldb-mi-current||>=sql-server-2016||>=sql-server-linux-2017 |
[!INCLUDE SQL Server SQL MI]
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.
Important
In Azure SQL Managed Instance copy-only backup cannot be created for a database encrypted with service-managed Transparent Data Encryption (TDE). Service-managed TDE uses internal key for encryption of data, and that key cannot be exported, so you could not restore the backup anywhere else. Consider using customer-managed TDE instead to be able to create copy-only backups of encrypted databases, but make sure to have encryption key available for later restore.
You can create a copy-only backup by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell.
In this example, a copy-only backup of the Sales database will be backed up to disk at the default backup location.
-
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
-
Expand Databases, right-click
Sales, point to Tasks, and then click Back Up.... -
On the General page in the Source section check the Copy-only backup checkbox.
-
Click OK.
This example creates a copy-only backup for the Sales database utilizing the COPY_ONLY parameter. A copy-only backup of the transaction log is taken as well.
BACKUP DATABASE Sales
TO DISK = 'E:\BAK\Sales_Copy.bak'
WITH COPY_ONLY;
BACKUP LOG Sales
TO DISK = 'E:\BAK\Sales_LogCopy.trn'
WITH COPY_ONLY;Note
COPY_ONLY has no effect when specified with the DIFFERENTIAL option.
This example creates a copy-only backup for the Sales database utilizing the -CopyOnly parameter.
Backup-SqlDatabase -ServerInstance 'SalesServer' -Database 'Sales' -BackupFile 'E:\BAK\Sales_Copy.bak' -CopyOnlyTo create a full or log backup
To view copy-only backups
To set up and use the SQL Server PowerShell provider
Backup Overview (SQL Server)
Recovery Models (SQL Server)
Copy Databases with Backup and Restore
Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL)
Backup-SqlDatabase