Restore to point in time from 2 Full backups

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Restore to point in time from 2 Full backups

Postby MartinH » Sat Jun 02, 2012 9:51 am

Hi:

I have 2 full backups, and no log backups were performed between these copies. Is it possible to restore to a point in time between the 2 backups.

Here is what I have:
1) Full copy of 29/4/12 22:00
2) Full copy of 30/4/12 22:00 (no log backup was performed since point 1)
3) I want to restore to a point at 30/4/12 18:00

Is this possible? If so, what steps should I take to perform this?

Thanks,
Martin
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am

Postby petey » Sat Jun 02, 2012 6:19 pm

Restoring to a point in time is only possible using transaction log backups.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby MartinH » Sun Jun 03, 2012 7:58 am

Okay, when I do a Full backup, and don't perform a Log backup, I take it the log is still preserved, so can I now do a Log backup and use that? A Log backup has not been performed yet, so is it still preserved? If so, can I use that Transaction Log to do a restore to a point in time?
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am

Postby petey » Sun Jun 03, 2012 11:06 am

That should work.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby MartinH » Mon Jun 04, 2012 7:37 am

Should I start with restoring the full backup from 29/04/12, and then restore the log to the specified point in time of 30/04/12 18:00?
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am

Postby petey » Mon Jun 04, 2012 8:19 am

Yes, restore the full backup from April 29 with the NORECOVERY option, followed by the first trx log backup you have made after that full backup.

Unless your recovery models changed between the time of the above 2 backups, or you had bulk-logged trxs, recovering to that point in time should be possible.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby MartinH » Mon Jun 04, 2012 11:09 am

Thanks to all who have contributed.

I have managed to recover the data I was interested in. Lots of lessons have be learnt, and once again Redgate make this all so easy.

Great job guys!
MartinH
 
Posts: 64
Joined: Fri Apr 15, 2005 9:13 am


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests