Sql-server – SQL Server schema intact but every record from every table is gone

sql serversql-server-2005

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?

Best Answer

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.

Related Question