sql-server – 删除数据库信息

如何确定谁删除了数据库,PC的IP地址,删除的时间等等？

我们使用的数据库是MS SQL Server 2005.

如果我需要更多信息,请告诉我.

解决方法

Detecting Schema Changes

这是完整的脚本：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!