How to recover views, stored procedures, functions, and triggers: ApexSQL Log vs fn_dblog function

Regardless of precautions taken to protect your SQL Server, accidents may still occur, causing serious consequences, such as are data and objects loss. We will now analyze two possible ways to recover SQL objects (views, stored procedures, functions, and triggers) lost to accidental DROP statement use

The first way to recover dropped SQL objects is to use the undocumented SQL Server fn_dblog function, which reads online database transaction logs, and can provide information about the objects. More precisely, the function can help in the following cases:

- for the database in full recovery model – if the transaction log wasn’t truncated after the object had been dropped

- for the database in simple recovery model – if the transaction log is intact (not overwritten by newer entries)

What steps need to be undertaken to recover a dropped object via the fn_dblog function? The procedure is the same for all objects. Shown below is the example for stored procedures:

1. Execute the fn_dblog function

   SELECT
          *
     FROM sys.fn_dblog(NULL, NULL);
   

   The resulting table contains complete database transaction log data, divided in 129 columns
   
2. To narrow down the results to the ones representing dropped objects, execute the following SQL script, using “DROPOBJ” as the value for the transaction name column:

   SELECT
          *
     FROM sys.fn_dblog(NULL, NULL)
   WHERE [transaction name] IN ('DROPOBJ');
   

   
   As you can see, we still have 129 columns, with no human-readable information whatsoever
   To translate the columns, you need to be familiar with the format, status bits, and their total number, and with some other characteristics beside. Unfortunately, no official documentation is available for this function, which makes the task a bit trickier
   The following columns contain the information about the objects affected by the committed transaction: RowLog Contents 0, RowLog Contents 1, RowLog Contents 2, RowLog Contents 3, and RowLog Contents 4
   
   Row data is stored in different columns, based on the operation type. To see the exact required information using the fn_dblog function, you need to know the column content for each transaction type
3. Finally, to get the CREATE PROCEDURE script, in order to re-create dropped procedure, the following complex SQL script needs to be executed

   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
         '2013/07/31'
        AND
         '2013/08/1')
      AND
          SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0;
   GO
   

   As you can see, the script finds all related transactions, using the user-specified time frame for narrowing down the search, and converting hexadecimal values into readable text
   The fn_dblog function is a powerful one, but it has limitations. For example, reading transaction log records for object structure changes usually involves the reconstruction of several system tables’ states, while only the active portion of the online transaction log is being read

As you can see from the examples provided above, this method is quite complex. There is another way to perform the recovery, however. You may use ApexSQL Log, a SQL Server recovery tool capable of reading transaction log data and recovering lost SQL objects to their original state by rolling back transactions

Let’s say there was a stored procedure named AUDIT_prc_AggregateReport in the AdventureWorks2012 database that was dropped by a DROP PROCEDURE statement

To recover the dropped procedure using ApexSQL Log:

1. Connect to the AdventureWorks2012 database
   
2. Add transaction log backups and/or detached transaction logs containing the data required to create the full chain and provide all transactions up to the point in time when the procedure was dropped. Use the Transaction logs tab to perform the operation
   
3. Using the Database backups tab provide the full database backup to be used as the starting point from which the full chain of transactions will start
   
4. Use the Filter tab and the Time range section to specify the target point in time for the recovery process (the time frame when the procedure was dropped). This will narrow down the search and speed up the reading process
   
5. Finally, use the Operations filter to narrow down the search to the DROP PROCEDURE statements only. To do this, deselect all DML and DDL operations except DROP PROCEDURE. For other dropped objects (views, stored procedures, functions, and triggers), an appropriate option should be selected instead
   
6. When everything has been set, use the Open button to start the reading process

When the process has finished reading, the main grid will show the transaction that can be rolled back, in order to recover the dropped procedure

Finally, to perform the recovery, right-click the selected row, and choose the Create undo script option from the context menu. This will open the Undo script dialog containing the SQL script, which may be either executed immediately or saved for later use

Unlike the fn_dblog function, ApexSQL Log offers a simple point-and-click recovery technique, which doesn’t call for initial knowledge of SQL scripting and transaction log structure. Furthermore, ApexSQL Log is capable of reading the information stored in both online and transaction log backups, which further simplifies the recovery procedure