Breaking the Backup Chain – REDUX (Or Eating Crow)

While I like to pride myself on being professional enough to admit when I’m wrong, it turns out that I’m a bit more shallow than I’d like to be.

For example, I commonly tell my clients that I don’t know everything about SQL Server (and that anyone who claims to know everything is someone they should run away from). Consequently, I don’t mind being wrong when I ‘guess’ at things with them as we’re figuring out various problems and issues together.

But as I’ve found out in the last day, admitting that I’m wrong has actually been a bit of a nightmare – for two reasons:

First because I was wrong in a case that really mattered: I offered flawed professional advice. And that, in turn means that I got to simultaneously feel sick about offering bad advice while also feeling dumb about doing so in ‘front of everyone’.

Second, this was a nightmare because this was sadly a case where I’ve been WRONG about a key concept for entirely too long. Years in fact.

I Was Wrong

Given that I was wrong, I want to do two things with this post:

First, I want to correct the bad information that I posted previously. To that end, rather than correcting my previous posts, I’m going to leave them intact and ‘update’ them with warnings about the bad info – along with a link to this post.

Second, I also want to take a look at HOW I ended up being so wrong because I think a post-mortem of my mind-set might be instructive and can hopefully help others avoid falling into similar traps. (And if I’m honest, I also want a chance to, umm, defend myself a bit.)

How I was Wrong

In the previous two posts: SQL Server Backups – When More is Less and Unused Secret Weapon – COPY_ONLY Backups, I erroneously linked COPY_ONLY backups to providing ‘protection’ for transaction log backups. That was incorrect. Transaction Log backups are NOT susceptible to the problems I outlined in terms of breaking the backup chain. Only DIFFERENTIAL backups are.

Being wrong about that sucks. But it’s also worth mentioning that even though I was totally WRONG about the scope of the ugliness that non-COPY_ONLY backups can cause, the reality still exists that ‘extra’ or non-COPY_ONLY backups can STILL cause disasters – just not as aggressively as I erroneously stated (and believed) in both of the posts listed above.

So, stated differently, IF a FULL backup is executed in your environment WITHOUT the COPY_ONLY clause, it can/will jeopardize your backup chain as it will reset the differential base LSN of all subsequent DIFFERENTIAL backups. Meaning, in turn, that if you don’t have (or retain) access to that FULL backup made without the COPY_ONLY option, you won’t be able to recover your databases using subsequent DIFFERENTIAL backups.

You will, however, be able to use transaction log backups if you have them. And that’s where I was wrong. I mistakenly assumed that a FULL backup (without the COPY_ONLY clause) would somehow break transaction log backups as well. (More on that in a second.)

Consequently, the images I provided in my last post to show the negatives and positives of COPY_ONLY backups should look as follows – where I’m using DIFF backups instead of TLOG backups as the target for these images:

Figure 1: Non-COPY_ONLY Backups break the log chain for subsequent DIFFERENTIAL Backups.

Figure 2: COPY_ONLY Backups preserve the log chain for DIFFERENTIAL Backups.

And, just to make sure that I’m being perfectly clear, this is probably a better image/overview of the correct implications of working with the backup chain:

Figure 3: A better overview of the Log Chain and COPY_ONLY backups – which shows how non COPY_ONLY backups can break DIFFERENTIAL backup chains, but how Transaction Log Backups can be used to recover regardless of COPY_ONLY considerations.

So, stated differently, a good way to protect yourself against non-COPY_ONLY backups is to always have an unbroken chain of transaction log backups to your most recent FULL backup. At which point you can then attempt to use DIFF backups as a way to more speedily recover should you need to. Only, in cases where this would make sense, you’d be dealing with large enough databases that I’m pretty sure you’d want to CHECK applicable LSNs before getting started. And, if that’s something that interests you, then you’ll want to check out a great post by Paul Randal where he covers example queries you could easily fit to your purpose here: Backup with COPY_ONLY – How to Avoid Breaking the Backup Chain.

Post Mortem (Or How I Came to Be Wrong)

Being wrong about such a ‘core’ SQL Server concept still has me reeling. Especially since I’ve been wrong about this for so long. But, as I mentioned, I felt that sharing some of the background BEHIND how I came to be wrong might be helpful as a bit of a warning for anyone who is patient enough to read this far. To that end, what follows is a bit of a post-mortem.

As near as I can determine, about 10 years ago (if my memory serves me correctly) I ran into an ugly problem with a non-mission-critical database where I wasn’t able to restore the transaction log as expected. I was really only starting to make my foray into ‘DBA-hood’ at the time, and never DID figure out exactly what specific problem I had run into. But, when SQL Server 2005 was released with the notion of a COPY_ONLY backup, I think I naturally just assumed that it was designed to protect me against the kind of problem I had encountered with the Transaction Log. (Or, more importantly, even if that wasn’t the case, I somehow WRONGLY assumed that COPY_ONLY backups applied to the transaction log.)

Through the years, this simple, wrong-headed, assumption has stuck. And since I’ve always been OVERLY cautious about potentially breaking the (transaction) log chain in my mind, I’ve never really put myself in a position where I’ve ‘challenged’ this assumption. Instead, in my mind this has been more like a huge sink-hole that I’ve tried to definitely steer clear of – and help others avoid as well. Which, in turn, was compounded by two mistakes:

First, before I blogged (in my previous post) about how Transaction Log Backups could be used against COPY_ONLY backups (and the ‘source’ backup) simultaneously, I went ahead and made sure to test everything out – just to make sure I hadn’t overlooked something. Sadly, my tests were only focused on half of the equation: making sure that transaction log files would still work AFTER a COPY_ONLY backup was made. Which, now that I look back upon it, was a totally dumb test – as that’s ALWAYS going to come out positive. What I should have tested was the OPPOSITE case as well. Which, in turn, is why I’m sure that the scientific community has turned to double-blind testing – as I fell victim to the age-old trap of setting up a test that confirmed what I thought I knew instead of ensuring the opposite and moving on from there. My bad – and lesson (hopefully) learned.

Second, over the years I’ve poured over the documentation in Books Online for both the section on Working with Transaction Log Backups (where it talks about breaking the log chain) and the section where it talks about COPY_ONLY Backups and in each case it’s TOTALLY possible (and, frankly, VERY EASY) to read both of these sections of documentation with my ERONOUS mind-set and have it validated over and over again. Or, in other words, I can still totally read both of these sections of documentation with the WRONG idea in my head about Transaction Log backups and COPY_ONLY operations and ‘feel’ like they validate what I previously, and incorrectly, thought. For example, in ‘intro’ to COPY_ONLY Backups in Books Online says: “Usually, taking a backup changes the database and affects how later backups are restored.” When I read that, I ASSUMED that was talking about TLOG backups – because that’s what I had already ‘pre-conceived’ it was talking about. Moreover, COPY_ONLY backups CAN be applied to Log File backups – further sort of ‘confirming’ in my mind that this section was TALKING about Log File backups – even though that is patently NOT the case.

So, what I’m trying to say is that I had a FALSE notion in my mind for YEARS and multiple passes through the documentation STILL let me ‘confirm’ that wrong idea over and over again because of my PRECONCIEVED (and incorrect) notions about what the documentation was saying. I’d love to say: “When in doubt, test” as a response to that. But in my case, there WAS no doubt – I had jumped to an incorrect conclusion years ago. So, somewhere in there, there’s a big lesson about seeing what you WANT to see.

Another Win for the SQL Server Community

Finally, I’d be remiss if I didn’t thank the three DBAs that commented on my last blog post. Because as much as it stunk to find out that I was wrong, I’d prefer to know that I was wrong rather than continue to wallow in ignorance. Which is just another reason why I love the SQL Server Community. Because I probably wouldn’t have ever figured out I was wrong on this one had rxmoore not posted such a detailed reply (or set of replies) and been joined by 2 other DBAs who confirmed his grasp of the concepts and validated his findings/tests as well. So, thanks guys – and I really appreciate your comments. Hopefully I’ll shape up in the future enough to merit your continued attention. ;)