What are the challenges you face when working across database platforms? Take the survey
Options

Restore to point in time from 2 Full backups

MartinHMartinH Posts: 82 Bronze 2
edited June 4, 2012 6:09AM in SQL Backup Previous Versions
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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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 8
  • Options
    MartinHMartinH Posts: 82 Bronze 2
    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?
  • Options
    peteypetey Posts: 2,358 New member
    That should work.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    MartinHMartinH Posts: 82 Bronze 2
    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?
  • Options
    peteypetey Posts: 2,358 New member
    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 8
  • Options
    MartinHMartinH Posts: 82 Bronze 2
    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!
Sign In or Register to comment.