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.

   

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

   

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.

   

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

   

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

   

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

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

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.