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.

12 Responses to “Full backups, the log chain and the COPY_ONLY option.”

  1. Very useful stuff. I was working under the assumption that it had a greater affect on the log. Thanks for the post!

  2. Nicely written.I thought to write this in my blog you done it.
    I am going to show this in my junior DBA’s,because they are always going for full bak to restore the DB to another server.

  3. Gail, Where to do the email subscription.

  4. Great explanation and demonstration of the effects and uses of the copy only option

  5. Gail
    Thanks for nice article. Don’t you think that the line “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.” is incorrect? As you mentioned, copy_only affects only differential backup base and it has no impact on log chain. Pls clarify.

  6. No, I think it’s totally correct. Read the second-last paragraph for Copy_only ON log backup (as opposed to ON a database backup)

  7. Brilliant! Thanks, this has confused me for years!

  8. I just came across this post. I’m sorry I don’t know the name of the person who wrote it. Is it Gail Shaw? I learned a lot from this post. I do full backups & transaction log backups, so I have never run into this problem with differentials. I did manual full backups with copy_only, & the database in simple backup mode in order to truncate tran logs. I would then switch back to full backup mode for the automatic backups. I thought I could avoid breaking the tran log chain. Now I understand what copy_only on full backups actually does. Thank you.

  9. Yup, you’ve got the author right.

    If you switch to simple recovery, you break the log chain immediately, no matter what you do in simple recovery.

  10. [...] USE MASTER GO BACKUPDATABASE DBA_TEST TODISK=‘D:BACKUPDBA_TEST.BAK’ with copy_only [...]

  11. One slight variation I’ve found. I changed a database from SIMPLE to FULL recovery. I then ran a full backup with COPY_ONLY. I then ran a log backup. The log backup failed due to the COPY_ONLY clause on the full backup – it would not use it as the base full backup.

  12. I’ll have to investigate that. If that’s the case, that has some profound implications for the distributed backups that SQL 2012′s availability groups can do.

Leave a Reply