One of the types of SQL Server database backup is the copy-only backup. In reality, it’s not a different type of backup like differential or transaction log backups. Rather, it is an option that does not change the backup itself. Instead, it affects the sequencing of how regular backups are tracked and restored.
Why are full copy-only backups needed?
You only need full copy-only backups if you have regular differential backups configured. Differential backups are unique because they only include changes made since the last full backup — specifically, the last full backup not marked by any special label. This is where the problem arises, and it’s easier to explain with an example.
Imagine you have automated full backups every Sunday, and on the other days, you run differential backups at midnight.
If something goes wrong, you know the process:
- Open the backup storage
- Find the differential backup you need
- Locate the full backup that was created before it
- Now you’re ready to restore the database
Cool! But here’s where the problem comes up: suppose you (or a colleague) need to make a full backup during the week, perhaps for testing on another server. If you make a regular full backup, then for future restores, you will need that exact full backup you made for testing. Most likely, this backup won’t be stored in the usual backup storage location.
This is where copy-only full backups come in. They do not interfere with the existing backup sequence. It’s as if they don’t exist for the regular backups. The differential backup will continue to reference the full backup from Sunday, ensuring your backup and restore strategy remains intact.
Best practice for copy-only backups
Always use copy-only backups when you need a backup for purposes other than your regular automated backups. This includes tasks like migration, testing or auditing. For regular scheduled backups, however, avoid using copy-only backups.
Copy-only doesn’t hide: it’s all in the logs
The fact that copy-only backups don’t affect the backup sequence doesn’t mean that SQL Server “forgets” about them. These backups are stored in a special table within the master database, as well as in the SQL Server logs. Below is more information on how to check the backup history.
Create copy-only backups via SQL Server Management Studio
In SSMS, you can mark a backup as copy-only by selecting the checkbox in the backup creation interface:
Create copy-only backups via SQL
To create a copy-only backup, simply add WITH COPY_ONLY:
BACKUP DATABASE [Pranas.Events] TO DISK = N’//Path-to-backup-storage’ WITH COPY_ONLY
For more details on SQL Server backups using T-SQL commands, refer to the guide.
Create copy-only backups via Powershell
If you’re creating a backup via PowerShell, simply use the -CopyOnly parameter in the backup command:
Backup-SqlDatabase -ServerInstance ‘SalesServer’ -Database ‘Sales’ -BackupFile ‘E:\BAK\Sales_Copy.bak’ -CopyOnly
For more information, check out our separate article on SQL Server backups via the command line.
Create copy-only backup via SqlBackupAndFTP
To create a backup with SqlBackupAndFTP, select the copy-only option in the backup settings.
How to restore a copy-only backup
This is done exactly the same way as any other backup. The copy-only backup file works like conventional SQL Server backups. You can restore it using SSMS or T-SQL commands. For more details on restoring SQL Server backups.
Copy-only log backup
Two types of backups use the copy-only option:
- Full copy-only
- Transaction log copy-only
Earlier in the article, we discussed the full copy-only backup. In general, the transaction log copy-only backup works the same way.
For example, if your full backup takes a long time, you can create a transaction log copy-only backup immediately after. This will capture any changes that occurred during the full backup. The transaction log copy-only backup does not truncate the database’s transaction log, nor does it alter the sequence of regular SQL transaction log backups.
Conclusion
Regular SQL Server backups are an independent process and shouldn’t be impacted by your daily tasks. It doesn’t matter how you set up your regular backups — whether using SQL Server Agent, SQLBackupAndFTP, SqlBak or custom scripts. Copy-only backups are a useful tool for keeping your regular backup routines separate from backups taken for specific tasks.