Sql-Server
刪除數據庫資訊
我們的生產數據庫位於生產伺服器上。數據庫被刪除了一些,但我不知道是如何被刪除的。
如何確定誰刪除了數據庫、PC 的 IP 地址是什麼、何時刪除等等?
我們使用的數據庫是 MS SQL Server 2005。
如果我需要更多資訊,請告訴我。
我站得更正了。如果您查看以下位置的腳本,它應該會告訴您刪除數據庫的人員和時間。(每天學些新東西…)
這是完整的腳本:
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