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.

So to put that in simple terms. I have a database, Test. I take a full backup, changes happen, I take a differential backup, changes happen, I take a differential backup, etc. Ignoring all of the log backups that are happening if the database is in FULL recovery of course.

Currently, both differentials contain everything that has happened between the time of the full backup and the time the differential was taken. But what happens if I take a second full backup between the first and second differential? Now, that second differential will only contain data between the second full backup and the differential.

Sounds normal right? But why do we care? Well, let’s say that that second full backup was not part of our regular backup cycle. Let’s further say that this ad-hoc backup was put in a different location than the regular backups, and then deleted once it was no longer needed. What about that second differential? Or the next one? Now they aren’t completely useless (we will get into that in a future post, soon I promise) but restores just got a whole lot more complicated. We can’t restore that first full backup and then the second differential any more. And if that’s what’s expected, what we have scripted, then it could cause a real headache if we actually have a disaster.

So to avoid that we use COPY_ONLY on the second full backup. Now it has no effect on the differentials and our original logic holds. The second differential has all of the information between when it was taken and the first full. Our planning, and scripts, are still in effect. Making a disaster, maybe not less of a disaster, but hopefully not more.

And again, I’m going to point out, because this is important, it has no effect whatsoever on the log backups.

Ok, so we know from earlier that a COPY_ONLY full backup can’t have a differential applied to it, but what about a log? Well, let’s give it a shot.

First setup

-- Run an initial full, a COPY_ONLY full and a log backup
BACKUP DATABASE Test TO DISK = 'C:\backups\Test.BAK' WITH COMPRESSION, INIT;
GO
USE Test;
GO
INSERT INTO LoadTable VALUES ('a','Yes');
GO
BACKUP DATABASE Test TO DISK = 'C:\backups\Test_CO.BAK' WITH COMPRESSION, COPY_ONLY;
GO
INSERT INTO LoadTable VALUES ('c','No');
GO
BACKUP LOG Test TO DISK = 'C:\backups\Test.TRN' WITH COMPRESSION;
GO

Now test

RESTORE DATABASE Test_CO FROM DISK = 'C:\backups\Test_CO.BAK' 
WITH NORECOVERY,
MOVE 'Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CS\MSSQL\DATA\Test_CO.mdf',
MOVE 'Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CS\MSSQL\DATA\Test_CO_log.ldf';
RESTORE LOG Test_CO FROM DISK = 'C:\backups\Test.TRN' WITH RECOVERY;

And it worked!

So a COPY_ONLY full backup can be used as the base for a log backup but not a differential. Good to know!

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, microsoft sql server