Transaction UnDo

ApexSQL Audit includes the capability to reverse audited transactions down to the cell level. To open the form click on "Undo Transaction" from the Outlook Bar, Audit Menu or Toolbar as shown here:

Buttons

The Undo Transaction module is structured in 4 steps.

Filter

Use the Filter criteria to select a subset of transactions. These are individual database operations that may affect one or more rows. You can filter by any number of criteria including the date the operation was executed, table, user, application, computer and action.

Table Name: You may chose to search for only one table. It can be used to narrow the search criteria.

Modified By: Designates the user who made the change.

Host: This is the computer on which the database operation was initiated.

Application: This is the Application through which the database operation was performed. Examples are Visual Basic, Query Analyzer and SQL Enterprise manager.

From Date and To Date: Allows the user to select actions within, greater than or less than specific dates.

Action: Can be Update, Insert or Delete. If you don't select this option will return all actions.

To search for the transactions that match your filter criteria simply hit button.

Examples of transactions are as follows:

1)  "Delete From MyTable" executed in Query Analyzer

2)  Editing a row in Enterprise Manager that changes the value of a cell

The following graphic illustrates some transactions including batch updates, deletes and individual inserts.

Select Transactions / Transaction Data to UnDo

Now that you have filtered out a subset of transactions you can select the transactions you wish to reverse. ApexSQL Audit provides the granularity to reverse transactions at the row and cell level if you don't want to reverse the entire transaction. We refer to Row and Cell level as the Transaction's Data.

The "Transaction's Data" grid will display all of the cells for all of the affected rows. If only one row is affected then only the affected cells in that row will be displayed. If more than one row is affected then all of the affected cells for all of the affected rows will be affected.

The following graphic illustrates the Cell level data for a particular Insert. Each row in this grid represents the data for a particular field affected by the inserted row, where the row level insert and field intersect is a referred to here as a Cell.

UnDo Transactions

Once you have selected the transactions you want to reverse click . A preliminary analysis will be conducted as to the feasibility of the UnDo transaction and a report will be displayed.

Key Information

UnDo Status: Doable = this UnDo is possible, Undoable = this UnDo is not possible. Note Comment field for explanation.

The Cardview control on the right is just a vertical representation of the row you have currently selected. This offers a more convenient view of the row's data.

Commit or Rollback your Undo

The UnDo operations are executed as soon as you click in the above step but within a transaction. This allows you to view the results via the following interface and provides an opportunity to Rollback the transaction thus cancelling the entire UnDo operation.

If you choose Commit, the undo operation will be committed against the database and the original transactions will be undone.

We recommend auditing identity fields along with others to avoid problems in incorrect undoing. Incorrect undoing would if you try to undo deleted rows, the identity fields will get new values instead of ones that were before delete. If other data reference to them then the undo procedure will not work correctly.

By default we exclude primary key, row key fields and BLOB fields to reduce amount of storage data. But you can change this behavior. See more information here.

Context Menus

The table below contains the commands in the context menus of the two grids above:

Copyright © 2008 ApexSQL LLC. All Rights Reserved.