In SQL 2005 a COPY_ONLY option became available for backing up your databases. What COPY_ONLY does is take a backup (full or t-log) without affecting the backup/restore cycle. This is a great new feature as quite often DBAs are asked for an out of sequence backup to be taken to restore to a different environment. Usually when this type of request comes in once that backup is restored to it’s destination the backup file is useless and should be deleted.
Before COPY_ONLY if you deleted the backup file taken and your database went down you could be in quite a bad situation as even though your backup was taken in an ad-hoc fashion it is/was still very much a part of the restore sequence and could be needed to recovery to a point in time.
Somethings to be aware of with COPY_ONLY:
- A T-Log backup using the COPY_ONLY functionality will not truncate the log after it’s completion. The current log archive point stays the same.
- A differential backup using the COPY_ONLY functionality is no different than a regular differential backup
- A Full backup using the COPY_ONLY functionality does not affect any existing (or new) differential backups and it cannot be used as a base backup.
New to SQL 2008 is that the COPY_ONLY functionality is now available in the SQL Server Management Studio. In SQL 2005 this was only a option for the users who did their backup and restore commands using T-SQL.
If you’re interested in reading more about COPY_ONLY backups here is the Microsoft Link
