How to Auto Track Deleted Databases in SQL Server

Using SQL Server’s Schema Changes History we can identify the name of databases that were dropped from the SQL Server. In a big SQL Server environment, connecting to each Schema Changes History of every SQL Server instance is a very difficult task. In this article, we will learn how to automatically track deleted databases.

Multi options

SQL Server's Schema Changes HistoryThere are several options with which you can track a database getting deleted from your SQL Server instance.

Option1: Log data from the system table sys.databses. By comparing the log from a different date range, you can identify databases that were deleted. However, this will not reveal the user who deleted the database.

SQL Server AlertOption2: You can set up a SQL Server alert to send an alert notification whenever a database is dropped from the server. However, this option is not preferred in an environment where applications create very and drop databases quite often.

Option3: Connect to your SQL Server database instance using SQL Server management studio (SSMS) and right-click the instance, select Reports and then select Standard Reports. Click on Schema Changes History to bring the report with which you can identify databases that were deleted and the user who deleted that database.

Option4: Manually identify the full path of default trace file and look for records that contain info about the dropped database.

Automatic

This script will automatically read the default trace file and will display records about dropped databases.

 

declare @isenabled int
select top 1 @isenabled = convert(int ,value_in_use) from sys.configurations where name = 'default trace enabled'
if @isenabled = 1
begin
declare @datepara1 datetime
declare @intpara1 int;declare @intpara2 int ;
declare @currenttrace varchar(500);
declare @basetrace varchar(500);
declare @tracetabletemporary table (c1_objectname nvarchar(256) collate database_default , c2_databasename nvarchar(256) collate database_default , c3_starttime datetime , c4_eventclass int , c5_eventsubclass int , c6_objecttype int , c7_servername nvarchar(256) collate database_default , c8_loginname nvarchar(256) collate database_default , c9_applicationname nvarchar(256) collate database_default ,c10_ddloperation nvarchar(40) collate database_default);
select @currenttrace = path from sys.traces where is_default = 1 ; set @currenttrace = reverse(@currenttrace)
select @intpara2 = PATINDEX('%\%' , @currenttrace); set @currenttrace = reverse(@currenttrace)
set @basetrace = LEFT( @currenttrace ,len(@currenttrace) - @intpara2) + '\log.trc';
insert into @tracetabletemporary select ObjectName ,DatabaseName ,StartTime ,EventClass ,EventSubClass ,ObjectType ,ServerName ,LoginName ,ApplicationName ,'temp'
from ::fn_trace_gettable( @basetrace , default ) where EventClass in (46 ,47 ,164) and EventSubclass = 0 and DatabaseID <> 2 
update @tracetabletemporary set c10_ddloperation = 'DROP' where c4_eventclass = 47
delete from @tracetabletemporary where c10_ddloperation <> 'DROP'
select @datepara1 = min(c3_starttime) from @tracetabletemporary
set @intpara1= datediff(hh ,@datepara1 ,getdate())
set @intpara1=@intpara1/24;
select c3_starttime as c1_LogTime ,c2_databasename ,c7_servername ,c8_loginname ,c9_applicationname ,c10_ddloperation from @tracetabletemporary where c6_objecttype not in (21587) order by c3_starttime desc
end

With this script, you can identify

  • Time at which the database was dropped
  • Login name with which the database was dropped
  • Name of the application with which the user connected to the database engine

To recover a damaged mdf database, a SQL Server database administrator might drop a database and recreate it from backups. This will also get captured in the above script.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst fix and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.