The Row Detail pane
This pane shows the column level data for the particular operation. Listed are information about the field name, the data type, the length of the column and the value. In this pane you can also find tabs for “UNDO” and “REDO” scripts. These contain the reversing statement for the currently selected row in the main grid. From here the scripts can be copied into a query editor such as the SQL Server Management Studio and be executed. Note that only single row statements will be contained in these 2 tabs, no matter if the transaction spanned more than that one row. To reverse the effect of an operation which affected more than 1 row, you choose a different functionality from the context menu of the main grid, which I will come back to a little bit later on.
Up until now, you have an impression on the basic handling of the software. So, this is now the point from where we begin to explore what the software can do for you and how it can assist you in reversing the effects of mistakenly executed actions.
Case studies
For the following case studies I use a newly created database on SQL Server 2005 SP1. This database is set to “Full Recovery Mode” and contains 1 table named dbo.Nums. Here’s the DDL and the population script for that table:
SET NOCOUNT ON
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums
GO
CREATE TABLE dbo.Nums
(
Num INT NOT NULL
)
DECLARE @i INT
SET @i = 1
WHILE @i <= 100000
BEGIN
INSERT INTO dbo.Nums SELECT @i
SET @i = @i + 1
END
GO
ALTER TABLE dbo.Nums
ADD CONSTRAINT PK_Nums PRIMARY KEY(Num)
GO
Single row INSERT
Let’s start with a fairly simple, yet a somewhat constructed case. You wish to undo the following statement:
INSERT INTO dbo.Nums SELECT 100001
You probably ask yourself now, why we should bother with using a tool like ApexSQL Log to undo a simple INSERT statement. Well, you’re right. You don’t need the tool for this action, since you can simply issue an appropriate DELETE statement. However, this is just to get warm and show you that the software can handle such cases.
Once the INSERT statement has been fired, you can the corresponding entry in the main grid after you’ve refreshed the view:
From there you can move to the UNDO Script tab, copy the UNDO statement into a query editor and issue the DELETE statement, if you want to undo the modification immediately. Another option would be to create the UNDO script and run it during times of less(er) database activity.
Single row DELETE
Suppose we issue the following statement:
DELETE FROM dbo.Nums WHERE [Num] = 100001;
You basically apply the same steps I’ve described above to undo the single row INSERT.
Single row UPDATE
Again, typically the same procedure as above, but with the exception you should be aware of the fact that sometimes UPDATEs are done “in place ” and sometimes are not. When UPDATEs are, for some reason, not done in place, the operation consists of a DELETE followed by an INSERT. In such cases you will observe 2 entries in the log. To properly recover, you should make sure that you catch all affected rows from the log. This can easily and safely be done by using the “Select All Rows in this Transaction” function from the context menu of the main grid.
ApexSQL Log will automatically mark all rows affected by the transaction in the main grid.
Note that you need to take one more step, before you can create an undo script. You need to mark all these rows as checked before you can create that script. This functionality is also provided from the context menu of the main grid.
Now you can create your undo script and reverse the effect of the transaction.