June 18, 2011 at 7:03 pm
Hi,
We run SQL 2008 R2 64bit and take a FULL backup every night. Now we have come accros a situation where we need to go back few days backup, restore that backup and anayse the transaction log.
What are the best tools that we can use/buy that can do the job right. Money isn't an issue at the moment so long the amount is not ridiculous.
Cheers 🙂
WRACK
CodeLake
June 18, 2011 at 7:58 pm
A few questions first. What is the problem you have encountered? What recovery model are you using for the database?
June 18, 2011 at 8:07 pm
Hi Lynn,
Thanks for a quick reply. We are using Full Recovery.
We are experiencing of deletion of records that we think is not legit. We have changed all the passwords and denied access to all users too but we just want to go back to the logs and find out within what window of time that happened.
Cheers 🙂
WRACK
CodeLake
June 19, 2011 at 5:23 pm
Hi,
This just happened again. Sunday 2 PM was a full backup right after we restored all the missing records and Monday 9:20 AM 250000 records have been deleted. I am taking the transaction log backup right now so I can use yesterday's backup and the transaction log since then to hopefully close in on the issue on where, what and how the records are being deleted.
I really want someone's advice on what tools can I use to analyse it?
EDIT: I just realised, since I took full backup after records restoration, if I understand correctly then the transaction log was cleared due to it being the full backup. Now it wont allow me to just take the transaction log backup. What command should I use?
WRACK
CodeLake
June 19, 2011 at 7:35 pm
WRACK (6/19/2011)
Hi,This just happened again. Sunday 2 PM was a full backup right after we restored all the missing records and Monday 9:20 AM 250000 records have been deleted. I am taking the transaction log backup right now so I can use yesterday's backup and the transaction log since then to hopefully close in on the issue on where, what and how the records are being deleted.
I really want someone's advice on what tools can I use to analyse it?
EDIT: I just realised, since I took full backup after records restoration, if I understand correctly then the transaction log was cleared due to it being the full backup. Now it wont allow me to just take the transaction log backup. What command should I use?
Are you taking regular transaction log backups? If you're in FULL recovery mode then you should.
You should be able to take a transaction log backup which will contain all log activity between your last full backup (the one you mentioned you took immediately after you "restored all the missing records") and now, i.e. yesterday's backup (if it is what I am thinking, the second most current full backup) is not going to help you with respect to this new transaction log backup you'll be taking.
My two cents: for future occurrences I would not worry about a transaction log analyzer at this point. Transaction log analyzers are expensive and complex (probably related). Since the process that deletes records seems to be doing this regularly why not setup a lightweight AFTER DELETE trigger on the table in question and start capturing the following information in a history table:
> identity column
> all columns from deleted records
> value of ORIGINAL_LOGIN()
> value of SUSER_SNAME()
> GETDATE() when delete occurred
> any other suggestions for good columns to capture?
Just a hunch, but it sounds like there may be some code with an incorrect where clause. If you setup the trigger as described above not only will you have easy access to recover deleted data after future occurrences plus you'll have the info you need to start tracking back to who or what is deleting the data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2011 at 11:03 pm
Thanks. Much appreciated.
Yeah we have asked the DBA to take transaction log backups every 30 minutes during the course of this investigation. After that we will change it to an hour or so.
I hear what you are saying but we are just an external consultants to this company and we don't have a lot of weight on their disaster recovery plan. Guess, this incident would make them hear us.
Triggers are out of question as the current IT department has things against them! Shall see how things go tomorrow.
I have found 93% records are same that were deleted the first time and this time around. Should give us some clues.
WRACK
CodeLake
June 19, 2011 at 11:22 pm
You can also look at setting up a light weight server side trace that logs delete actions against the table(s) involved.
June 19, 2011 at 11:25 pm
Hi,
How would I set that trace? Do you have some code handy? I would really like to be prepared for the next delete phase as I am almost certain that it would happen and I would like to be prepared.
EDIT: I think you mean a profiler trace, right? If so then already set that one. Fingers crossed.
WRACK
CodeLake
June 20, 2011 at 4:58 am
There are a number of tools out there which can translate the transaction log for you into a GUI so you can see what happened and when
My personal favourite is the log reader in LiteSpeed for SQL Server by Quest Software (http://www.quest.com/litespeed-for-sql-server/) you can download a 14 day trial after which you need to licence the product for that functionality
There is also Toad from Quest and ApexSQL's Log Reader, you can get free trials for a number of days then you need to buy the software
The reason I like LiteSpeed is for the offline log reading ability so you can point it to you bak / trn files without having to restore them to a server and it cycles through them loading into memory saving on disk space, but make sure you have a machine beefy enough to load a lot into memory, usually find for about a 50GB database 6GB RAM will be enough.
But for ongoing tracing I would look at implimenting a trigger on the table(s) in question for after delete to capture the user and the program which is actually doing the delete.
June 20, 2011 at 5:37 pm
Thanks Anthony.
I have already downloaded the trial of Apex softwares. From what I can see, it can also load BAK file directly without restoring. Anyways, at the moment we are prepared to capture the culprit and find out who/what is deleting the records.
Thanks all for your help.
WRACK
CodeLake
August 30, 2018 at 2:50 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply