September 16, 2008 at 2:14 pm
First and foremost - I understand that t-logs are not an audit solution.
That disclaimer out of the way, I was wondering if something was possible: we had a couple dozen records deleted out of a table several weeks ago. We've recovered the data via trial and error to pinpoint the time it was lost. My question is this: is it possible to restore a database with the transaction log intact and readable as it existed at that point in time? The restores we did ended up with empty transaction logs, which makes sense if the transactions are just played forward and applied to the db.
So if that's not possible, is it possible to ever find out what user deleted those records?
Other information:
weekly full backups
nightly diffs
4 hour t-logs
thanks for the help.
September 16, 2008 at 5:22 pm
I think it would be possible with a log reader tool. We use Lumigent Log Explorer and it can read a backup of a transaction log without having to restore it.
Greg
September 16, 2008 at 9:20 pm
Once you start restoring, you're applying log records to the database. The log itself doesn't get restored, but I believe that it's marked as truncated to the restore time.
To get the transactions, you would need a log reader tool. Red Gate (my employer) has one for 2000 that's free because it wasn't really a good selling product. You can download that and use it to read the logs. It should read t-log backups.
September 17, 2008 at 6:54 am
One additional piece of information - our backups are handled via CommVault, and the redgate log reader doesn't seem to be able to read these backup files, which is what I would expect. So reading the actual backup file prior to restoration doesn't seem to be possible in my case.
September 17, 2008 at 8:04 am
This is one reason I really avoid the third party backups like that. The compression tools (Litespeed, Red Gate, etc), are convertable to native format. Some of the others aren't.
September 17, 2008 at 9:29 am
Trust me, I would very much like to avoid them also. The decision was made at a higher level, where it was determined that a single enterprise backup solution made more sense than individual backup solutions tailored for specific applications/funtions. I would tend to disagree, but it's beyond my pay grade. Thanks anyways for your help, as I told the powers that be that I would double check my assumptions with someone else.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply