我们的生产数据库位于生产服务器上。数据库不知怎么被删除了,但我不知道它是怎么被删除的。
我如何确定是谁删除了数据库、PC 的 IP 地址是什么、什么时候删除的等等?
我们使用的数据库是 MS SQL Server 2005。
如果我需要更多信息,请告知我。
答案1
我承认错误。如果您查看以下位置的脚本,它应该会告诉您数据库被删除了,删除的时间和对象。(每天学习新东西……)
以下是完整脚本:
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