point-in-time restore of a database requirements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » point-in-time restore of a database...

point-in-time restore of a database requirements

Rate Topic

Display Mode

Topic Options

Author

Message

andrewp_uk

Posted Saturday, August 18, 2012 1:08 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 11, Visits: 205

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

GilaMonster

Posted Saturday, August 18, 2012 1:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

andrewp_uk

Posted Sunday, August 19, 2012 4:00 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 11, Visits: 205

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

GilaMonster

Posted Sunday, August 19, 2012 4:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

chewychewy

Posted Wednesday, January 16, 2013 2:05 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 72, Visits: 287

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

anthony.green

Posted Wednesday, January 16, 2013 2:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

chewychewy

Posted Wednesday, January 16, 2013 2:20 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 72, Visits: 287

many thanks u cleared my doubts!

Post #1407692

« Prev Topic | Next Topic »

Permissions