4

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.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
xorpower
  • 141
  • 6
  • Did you have a backup? – JNK Mar 04 '11 at 19:30
  • 1
    http://stackoverflow.com/questions/1043971/determine-which-user-deleted-a-sql-server-database – Brandon Frohbieter Mar 04 '11 at 19:31
  • 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 04 '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 04 '11 at 19:56
  • @Thomas - EventClass=47 is from `SELECT trace_event_id FROM sys.trace_events WHERE name = 'Object:Deleted'` [16964 is database](http://msdn.microsoft.com/en-us/library/ms180953.aspx) – Martin Smith Mar 04 '11 at 20:02
  • @Martin - Yeah, after you posted that I did some digging. Good find! – Thomas Mar 04 '11 at 20:21

1 Answers1

4

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
Thomas
  • 168
  • 5