@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

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.

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.

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.

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