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.

A table in the production database has 'mysteriously' disappeared.

Does anybody know of any way to diagnose what the heck happened to it? And who did it?

Edit 1: This is an internal app, with weak security. All of the apps (except mine of course ;-) is vulnerable to SQL Injection, but our users are very unsophisticated and the table name was not one that might be immediately obvious, so I don't think it was an SQL Injection (not that it matters ... kind of beyond the scope of the question).

Edit 2: Also, just an FYI; this table has been around for a long time, so it wasn't 'undone' with a restore.

share|improve this question
    
You mean the "Not Me" ghost got you too? –  Nick DeVore Jun 16 '09 at 21:40
    
do you have separate database accounts for everyone or does everybody log in as 'dba' or something equivalent? –  Zerofiz Jun 16 '09 at 21:43
    
@Zerofiz-We're using Windows Integrated Security, so yes every user can be identified. –  John MacIntyre Jun 16 '09 at 21:46
add comment

7 Answers

You might be able to get the info out of the log using the undocumented ::fn_dblog function which interprets log records. I'm in the middle of teaching a disaster recovery class right now, but if you can wait 2-3 hours I'll post how to do it for you - should be able to get the username too without having to buy any tools (I used to spelunk around the log a ton in 2000 as I wrote a bunch of the internal log analysis code that DBCC CHECKDB uses in 2000).

[Edited to include instructions] Ok - finished teaching and I knocked up a blog post to show you how to analyze the log in 2000, 2005, 2008 to find out when the table was dropped and who did it. Checkout my blog post at Finding out who dropped a table using the transaction log. [/edit]

Do you still have the transaction log around? Which recovery model is the database in? If it's SIMPLE, don't do anything that would cause a checkpoint. If it's FULL or BULK_LOGGED, don't do a log backup. Either of these will cause the log to be truncated and then you may lose the ability to look back in the log, although I included a trace flag in the blog post that may help you with that too.

Thanks

PS One way of preventing table drops in 2000 without adding security is to create a simple schemabound view on it - DROP TABLE will fail if the view exists.

share|improve this answer
    
Thanks Paul, that's great advice. I can wait. I'm leaving now, and going to another client tomorrow, so I'll be trying to figure out what happened on Thursday. I will tell the admin about the log backup. –  John MacIntyre Jun 16 '09 at 23:37
add comment

Maybe it was Little Bobby Tables...

share|improve this answer
    
Nice :) Would +1 it for humour but that would be silly as the only other answer has 1 vote as well. –  John_ Jun 16 '09 at 22:08
2  
Nah, that's funny enough for an upvote. –  Electrons_Ahoy Jun 16 '09 at 23:43
add comment

You might be able to recover this information out of the SQL logs.

share|improve this answer
    
I know this information is in the SQLServer logs, but I thought you couldn't read anything from them. I'd love to find out you can. Anybody know? –  John MacIntyre Jun 16 '09 at 21:43
    
Im only familiar with Sybase SQL Anywhere, but they have a utility to translate log files into SQL statements... –  Zerofiz Jun 16 '09 at 21:44
1  
This tool might be able to help you read the logs. red-gate.com/products/SQL_Log_Rescue/index.htm –  RSolberg Jun 16 '09 at 21:46
    
+1 for the log tool link. Why don't you put it into the answer? –  John MacIntyre Jun 16 '09 at 23:41
add comment

If the default trace log is running, all the info is stored in the log folder. You should be able to see when the object (table) was dropped and by which connection that did it. But this type of permission should be only given to DBAs anyway

share|improve this answer
add comment

I am trying to fix a corrupt MSDB. Sorry I am not able to elaborate.

Run these and it should give a general idea where to look, assuming your default trace is on.

SELECT * FROM ::fn_trace_getinfo(default)

SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description FROM ::fn_trace_geteventinfo(1) t JOIN sys.trace_events e ON t.eventID = e.trace_event_id JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

share|improve this answer
    
Thanks, I think the sys.* tables are 2005 though aren't they? Is there a 2000 equivalent? –  John MacIntyre Jun 16 '09 at 23:38
add comment

Only way to find out this info is by reading transaction log (assuming it’s in full recovery mode).

Two ways to do this:

  • Third party tools such as ApexSQL Log or SQL Log Rescue (free but sql 2000 only)
  • Using commands such as DBCC LOG or fn_dblog – none of which is well documented unfortunately
share|improve this answer
add comment

In SSMS you can try right-clicking on the dB and navigating through Reports -> Standard Reports -> Schema Changes History.

Right click on the report and 'SaveAs' Excel and find your object namne.

You will not be able to get anything if the server has been restarted more than five times after the object had been dropped.

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.