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.