Data disappears from table

Question

Data disappears from table

kensai

Old Hand

Points: 352
More actions
September 7, 2002 at 1:42 pm

#57958

I have a very confusing problem with our database. All data in a table totally disappeared!
I'd backup one of our databases to use it in a new one. I uploaded the backup to the new server with ftp and with I created the db on the new one using Restore. I created the ODBC settings and tested the site, it was working perfectly.
A few days ago we had a call saying that our pages are not showing up properly. I checked the database and saw that it was totally empty!
Now thinking that I'm the only admin of the server, do you have any idea what could have caused this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

About the only way is for someone to truncate the table or do a delete from. As this is the first time you may need to check with Profiler to try to catch future occurrances. One thing thou to see how they did it if you are the only admin is to break down how your site works. Look especially for any Stored Procedures where based on input you build the queires. If you have any of these and they have enough space you could have suffered from an injection attack where some jerk put arbitrary code in that based on the way they added could have executed those exact tasks. Also, check out the link in this article to help find other possible security holes http://www.sqlservercentral.com/articles/articlesexternal.asp?articleid=794

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

kensai Old Hand Points: 352 More actions · Answer 2

Let me be a little specific..

A compromised security is not an issue I think; and we can rule out any SUE (Stupid User Error) possibility.

I am saying that not because of the iron-clad security or MCPs we have! We don't!

But!! When we restore this particular DB from daily backups we find this particular table always EMPTY!

It can't be: because even Google has our database-populated pages in its cache!?! The business-users had populated this table for more than 2 months. Now, all of a sudden, our data had dissappeared not only from the actual DB, but also from its backups!

So only logical explanation should be in the DB itself: When we restore, (or open, or access) this table, SOMETHING is wiping the data out!

I checked oll the triggers; all the SPs, all the Extended SPs to no avail!

It is soo bizzare.

I will do anything to get to the bottom of this: why it happened (is happening) and hpow it can be avoided?

Thanks

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Have you tried running DBCC CHECKTABLE ('tablename') on this table? Have you tried restoring your backups to a new database, possibly on a different server?

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Also, try restore to another database from a previous backup to see if the same thing exists.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 5

One more option:

Try creating a new database from scratch (with fresh system tables) and recreate your tables along with the data (either through a transfer or a script).

I had a similar problem one day and found out that the system tables of the database were corrupted.

Herve

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing