Sometimes, it is very important to know when your database has been dropped as well who dropped it. Obviously, if you setup the database backup properly, you can easily recover it from the last backup but how to find who dropped/ deleted the database ? Today, I came across this issue and I started my research and found some solutions to recover this info using trace, however, I developed a script that will help you find who dropped database, at what time, by using SQL Server Log.
Note : Please do not use this script for any negative purpose.
Script :
--This script is compatible with SQL Server 2005 and above.
USE master
GO
DROP PROCEDURE Recover_Dropped_Database_Detail_Proc
GO
CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
;WITH CTE AS (
Select REPLACE(SUBSTRING(A.[RowLog Contents 0],9
,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Database Name]
,[Transaction ID]
FROM fn_dblog(NULL,NULL) A
WHERE A.[AllocUnitName] ='sys.sysdbreg.nc1'AND
A.[Transaction ID] IN (
SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%dbdestroy%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))
SELECT
A.[Database Name]
,B.[Begin Time] AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%dbdestroy%'
INNER JOIN sys.sysusers C ON B.[Transaction SID]=C.[Sid]
GO
EXEC Recover_Dropped_Database_Detail_Proc
GO
Let me know if you came across this issue and its solution as well.
SQL Server Portal 