How can I see last deleted database database database database

Question

Is it possible to refer to the last deleted file in the database? If yes, how can I do that?

The goal is to recreate it.

Answer 1

Well I was able to save what was deleted in 2 steps.

The solution came from a reply of SO in English that the Randrade showed me, I will share here because it can save the skin of another person ^^.

 1. Passo:

    CREATE PROCEDURE [dbo].[sp_Recover_Dropped_Objects]
        @Database_Name NVARCHAR(MAX),
        @Date_From DATETIME,
        @Date_To DATETIME
    AS

    DECLARE @Compatibility_Level INT

    SELECT @Compatibility_Level=dtb.compatibility_level
    FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name

    IF ISNULL(@Compatibility_Level,0)<=80
    BEGIN
        RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
        RETURN
    END

    Select [Database Name],Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
    from fn_dblog(NULL,NULL)
    Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
    And [AllocUnitName]='sys.sysobjvalues.clst'
    AND [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]='DROPOBJ'
    And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
    And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0

 2. Passo:
EXEC sp_Recover_Dropped_Objects 'Database_Name','2017/10/06','2017/10/06'

Answer 2

Yes, it is possible.

With the SELECT below, given example, you can get all logs related to the DROP’s of your base, as beginning of the query, the type of operation and other transaction Ids, all of them obtained by the function fn_dblog.

SELECT 
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

To discover the user, just take the value of the Transaction SID column (obtained from the SELECT above) and pass it in the function SUSER_SNAME(), which returns the name of the user who performed the DROP.

Ex:

SELECT SUSER_SNAME(VALOR_TRANSACTION_SID)