I have a question about a point-in-time restore of a database. I thought I understood all about backups years ago, but it turns out there is more to learn!
I have a database (SQL 2008 R2, Full recovery model). This is created on 01/08/2012 and I do a full backup on this day. Then the following things happen on the following days at 21:00 (note that data is continually being added / updated etc to my database):
02/08/2012 - Full copy only backup
03/08/2012 - Full copy only backup
04/08/2012 - Full backup
05/08/2012 - Full copy only backup
06/08/2012 - Full copy only backup
07/08/2012 - Transaction log backup
After the transaction log backup on the 7th, I'm asked to do a point-in-time restore to 17:00 on 06/08/2012. Which files would be required? My answer to this would be that the last full backup, and the transaction log backup would be required. So, the copy only backups on the 5th and 6th would suffice as the full backup, or the full backup from the 4th. But the full backup from the 1st would not be sufficient because a full backup had been done since then. Obviously there is only one transaction log backup which would be required too.
However, it turns out that this is wrong!
I am able to use the full backup from the 1st along with the transaction log backup from 7th to restore to any point in time between the 1st and 7th! I have tested this, and it goes against my understanding of how the backup process works.
I was wondering if anybody knew of a good resource that would explain this well? I've tried searching the internet, but nothing seems to go into much detail past the basics.
Thanks in advance!