How to create an undo/redo script in ApexSQL Log

This article explains how to use ApexSQL Log to create an undo or redo script.

ApexSQL Log is a SQL Server auditing and recovery tool which reads SQL Server database transaction logs. Along with its auditing capabilities, it provides undo/redo T-SQL script creation which allows you to rollback or reconstruct transactions recorded in the transaction log.

Note that the created undo/redo script, in most cases, will not be the same as the original one that was used to create the transactions. This is because the original script is not logged in the transaction log, but only the transactions that were executed according to the script. However, the redo script will have exactly the same effect on data and database schema. Similarly, the undo script will have the opposite effect to roll-back the operations as if they’ve never occured.

There are several methods to create an undo/redo script.

The Result viewing options dialog in ApexSQL Log

From the main grid

Opting for Open results in grid will populate the main application grid with transactions found in transaction logs selected in the session wizard according to filter options used.

There are three methods to create undo/redo scripts using the main grid. The first method is to use the Create undo script or Create redo script options in the Home tab of the main application toolbar.

Create undo script and Create redo script options in the Home tab of ApexSQL Log

Either option will generate the appropriate script for all checked transactions in the grid or, if none of the transactions is checked, the script will be generated for the selected transaction. The Created script will be shown in the Undo (Redo) script dialog which can be used to review, edit, or save the script for later use (e.g. using SQL Server Management Studio). The script can be immediately executed against the database connected in the session wizard using the Execute option.

Using the Execute option to execute script immediately

The second method is to use the context menu of the grid. It provides the same two options as described in the first method.

The third method is limited per one transaction and it requires copying and pasting the script from one of the tabs (Redo script and Undo script) for a particular operation. Both tabs are located below the grid. To view appropriate scripts for a particular operation, select the operation and the tab for the desired script type.

Figure illustrating an example of creating an Undo update script using ApexSQL Log

Batch file

The Create batch file option allows you to create a batch file that can be executed later without utilizing the ApexSQL Log GUI.

Use this option to proceed to the Create batch file dialog. This dialog allows you to select the desired script type (undo or redo) while showing the content of the batch file that will be generated at the same time.

The batch file contains one CLI command with set for the options used during the session wizard.

The output path where the script will be generated can be set using the same dialog.

The Create batch file dialog in ApexSQL Log

Export results

The Export results option provides a way to create undo/redo script directly to a .sql file. This option creates a script file without utilizing the application main grid, which speeds up the process since no information is rendered for showing in the grid.

The Export results to file option in ApexSQL Log