Missing Table Data in SQL 2005 Standard

BNeagle

Hello,

There has been a sudden loss of data in some (not all) of the tables in my database. I am using SQL Sever 2005 Standard with a simple recovery model for a single database. I can't find any specific hints in the logs to explain the loss, and this did not occur during a backup or scheduled maintenance routine. It seems the data just vanished at a specific point this morning, and I am not able to find out why. Do any of you have a suggestion that would help explain the loss of data in specific tables and not others in a given database without the known use of ROLLBACK or DELETE Or do any of you know a way that I can look back in the server to identify what may have happened Any help would be appreciated.

Thanks,

Billy

Madhu K Nair

if the database is intact then it should be due to some delete statement. Otherwise , database can not have these kind of inconsistant behaviour. You can try the default trace of sql server 2005 and see whats the statement ran in the morning if the trace is logged. Check here http://madhuottapalam.blogspot.com/2007_10_01_archive.html

Otherwise you have to restore the database from backup and do reverse engineering

Madhu

BNeagle

Madhu,

The DELETE was actually my first instinct, but the circumstances do not lend themselves to the use of the statement directly. Your post about the default trace was very helpful, thank you. Although the trace did not indicate the use of a DELETE statment, it seems the database did attempt a few RESTOREs with REPLACE options about twenty minutes before the staff noticed the issue. I'm not certain why the RESTORE command was issued. Do you know if this a normal part of the functioning of the backup or some other automated event in the SQL Server

In this particular case, recovery of the data isn't essential (although it would be nice.) I'm just wanting to ensure that I am not neglecting something that would make this a more serious issue in production.

Thanks,

Billy

Sue Hoegemeier

Did you run a DBCC CHECKDB on the database Are there any errors or warning messages in the error log or in the event logs on the server

Those would be least likely. It seems most times when data disappears, it's from the unknown deletes by a user.

-Sue

BNeagle

Sue,

No, I did not run CHECKDB, but I will if this crops up again. However, just running through the logs made available in the SQL IDE, there were no significant errors reported. The only issue with the DELETE is that no one other than myself has access to directly query the database. All of the staff interaction is managed through an application I developed, and the few DELETEs that it uses are contained and do not have this kind of scope.

Thanks,

Billy

Sue Hoegemeier

It really isn't likely to be something odd going on with SQL Server just wiping out data on it's own. You have something that is deleting the data that you just aren't thinking of. Could be a job, bug in some code, user error, lots of things. If it continues, you could set up a server side trace to run to find out what's going on.

-Sue

BNeagle

Sue,

Thanks for the help. I'm probably going to sit on it and wait. I've confirmed my backup job, so I don't think I'll lose any data. If it comes up again, then I'll figure out another avenue of attack probably involving a trace.

Thanks,

Billy