Rotating Header Image

COPY_ONLY Backups

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



Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

4 Comments

  1. ob says:

    Hi Colin,

    I do native log shipping. How should I do full backups, with or without copy_only?

    Thabk you

  2. If you’re intent is to use the full backup as part of your disaster recovery solution inconjunction with your transaction log backups then you CANNOT use the copy_only flag.

    COPY_ONLY backups will not be referenced by subsequent transaction log backups.

    Hope this helps clarify.

  3. App-wise, Android is better than ever, and the smartphone battle is now a two-horse race between iOS and Android. In fact, I’ve struggled to find any app or service not available on Android.

  4. LifeProof helps you get the most out of your iPhone and our latest designs incorporate some exciting changes that make the user experience the best we’ve ever delivered

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.