February 3, 2011 at 4:48 pm
Hi,
We have come across a issue in our production servers in which some of the table data was deleted. The tables which were affected had some few rows left, the data was not completely deleted
1. Can a virus delete some data from a particular , or can it corrupt the DB.
2. we have antivrus scans running on the DB servers, no logs were found in eventvwr also.
3. manual delete by some user has been ruled out.
4. We have 2 DB's and the tables affected are the most frequently used tables in our application.
Though we have succesfully recovered DB from transaction logs and DB backup, we need to figure out the exact issue which could be human error or MS SQL server crash/corruption
Please help me urgently in finding out the exact reason for the issue.
February 4, 2011 at 1:23 am
A user (from somewhere) connected to the DB and deleted data. SQL doesn't delete data by itself and corruption wouldn't be that clean (it would leave errors that checkDB would pick up)
A virue could, if that virus could get a connection to SQL server, with a valid login and password and permissions on that table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2011 at 1:43 am
if this database is in 2008(i know u raised this question in 2005 forum), and if record get deleted very recently then you can use sql server reports which will give infirmaiton on schema change history.
How to see it...
SSMS--Databases--right click on your dbname--reports--standard reports--schema change history
----------
Ashish
February 4, 2011 at 1:56 am
The default trace (which is where the schema change report pulls its data from) does not contain any information about row deletions. It has schema changes (create, alter, drop), not DML (select, update, insert, delete)
The only way to tell exactly what (login, host machine, application name) ran a delete was to have a custom trace or trigger present at the time of the deletion.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2011 at 7:26 am
Hi,
Thanks for your responses.
Custom traces have not been enabled. Is there any other way to trace out the exact cause.
February 4, 2011 at 7:28 am
Thanks Ashish ,
But this is 2005. any way to do the same in 2005 sql server ?
February 4, 2011 at 9:58 am
dhar.abhay (2/4/2011)
Thanks Ashish ,But this is 2005. any way to do the same in 2005 sql server ?
Ashish's solution wouldn't work in 2008 either, as the default trace (and hence the schema reports) don't show deletions, only schema changes. The default trace exists in 2005, it's just that the reports aren't there. Won't show what you want though.
Bottom line, if you didn't have an audit of some form in place at the time of the deletion, you are not going to be to tell exactly what happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply