SQL Server 2000 - Database Table Dissappeared. What are the possible reasons?

Question

We've had a table disappear from our production SQL Server 2000 database.

It's running on RAID 5 storage so we're pretty confident that it's not hardware-related corruption.

We're aware that this indicates a possible breach of security. We're investigating.

Are there any other possible reasons why a table would seemingly-randomly disappear?

Answer 1

Here are the most likely situations (assuming no replication):

• intentional DROP TABLE
• unintentional DROP TABLE
• SQL Injection Attack
• bug in code (stored procedure, compiled exe/dll, etc)
• someone fat-clicked in SSMS somewhere
• someone edited a diagram and hit "delete table from database" instead of "remove from diagram"

What recovery model are you using on the database? Can you roll the trans log to see what happened?

Answer 2

The following doesn't work on SQL Server 2000, but if you ever move to SQL Server 2005 or 2008, you might want to implement the following:

CREATE TABLE [dbo].[AuditDDL](
    [EventID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [EventData] [xml] NULL,
    [EventTime] [datetime] DEFAULT (getdate())
    )
GO

-- This fails on SQL 2000. 
CREATE TRIGGER [trgAuditDDL]
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
INSERT INTO AuditDDL(EventData)
SELECT EVENTDATA()
GO

Since it also catches when indices are rebuilt, you'll need to run the following every month or so to clear out those events:

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"ALTER_INDEX")]') =1
GO

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"UPDATE_STATISTICS")]') =1
GO

I know it is also a little late for your situation, and the wrong version of SQL Server, but it may help other folks in the future.

Answer 3

Redgate's SQL Log Rescue is a free download that might help.

I would expect hardware issues to manifest themselves in the System Event Log (at least mine always have).