Copy-Only Backups (SQL Server)
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.
To Create a Copy-Only Backup
You can create a copy-only backup by using SQL Server Management Studio, Transact-SQL, or PowerShell.
Using SQL Server Management Studio
- 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
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
- Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.
Related Tasks
To create a full or log backup
To view copy-only backups
To set up and use the SQL Server PowerShell provider
[Top]