Profile photo for Eli Blake

You must be in full-recovery mode for P-I-T recovery that requires a full backup file, log file backup and tail log. The LSN associated with the bak/trn files is supposed to help with the exact time that can be recovered to assuming frequent backups. If you have a full at 4am, a differential at 6 am and then log backups every hour you are accepting you will won't be recovering data between 4 and 6 am. Recovering the tail of the log gets some of the data in between but I tested it since I haven't done it in a while. In the example below you would want to restore after the LSN for the full file.

My question is how do you piece to together the log files since the stamp on the backup doesn't list the LSN? Picking the wrong file and get this error.

I have queried the msdb table msdb.dbo.backupset which can give the chronology but curious if other than this its just trial and error. Anyhow here are the remaining steps.

This sends the database status into "recovering mode" until all the logs are recovered. On the final log you can choose the option for Point in time recovery.

I think this would be lengthy without differential backups every so often through out the day. Eventually after hunting around you will get this:

If you are using simple recovery mode you could use frequent differentials although is more expensive and there is no ability to recover after the last differential. There is one tidbit if you need smaller backup increments is marked transaction log backups for busy apps like BizTalk or TFS. This is like the inception of stored procedures with a Proc in a Proc and a marked log table as shown. Enough of that for now. I will be comparing Oracle's flashback and recycle bin options that rely on pre-planned undo retention and a flash recovery areas. I don't think SQL Server offers a comparable real-time flash back.

Works at Government
971 content views
Active in 1 Space
Joined January 2016
About · Careers · Privacy · Terms · Contact · Languages · Your Ad Choices · Press ·
© Quora, Inc. 2025