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.

  • Did you have a backup?
    – JNK
    Mar 4 '11 at 19:30
  • 1
  • 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 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
  • @Thomas - EventClass=47 is from SELECT trace_event_id FROM sys.trace_events WHERE name = 'Object:Deleted' 16964 is database Mar 4 '11 at 20:02

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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