Why use BACKUP...WITH COPY_ONLY?

JustinL — Tue, 03 Jun 2008 17:49:00 GMT

New in SQL Server 2005 - this BACKUP option doesn’t appear especially interesting from the description in Books Online - however could be really handy in some scenarios. The benefit of using the COPY_ONLY option is that it doesn't break the backup chain - so won't disrupt the restore routine required for regular log, or differential backups.

Periodic refreshes of non-production environments is really useful to maintain consistency and enable testers to use live data for functional and performance testing. The time when the COPY_ONLY option could be useful is when an ad hoc full backup is required to refresh a development or pre-production environment. Typically environments refreshes are an ad hoc task - run only when needed. Often the refresh requires a backup taken from production, copied and restored to a development or test server and the .BAK file is deleted. However, if a T-SQL regular BACKUP DATABASE statement was used or a backup via Management Studio – the production database log chain will be disrupted. Without the .BAK file taken during this ad hoc backup - subsequent log and differential backup files are useless, (since they relate to the ad hoc full backup file).

The situation can only be overcome by retaining the ad hoc backup until the next full backup has completed. If a production restore were necessary, - this could add complication (and delay) as recovery documents could be inaccurate and restore scripts may not complete since the backup file name and location could be different from expected. This situation can be avoided by using the COPY_ONLY option when the ad hoc backup is initially taken. The option will take a full backup (option can also be used with log – although not with differential backups too) without disrupting the backup chain – and means any production restore processes are valid.

This backup option will is useful since it means ad hoc backups won’t affect database recoverability – and the .BAK files can be safely deleted once restored - avoiding a cluttered server!

Justin Langford

Coeo - The SQL Server Experts

> sum(its parts) : lsn

Why use BACKUP...WITH COPY_ONLY?