What is COPY_ONLY?

SQL Server Best Practices
SQL Server Best Practices
You’ll frequently hear that you should add the COPY_ONLY clause when taking an ad-hoc full backup to avoid messing up the backup chain. Just in case you haven’t: You should add the COPY_ONLY clause when taking an ad-hoc full backup to avoid messing up the backup chain.

But you may be asking why? What exactly does COPY_ONLY do?

Let’s start with the BOL definition.

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

And an exerpt from the BOL definition of the BACKUP DATABASE command.

When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.

Let me make a quick note that I’m going to concentrate on using COPY_ONLY with FULL backups although it can also be used with LOG backups.

So if we look at that second excerpt we get some interesting information:

  • COPY_ONLY FULL backups can not be used as the base to restore differentials.
    Ie we can’t restore a COPY_ONLY full backup, then restore a differential on top of it. It doesn’t say anything about logs, though, so that should be possible, but let’s test that in a minute.
  • The differential bitmap is not updated.
    Which begs the question “What’s the differential bitmap?” Well, simplifying a bit, it’s what tells SQL what data needs to be copied into a differential backup.

Continue reading on SQLStudies.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *