RECOVER DROP DATABASE OBJECTS

For SQL Server 2008 and 2014,

Accidents still may happen. During the sql scripting drop objects like – views, stored procedures, functions, and triggers. And it undocumented how can be recover the deleted script. Beginning the way to recover a sql object using SQL Server fn_dblog function. Which read the database transactional log file. Finally SQL dropped Object is shown using below SQL Script.

SELECT

       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

                                                                        ‘2016/12/27’

                                                            AND

                                                                        ‘2016/12/28’)

   AND

       SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0;

GO