SQL Server schema intact but every record from every table is gone

Question

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?

Answer 1

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.

Answer 2

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