In order to understand how to operate the “COPY_ONLY” option and its nature, let’s consider the following example. Assume that a full database backup starts at 00:00 once a day and a differential backup starts every six hours. In the picture below you can see what database changes (marked as pages) are included into each of database backups shown on the picture.
Assume that there is a need to make an extra full database backup to retrieve it on another server. Suppose that this backup is done after the database change #4. If the full backup is made without T-SQL option “COPY_ONLY”, the differential backup that was made after such full backup will no longer contain changes #1, #2, #3 and #4:
The picture above shows that the full backup that was made after the database change #4 has disrupted the backup schedule set by the user (marked as green arrows). The following two differential backups that were made according to the schedule at 12:00 and 18:00 completely depended on the last extra full backup (marked as red arrows). If the last full database backup that was made after database change #4 is not saved and the database failure occurs, the best way out is to restore the database to the database change#3 state (06:00). All changes that are highlighted in red will be lost.
To make a full database backup and thus keep backups schedule safe and sound, it is necessary to use “COPY_ONLY” option. In this case, the database backup sequence will not be broken and the full backup will be available.
Copy-only Transaction Log Backup
You can use the COPY_ONLY option with transaction log backups as well (note though that they are suitable for full or bulk-logged recovery models). If you need to make an extra transaction log backup and not to break the backup chain, then use the “WITH COPY_ONLY” option, otherwise if the backup file is lost the restore process will not be possible. It has to be admitted that the “WITH NO_TRUNCATE” option does not truncate the transaction log.
How to Make a Copy-only Backup
To make a copy-only backup simply add “COPY_ONLY” clause to BACKUP DATABASE command:
BACKUP DATABASE your_database TO DISK = 'full.bak' WITH COPY_ONLY
Another way of making a copy-only backup is using SQL Server Management Studio (SSMS): right-click on the database you want to backup, select “Tasks”, then “Back up…”. Select type option as “Full”, check the “Copy-only backup” checkbox, add backup destination, and then click “OK”.
If you use SQLBackupAndFTP to schedule your database backups, please click “more settings…” and scroll down to the “Backup options”, then check off “Copy Only”.
