Point in time restore

[laker42]

I need some help with getting a point in time restore to work successfully. I need to restore my database to a time between 10/7/2003 6 pm and 10/8/2003 1 am. It doesn't matter what time it is between then. Data was corrected before 6 and a purge process was fired at 1 am so we want the data after it was corrected but before the purge process ran.

Here are the backups that we have:
10/7/2003 2:23 AM database backup
10/7/2003 2:26 AM transaction log backup
10/8/2003 2:23 AM database backup
10/8/2003 2:26 AM transaction log backup

Here is what I have tried so far unsuccessfully:
Restored both 10/7/2003 Db & trans log backups. I then tried to restore the 10/8/2003 trans log backup and specify a point in time that I stated above. When I try to specify a time, it says that the time specified is less than the minimum point in time allowed. Time corrected. It then sets the time to the beginning time of the database backup on 10/8/2003. I have tried several different combinations but have been unsuccessful to this point. What order do I need to restore these backups in? Any help would be appreciated.

Thanks!
John

I forgot to mention that the backups are on tape. So we have a 10/7/2003 tape and a 10/8/2003 tape. I believe this would be easier if we had a disk backup.

 

[SQLBill]

I'm not sure you can do it. As I understand it, a transaction log applies to the last FULL Backup.

Therefore, the 10/8/2003 2:26 AM transaction log backup cannot be applied to the 10/7/2003 2:23 AM database backup. It must be applied to the 10/8/2003 2:23 AM database backup.

This is why some DBA's feel it's best practice to do a transaction log backup just BEFORE doing a full backup.

-SQLBill

 

[laker42]

SQLBill,
If I restore the 10/8/2003 backup, shouldn't I be able to roll the transaction log back to a certain point in time? I have tried all kinds of combinations and have not been successful so far.

John

 

[SQLBill]

You can only do a point-in-time restore with the transaction log backup.

(RESTORE LOG dbname WITH STOPAT <date/time>)

Full backups are restored completely. So you can restore your 10/8/2003 full backup which will restore EVERYTHING up to 2:23AM. Then you can restore the transaction log backup from 10/8/2003 and use WITH STOPAT for any time from 2:23AM to 2:26AM.

IF you had done your transaction log backup BEFORE the full backup (ie. TLog at 2:23 and FULL at 2:26), you would have been able to restore the 10/7 Full Backup and then the 10/8 TLog backup using WITH STOPAT the time on the 8th that you needed.

-SQLBill