Copy-Only Backups (SQL Server)

A copy-only backup is a SQL Server backup that is taken independently of the normal sequence of conventional backups. A copy-only backup is always a full backup, regardless of the backup type of the conventional backup that precedes it (full, differential, or log).

Copy-only backups are used to create a backup that does not interfere with the regular backup routine of the database. This is useful in situations where you want to back up a database for a special purpose, such as for a disaster recovery test or for a temporary purpose, without affecting the backup chain.

When to Use Copy-Only Backups

• Disaster Recovery Testing: Create a standalone backup for testing your disaster recovery plan without disrupting your production backup schedule.
• Creating a Snapshot: Take a backup before a major change or upgrade to have a clean restore point.
• Troubleshooting: Generate a backup of a specific state of the database for analysis.
• Migration or Cloning: Create a self-contained backup that can be easily restored to another server for migration or cloning purposes.

Features and Benefits

• Independent Backup Chain: Copy-only backups do not advance the differential base or clear the log. This means they do not affect subsequent full, differential, or log backups.
• Always Full: Even if the last backup was a differential or log backup, a copy-only backup is always a full backup.
• Simplifies Restore Scenarios: Because they are independent, copy-only backups can be restored without needing to consider the normal backup sequence.

Syntax for Copy-Only Backups

You can create a copy-only backup using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

Using Transact-SQL (T-SQL)

To create a copy-only full backup:

BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_CopyOnly_Full.bak'
WITH COPY_ONLY, NOINIT, NOUNLOAD, NAME = N'YourDatabaseName-Copy-Only Full Backup', STATS = 10;
GO

To create a copy-only differential backup (note: copy-only backups are always full by nature of their definition, this syntax will create a full backup marked as copy-only):

-- A copy-only backup is always a full backup and does not affect the differential base.
-- The following command effectively creates a full copy-only backup.
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_CopyOnly_Full.bak'
WITH COPY_ONLY, NOINIT, NOUNLOAD, NAME = N'YourDatabaseName-Copy-Only Backup', STATS = 10;
GO

Using SQL Server Management Studio (SSMS)

1. In Object Explorer, connect to an instance of the SQL Server Database Engine.
2. Expand the Databases node.
3. Right-click the database that you want to back up.
4. Point to Tasks, and then click Back Up....
5. In the Back Up Database dialog box, select Full as the backup type.
6. Under Backup Options, select the Copy-only backup checkbox.
7. Click OK to create the backup.

Understanding the Impact on Backup Chains

Conventional backups follow a sequence:

• A full backup is taken.
• A differential backup can be taken, which is based on the last full backup.
• Transaction log backups can be taken, which are based on the last backup (full or differential).

A copy-only backup does not participate in this chain. It acts as a standalone snapshot:

• If you take a copy-only backup, the next conventional full backup will still be considered the base for subsequent differential backups.
• The transaction log will not be truncated by a copy-only backup, even if it's the only backup taken recently.

Considerations

• Copy-only backups still consume disk space and should be managed as part of your overall storage strategy.
• Ensure you have a clear naming convention for copy-only backups to differentiate them from your regular backups.
• Regularly review your backup strategy to ensure it meets your business continuity and recovery objectives.