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.
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.
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'
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)