How to recover views, stored procedures, functions, and triggers

Regardless of precautions taken to protect your SQL Server, accidents may still occur, causing serious consequences, such as 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:

In case a database is in the full recovery model – if the transaction log wasn’t truncated after the object had been dropped

In case a database is in the 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.

    Database Transaction Log Data

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

    SQL script - narrow dropped objects

    As it can be seen, there are still 129 columns, with no human-readable information whatsoever.

    To translate these columns, user needs 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 Log Contents

    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 it can be seen, 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 it can be seen from the examples provided above, this method is quite complex. There is another way to perform the recovery, and it is very simple. 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 usp.LogError in the ApexSQLLogDEMO database that was dropped by a DROP PROCEDURE statement.

To recover the dropped procedure using ApexSQL Log:

  1. Connect to the ApexSQLLogDEMO database

    Project Connection

  2. Add database backups and/or 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.

    Project transaction Logs

  3. Use the Time range filter 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

    Project Filter

  4. 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

    Filter Setup

  5. Click Finish

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

Maingrid transaction

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

Undo script dialog

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

August 5, 2013