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.