How can I query the last deleted database procedure

1

Is it possible to check which was the last procedure deleted in the database? If so, how can I do this?

The goal is to rebuild it.

    
asked by anonymous 06.10.2017 / 19:33

2 answers

1

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

The solution came from a answer 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'
    
06.10.2017 / 19:54
-1

Yes, it is possible.

With SELECT below, given as an example, you can get all logs related to DROPs from your database, such as query start, transaction type, and other transaction IDs, all of which are obtained by the fn_dblog function.

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

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

Ex:

SELECT SUSER_SNAME(VALOR_TRANSACTION_SID) 
    
06.10.2017 / 19:53