Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
1  
definitely not hardware-related corruption - you'd be having plenty of errors showing up the Event Log, and it wouldn't be an isolated table disappearing. –  Duncan Smart Jan 6 '10 at 23:50
add comment

3 Answers

up vote 5 down vote accepted

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?

share|improve this answer
add comment

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.

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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