Restore to point in time from 2 Full backups

[MartinH]

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

[petey]

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

[MartinH]

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?

[petey]

That should work.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7

[MartinH]

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?

[petey]

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

[MartinH]

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!