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 had our production database located on a production server. The database got deleted some how but I don't know how is was deleted.

How can I determine who deleted the database, what was the IP address of the PC, at what time it got deleted and so on?

The database that we use is MS SQL Server 2005.

Let me know if more info required from my end.

share|improve this question

migrated from stackoverflow.com Mar 4 '11 at 19:31

This question came from our site for professional and enthusiast programmers.

    
Did you have a backup? –  JNK Mar 4 '11 at 19:30
1  
    
no backup!..... –  Romil N Mar 4 '11 at 19:39
    
Actually deleted previous comment. I was searching for the word DROP in the text data. I think this might show it SELECT * FROM sys.traces cross apply fn_trace_gettable(path, 1) where EventClass=47 and id = 1 and ObjectType=16964 –  Martin Smith Mar 4 '11 at 19:51
    
@Martin - Really? I tried creating and deleting a database a couple of times but I don't see anything in that query that would tell me a drop db has occurred. –  Thomas Mar 4 '11 at 19:56

1 Answer 1

up vote 4 down vote accepted

I stand corrected. If you look at the script at the following location it should tell you who and when a database was dropped. (Learn something new everyday...)

Detecting Schema Changes

Here is the full script:

declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
 obj_name nvarchar(256) collate database_default
, database_name nvarchar(256) collate database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) collate database_default
, login_name nvarchar(256) collate database_default
, application_name nvarchar(256) collate database_default
, ddl_operation nvarchar(40) collate database_default
);

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

insert into @temp_trace
select ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and
DatabaseID <> 2

update @temp_trace set ddl_operation = 'CREATE' where
event_class = 46
update @temp_trace set ddl_operation = 'DROP' where
event_class = 47
update @temp_trace set ddl_operation = 'ALTER' where
event_class = 164

select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;

select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, *
from @temp_trace where object_type not in (21587)
order by start_time desc
share|improve this answer

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.