Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

point-in-time restore of a database requirements Expand / Collapse
Author
Message
Posted Saturday, August 18, 2012 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:00 AM
Points: 11, Visits: 258
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
Post #1346874
Posted Saturday, August 18, 2012 1:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 41,519, Visits: 34,437
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1346878
Posted Sunday, August 19, 2012 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:00 AM
Points: 11, Visits: 258
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
Post #1346915
Posted Sunday, August 19, 2012 4:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 41,519, Visits: 34,437
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1346916
Posted Wednesday, January 16, 2013 2:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:38 AM
Points: 91, Visits: 391
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
Post #1407681
Posted Wednesday, January 16, 2013 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1407684
Posted Wednesday, January 16, 2013 2:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:38 AM
Points: 91, Visits: 391
many thanks u cleared my doubts!
Post #1407692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse