Find who dropped Database at what time
Scenario
As a DBA often we face a scenario that someone has dropped the database and we need to find out who dropped it. We can easily find the information from Schema Changes History and SQL Server Log. But here we find an excellent stored procedure to find the same at https://raresql.com.
Script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Use Master 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 -- Run the below-stored procedure to find the deleted database info EXEC Recover_Dropped_Database_Detail_Proc GO |
Report
Comments
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.
Google
One of our guests not too long ago advised the following website.