Ways to Track Deleted Database in SQL Server

SQL Server’s Schema Changes History we can recognize the name of databases that were dropped from the SQL Server. In a big SQL Server environment, interfacing with each Schema Changes History of each SQL Server case is an extremely troublesome task. In this blog, we will take a glance at the steps which you can trace to a rapidly auto-track deleted database in SQL Server.

Important Solution: Are you facing problems in recovering deleted database components from SQL Server if yes then try SQL Repair Tool Software to export deleted database object live to SQL Server.

There are two unique techniques by which one can without much effort track who deleted the database in SQL Server. The principal technique is using the built-in Schema Changes History Report. The subsequent technique is to load the SQL Server Default Trace into a table to see who deleted the database.

Methods to Track Deleted Database in SQL Server

1. Track Database Using SQL Server Schema Changes History Report

1. Open SQL Server Management Studio and connect with the SQL Server Instance.
2. Right-click SQL Server Instance and Select Reports – > Standard Reports – > Schema Changes History.
3. Then, Scheme Changes History report will open, which will have the insights concerning who deleted the SQL Server Database along with the time when the database was deleted.

2.Track Database Using Default Trace Files

The SQL Server Default Trace file gives exceptionally helpful data to a DBA to comprehend what’s going on the SQL Server Instance.

Execute the below query to trace the default path of trace file in SQL Server.

SELECT

           path AS [Default Trace File]

          ,max_size AS [Max File Size of Trace File]

          ,max_files AS [Max No of Trace Files]

          ,start_time AS [Start Time]

          ,last_event_time AS [Last Event Time]

FROM sys.traces WHERE is_default = 1

GO

Step by Step Instructions to Load SQL Server Trace File in SQL Server Table

Note: Try SQL Log Analyser Software to Recover Database from Corrupted LDF Files.

Execute the beneath script to load the default trace file content in a transitory table to read the significant data as for who deleted the client database in SQL Server. If you don’t find the significant data in the most recent trace file, at that point it is prescribed to load the data from all the accessible trace files on the server to explore the data.

USE tempdb

GO

IF OBJECT_ID(‘dbo.TraceTable’, ‘U’) IS NOT NULL

          DROP TABLE dbo.TraceTable;

SELECT * INTO TraceTable

FROM ::fn_trace_gettable

(‘G:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\log_12.trc’, default)

GO

SELECT

           DatabaseID

          ,DatabaseName

          ,LoginName

          ,HostName

          ,ApplicationName

          ,StartTime

          ,CASE

                   WHEN EventClass = 46 THEN ‘Database Created’

                   WHEN EventClass = 47 THEN ‘Database Dropped’

          ELSE ‘NONE’

          END AS EventType

FROM tempdb.dbo.TraceTable

          WHERE DatabaseName = ‘MyTechMantra’

                   AND (EventClass = 46 /* Event Class 46 refers to Object:Created */

                             OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */

GO

Wrap Up

In this blog, we have perceived how effectively one can track a deleted database in SQL Server with the assistance of an inbuilt SQL Server Schema Changes History Report or by using default trace files. Moreover, if your database files are corrupted or highly damaged then, in order to recover the database files, we recommend using a third-party tool such as SQL Recovery Sofware.