Full backups, the log chain and the COPY_ONLY option.

There have been a large number of posts made regarding the interaction between full database backups and the log chain (like this one). However there still seems to be a lot of confusion around regarding how they interact. I doubt I can clear up the confusion with yet another blog post, but I’ll try.

One that seems to come up now and again is the COPY_ONLY Option on backups. Typically the comment is something like ‘Use the COPY_ONLY option when taking ad-hoc full backups so that you don’t impact the log backups.’ Now we know from the blog posts linked above that full backups don’t ever break the log chain (and I’m not going to run yet more tests to prove it) so what is the copy only option there for?

Books Online states the following regarding the COPY_ONLY option for backups – “Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.”

Well, that doesn’t clear things up much. It does however go on to say this: “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.”

So it’s not the log chain that copy only is there to not affect, it’s the differential base. Let’s test and see how it works.

CREATE DATABASE TestingBackups
GO

USE TestingBackups
GO

CREATE TABLE Testing (
ID INT IDENTITY PRIMARY KEY,
SomeValue CHAR(4)
);
GO

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Full1.bak'
GO

INSERT INTO Testing (SomeValue)
VALUES ('abc')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Diff1.bak' WITH DIFFERENTIAL
GO

INSERT INTO Testing (SomeValue)
VALUES ('def')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Full2.bak'
GO

INSERT INTO Testing (SomeValue)
VALUES ('ghi')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH DIFFERENTIAL
GO

Right, so that’s a database with two full backups and two differential backups and a couple data changes in between. Let’s drop the database and test some restore strategies. The goal is to restore the database to the point that it was at the time the second differential backup was taken.

First the obvious route. Full backup 2 and differential backup 2.

RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Full2.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups WITH RECOVERY

That works. That’s the most obvious and likely the one that will be used the most. What happens though if we don’t have full backup 2? What if it was a full backup that an evil developer took without asking, used to restore a development/test database somewhere and then deleted the backup file?

There are two options there that might work:

• Full backup 1 and differential backup 2 (differential backups are cumulative aren’t they?)
• Full backup 1, differential backup 1 and then differential backup 2.

Option 1

RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Full1.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups WITH RECOVERY

Well that didn’t work…

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Option 2

RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Full1.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Diff1.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups WITH RECOVERY

That didn’t work either.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Differential backup 1 restored fine, differential backup 2 didn’t. The reason is that, unlike log backups, differential backups are affected by full backups. Specifically, a differential backup is based on the last full backup that ran before it. If an ad-hoc full backup is taken, any differential run after that is based on the ad-hoc full backup and not the one run as part of the standard backup plan. Not fun if a developer runs one then deletes the backup file when it’s no longer needed.

This is what copy-only affects when specified on a full backup. A full backup run with copy-only does not change the differential base and does not reset the list of extents changed since the last full backup. Hence an ad-hoc full database backup, if specified with the COPY_ONLY option, won’t cause administrators unpleasant surprises when test restores are done or a disaster occurs and a full restore is necessary.

Let’s drop that test database and recreate it using COPY_ONLY on the second full backup.

CREATE DATABASE TestingBackups
GO

USE TestingBackups
GO

CREATE TABLE Testing (
ID INT IDENTITY PRIMARY KEY,
SomeValue CHAR(4)
);
GO

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Full1.bak'
GO

INSERT INTO Testing (SomeValue)
VALUES ('abc')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Diff1.bak' WITH DIFFERENTIAL
GO

INSERT INTO Testing (SomeValue)
VALUES ('def')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Full2.bak' WITH COPY_ONLY
GO

INSERT INTO Testing (SomeValue)
VALUES ('ghi')

BACKUP DATABASE TestingBackups TO DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH DIFFERENTIAL
GO

Now the restore path using full backup 1 and differential backup 2 does work.

RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Full1.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups FROM DISK = 'C:\Databases\Backup\TestingBackups_Diff2.bak' WITH NORECOVERY
GO
RESTORE DATABASE TestingBackups WITH RECOVERY

Processed 168 pages for database ‘TestingBackups’, file ‘TestingBackups’ on file 1.
Processed 5 pages for database ‘TestingBackups’, file ‘TestingBackups_log’ on file 1.
RESTORE DATABASE successfully processed 173 pages in 0.215 seconds (6.286 MB/sec).
Processed 64 pages for database ‘TestingBackups’, file ‘TestingBackups’ on file 1.
Processed 1 pages for database ‘TestingBackups’, file ‘TestingBackups_log’ on file 1.
RESTORE DATABASE successfully processed 65 pages in 0.103 seconds (4.873 MB/sec).
RESTORE DATABASE successfully processed 0 pages in 0.371 seconds (0.000 MB/sec).

With that out of the way, the question still remains as to whether COPY_ONLY has anything to do with log backups or the log chain. Books online also says this about them: “When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.”

So COPY_ONLY changes the effect a backup has on the log chain only when specified on a transaction log backup, not on full databases backups. Useful if you need an ad-hoc log backup for something.