Unused Secret Weapon: COPY_ONLY Backups

[UPDATE: This post contains INCORRECT content in terms of the scope/nature of the problems described. While COPY_ONLY backups ARE useful to protect against ‘extra’/’accidental’ backups, this post incorrectly targets TLOG backups instead of DIFF backups. See more about why this is incorrect here.]

COPY_ONLY backups were a great addition to SQL Server 2005 (and up). Without them, it was all to easy for DBAs, devs, and SysAdmins to create a ‘backup’ for dev/testing purposes which would actually, silently, break the Log Chain and put production data in severe risk.

In other words, if:
a) A Non COPY_ONLY backup was made,
b) A new or scheduled FULL/DIFFERENTIAL backup hadn’t yet been made, and
c) A database disaster occurred

Then DBAs _HAD_ to have that non-copy only backup on hand or they wouldn’t be able to recover from the disaster properly – because they’d be missing the proper baseline from which to begin applying transaction log backups.

What’s So Secret?

As I’ve mentioned before, the notion of breaking the log chain can be a bit counter-intuitive. (Seriously, backing something up can actually BREAK it? That’s not something you expect to find with many systems. Yet, if you understand what’s going on and WHY the log file is so essential to SQL Server, then the fact that you CAN break the log chain becomes something that is not only second nature – but which you’re constantly vigilant against.)

Yet, while I think that increasing numbers of DBAs (including even reluctant DBAs) have a decent understanding of the benefits of COPY_ONLY backups in terms of protecting the log chain, I think that the overwhelming majority of DBAS don’t realize that log file backups can ALSO be used against COPY_ONLY backups as well.

Therefore, since a picture is worth a thousand words, here’s a diagram that I think increasing numbers of DBAs understand and get – in terms of why COPY_ONLY backups are so important.


Figure 1: Why it’s so important to use COPY_ONLY Backups (or keep non-COPY_ONLY backups on hand until next FULL/DIFF backup).

On the other hand, while the benefits of COPY_ONLY backups are understood from the sole standpoint of PROTECTING the log chain for a PRODUCTION database, Figure 2 shows that there are also some other, very important, benefits that come from COPY_ONLY backups as well.


Figure 2: With COPY_ONLY backups, you can easily set up MULTIPLE copies that can remain synchronized with Log File Backups.

And this benefit, in turn, is the secret weapon – as we’ll see in a couple of upcoming posts because this can have HUGE implications when it comes to various types of high-availability situations or even in terms of disaster recovery purposes as well.

And, the fact that DBAs rarely use this benefit of COPY_ONLY databases is really what makes it an Unused Secret Weapon – as we’ll see in subsequent posts.

Discuss this Blog Entry 8

on Nov 23, 2011
I did the same test as rxmoore and his is right. Your article is confusing, because otherwise we need to change our backup strategy for whole company.
on Nov 28, 2011
Thanks for the comments everyone. I think I'll address them a bit more with a follow-up post - because this stuff IS confusing. And, the peril REALLY is there. The big thing that I think you are all missing is that you have to 'lose' access to that additional/secondary FULL backup. In my previous post about this problem the assumption was that some 3rd party backup solution was making this FULL (or differential) backup for you - and you didn't 'know' about it - or wouldn't remember it in a crisis. In which case, any log files applied after that backup was taken would NOT be able to be applied. Same thing in this case, IF you or a developer or SOMEONE/SOMETHING else takes a 'test' backup in the middle of the day and you NO LONGER have access to that file/backup, then you're dead in the water and you're NOT able to restore t-logs after that point until the next FULL/DIFFERENTIAL backup when the LSN is re-seeded and the backup chain is restarted. Again, a bit counter-intuitive - and I potentially (sadly) muddled that a bit with my diagram by NOT specifically calling out that the 2ndary backup has to be gone/missing. BUT, the key point is still the same: you can't apply T-Log backups to a FULL/DIFFERENTIAL backup after a non-COPY_ONLY backup has been made. (If you have access to that non-COPY_ONLY backup, then you're FINE as you can use it. BUT if it's GONE and you no longer have access to it, you'll be hating life.) Paul Randall covers a pretty technical example of this here: http://sqlskills.com/BLOGS/PAUL/post/BACKUP-WITH-COPY_ONLY-how-to-avoid-breaking-the-backup-chain.aspx I think I'll follow up in a day or so with a more simple example that readers can try for themselves in order to be able to better see this problem in action. Regards, --Mike
on Nov 23, 2011
This is the second article that you have posted that I've read that is confusing to me with regards to breaking the log chain. The copy_only backup feature is there to prevent the breaking of the chain between full backups and differential backups. Since the differential backup ties specifically to the last FULL backup. You can successfully restore transaction logs to any full backup as long as order is maintained. Please see the following example which works under any scenario where differentials aren't being used. 1. FULL backup 2. insert into a table 3. LOG backup 4. insert into a table 5. FULL backup 6. insert into a table 7. LOG backup I can restore either steps 1 & 3 & 7 (which is the log after the 2nd full backup) without any issue at all I can also restore steps 5 & 7 which brings me to the same state. The begins_log_chain value in msdb..backupset is only set to 1 for the 1st transaction log backup unless the recovery_model is changed. I'd like to know under what scenarios with the given example that the long chain would be broken that would prevent me from being able to restore the transation logs. The 1st pictured example leads one to believe that logs 3-5 couldn't be restored to the initial full backup which isn't the case.
on Nov 28, 2011
Sigh. No sooner than had I posted my previous comment did I notice this disclaimer in Paul's post: [Edit: In the initial version of this post, the DBA's backup strategy included log backups. I went through a couple of versions of this post before settling on full + diffs, but I forgot to remove the reference to log backups. In the first comment, Mark House correctly points out that an accidental full backup doesn't prevent a DBA with a complete log backup chain from recovering to any point in time. Apologies for the confusion!] Which, frankly, is news to me - as I could swear I've been burned by this in the past (i.e., with transaction logs). Even worse is that I spent some decent time testing this out in two test environments to make sure that my statements about the use of log-files was correct. Sadly, I just tested that FULL/COPY_ONLY + TLOG backups worked - which was a moot point (as they'll always work). What I should have tested was whether they'd work WITHOUT the COPY_ONLY test going on. Or, stated differently, I was WRONG. So, I'll have to post a follow-up/correction.
on Nov 28, 2011
@rxmoore No worries on the cross post. I'm obviously pretty bummed that I've a) presented bad info here, b) carried along an INCORRECT understanding of the basics for so long. (I could swear I've been burned by t-log problems here in the past - and have, accordingly, always assumed that they were at risk of this issue. And so, this is just one of those cases where an incorrect assumption (on my part) can slowly blossom over time into almost paranoia - where I've always stayed SAFE by being a bit 'extra' cautious even though that extra caution wasn't warranted.) So, thanks a ton for calling me out on this - I really appreciate learning that I was wrong. (I just wish I had known BEFORE this post ;) ) Still trying to figure out the best way to salvage this post and the basics of this thought/cautionary-tale. --Mike
on Nov 28, 2011
I guess I have to applogize as well as my last post overlapped yours. I appreciate the fact that you tested out the scenarios and have come to the same conclusion. Thanks much for your update.
on Nov 23, 2011
Agree with rxmoore. I've tested that same solution and worked perfectly if you have only Full and Tlog backups. As long as you have the whole set of Tlogs you can restore with any of your full backups not necessarily the last one. I can assure this because I had to set many times mirroring accross datacenters on different continents for DBs around 500 GB which takes at least in my network a couple of days to copy... and I have copy a full backup and while it was being copied another full backup took place on the source instance... however I was able to restore everything using the 1st backup and all the tlogs that followed that.. and set the mirror.. all these without using the COPY_ONLY option in any case....
on Nov 28, 2011
I'm sorry, but I still have to disagree and point back to my original post. It doesn't matter that you have taken a 2nd full backup or several full backups nor does it matter if a 3rd party utility was used. It also has nothing to do with whether or not that backup has been lost. Any transaction logs taken after any subsequent full backup can still be applied to any previous full backup as if the other full backup(s) were never taken as long as all of the logs between the 1st full backup and the time you need to recover to still exist. The pertains to only transaction logs and can't be done using differential backups as those are directly tied to the previous non-copy only full backup. You can take Nightly full backups every night and hourly transaction log backups during the day and you could restore Sunday nights full backup and every hourly log through Friday and not have any issues with LSNs. The reference to Pauls blog is completely different as it's using nothing but full backups and differential backups. It has nothing to do with transaction logs and therefore the example doesn't apply to this scenario. I've also included an update from Pauls blog as noted below. [Edit: In the initial version of this post, the DBA's backup strategy included log backups. I went through a couple of versions of this post before settling on full + diffs, but I forgot to remove the reference to log backups. In the first comment, Mark House correctly points out that an accidental full backup doesn't prevent a DBA with a complete log backup chain from recovering to any point in time. Apologies for the confusion!] Please note that he is saying the exact same thing that I have already pointed out.

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.


Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×