我们的生产@R_301_457@位于生产服务器上.@R_301_457@被删除了一些如何,但我不知道如何被删除.
如何确定谁删除了@R_301_457@,PC的IP地址,删除的时间等等?
我们使用的@R_301_457@是MS sql Server 2005.
如果我需要更多信息,请告诉我.
解决方法
我有所纠正.如果您查看以下位置的脚本,它应该告诉您删除@R_301_457@的人员和时间. (每天学些新东西…)
这是完整的脚本:
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