7

We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real maintenance we've done on it is to move the partition the database files resided (done nearly a week ago) and we ran the Database Engine Tuning Advisor to look at some suggestions for new indices yesterday around 12PM.

Just after 5PM while the software was being used as usual someone notified me that the program no longer seemed to work properly. I checked the database and not only was data specific to his use of the program was gone, but every table of fifty tables or so seemed to be wiped of all records.

After talking to the application developers in extensive length, it became clear that this wasn't a remotely possible capability of their software.

In the scramble to get back from our feet we cloned a backup of the VM over the running copy between restarting and it's been running five for about a day ever since. Consequently, we've no logs of the events leading up to the problem itself.

Have you seen anything like this happen before? (All records in all tables of a database just... gone.) Does anyone have a reasonable theory as to how this could occur and what if anything would be a reasonable measure of prevention?

Improve this question
5
  • Do you have foreign keys between tables?
    – gbn
    Oct 6, 2011 at 17:14
  • The application does not use any foreign keys, so no it does not.
    – Kaganar
    Oct 6, 2011 at 19:20
  • 3
    How you maintain referential integrity better then the RDBMS could do?
    – gbn
    Oct 6, 2011 at 19:22
  • FYI the application isn't directly under my control, unfortunately, and I'm unlikely to see any changes as the result of my direct requests within months -- a long time with such potential data loss issues lurking.
    – Kaganar
    Oct 6, 2011 at 19:24
  • Let's just say this application wasn't coded with a solid data model in mind. It's a hydra in the way it deals with data -- some on a common file share, most of it on a MSSQL server, and some in a disk-based relational database. Unfortunately, it's niche software and it does certain things very well.
    – Kaganar
    Oct 6, 2011 at 19:26

2 Answers 2

Reset to default
14

This happened at my company.

Someone re-ran the schema creation scripts from within the application's admin interface--something like an "initialize database" command. These scripts included a series of IF EXISTS()...DROP...CREATE statements to drop and recreate each table and constraint. Since they were designed to wipe a database even if it was already populated, they were carefully structured to account for foreign key relationships. Thus, the database schema was there and looked great, but all of the data was gone.

To prevent this, revoke the appropriate DDL permissions from the app's database login after it has created its own schema, or make sure any "initialize database" functionality your application has includes several safety checks and presents appropriate warning to the user regarding what's about to happen.

Improve this answer
2
  • 1
    Scary but a likely explanation.
    – Mark Storey-Smith
    Oct 6, 2011 at 17:22
  • @Mark - Thankfully, it was a QA database that stored no critical data.
    – Nick Chammas
    Oct 6, 2011 at 22:04
11

Every table? This is deliberate vandalism or stupidity. It isn't a SQL Server issue

There are at least 3 ways:

  • DELETE with no WHERE clause
  • TRUNCATE TABLE
  • DROP TABLE with CREATE TABLE after

If you have foreign keys then it is impossible to use TRUNCATE TABLE and trickier to use DELETE. You'd have to have multiple passes of DELETE. Ditto for DROP/CREATE TABLE

A DELETE with no WHERE is quite easy to do with your app uses dynamic SQL and some WHERE logic is removed with NULL concatenation.

DROP/CREATE and TRUNCATE TABLE also require very high levels of privilege.

Summary. One or more of:

  • poor security
  • no foreign keys
  • ill-informed developers

Edit: badly designed triggers or FKs could wipe several tables quite quickly

Improve this answer
1
  • 5
    A possible 4th way: If there are foreign keys and they were created with an 'on delete cascade' clause then (depending on the database design) deleting with no where clause from the wrong table could clean out the entire database. I'd call the summary for that "foreign keys with poor design".
    – gsiems
    Oct 6, 2011 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.