August 18, 2012 at 1:08 pm
Hi all
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!
Andy
August 18, 2012 at 1:47 pm
Simple answer: Full backups don't truncate the transaction log.
A log backup contains all log records since the last log backup. It's only if there's no previous log backup (it's the first one since the DB went into full recovery) that the log will be based on the first full backup that DB had since being set to full recovery.
Hence in your case that log backup contains all log records since the very first backup the DB had, the one on the 1st.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2012 at 4:00 am
Ha that is so simple! Thanks for the explanation Gail! I think I was a little confused by the function of the copy-only backup (as opposed to a full backup). But now I understand that this is all related to dif backups though, and not transaction log backups.
Thanks again for your help
Andy
August 19, 2012 at 4:02 am
Yup. Copy only full backups just don't reset the differential base. Differentials (unlike logs) are based on the last full backup that ran.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2013 at 2:05 am
Hi Gila / Guys,
Can i check, Backup taken with copy_only can't be used in point in time recovery?
Meaning to say I can't use the backup taken with copy_only with the transaction log?
thanks
January 16, 2013 at 2:12 am
Full backup with copy only can be used for a PIT recovery.
A copy only full backup, cannot serve as a base for differential restores.
Restore a Full backup with copy only -> Restore a differential = FAIL
Restore a Full backup without copy only -> Restore a differential = SUCCESS
Restore a Full backup with copy only -> Restore a transaction log = SUCCESS
Restore a Full backup without copy only -> Restore a transaction log = SUCCESS
January 16, 2013 at 2:20 am
many thanks u cleared my doubts!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply