ApexSQL Log 2005

1. Introduction

Apex SQL Log version 2005 is the only tool available in the

market for transactional analysis which has support for SQL Server 2005.The

Apex SQL Log added the support for SQL Server 2005 in its release 1.90(Feb 22

2006).The current version of the tool is 2005.03

The main features of the tool are:

1. Support for SQL Server 2005(SQL Server 2000 and 7.0 are still supported)
2. Support for 64-bit
3. Improved user interface
4. Export records in the XML format, excel and SQL script format.
5. Recovery wizard with 5 recovery options
6. The tool does not need log backups for data recovery.
7. The tool can attach itself to live logs or log backups or detached logs.
8. Data can be recovered to a large extent, even if the recovery model is Simple(Provided the time between the data loss and recovery is small).
9. Can audit multiple transaction logs of different databases at a time.i.e The user can attach the tool to "Adventure works" database and the same time can attach to the "pubs" database, thus enabling simultaneous auditing.
10. Command Line Interface

The ApexSQL Log evaluation version can be obtained from http://www.apexsql.com/downloads.asp#log.

2. Installation Procedure

The installation procedure is very simple(as usual).The user can click the "Next" button and finish the installation within a minute.

3. Log Selection wizard

1)In the Log selection wizard, select the authentication method and click "Next"

2) The ApexSQL Log can attach itself to the live logs of the database or log backups or the detached logs.

Live Log | Online transaction log

Log backups | Transaction Log backups

Detached Logs | Detached logs are taken by the following steps:

1) The database is detached from the server.

2) A backup of the LDF file is taken by copying the LDF file

in a location other than the SQL Server default folders.

3)The database is attached to the server again.

Click "Finish" button.

If the recovery model of the database is "Simple", a warning message is displayed.

Change the recovery model to "Full" from Database Properties

Options.

3) Click "Evaluate" button.

4) Click the "Create new view" option.

5) Click "Next" button.

The next stage is to create log filters.In this step,we have to determine what actions has to be audited. For example,

1) Audit the insert and delete operations.

2) Mention the tables to be audited.

3) Audit the users with specific user IDs

The user can save these log filters which have extension "*.axlf".

Click "Finish" button.

4. Main Application Window

The main window consists of three sub windows

1) Server Explorer This contains

information of the server to which the tool is connected. On expanding the

server node, it gives information about the logs of the database to which it is

attached to."Open Log" node gives information of the logs which are open

currently.

2) Log view The transactions of all type are displayed in the

pane.

There are four tabs

a) Row Details

b) Row History

c) Undo Script

d) Redo Script

The user can select the necessary tab and view the details.

a)Row Details

The current row sub window provides details of the row selected in the Log view. It gives information about the state of the row, the operation performed on the row,schema,table name etc.

Menus in the ApexSQL log Tool

The Row Data tab shows the old value and the new value if the row has undergone a update operation.When the user clicks the "" button,the column data viewer shows the old value and the new value of a column in that row.

b) Row History

The update operation was performed on the table "Employee" on a particular row twice. So row history also contains two rows.

c) Undo Script

The undo script tab here generates the undo operation for the insert statement.

d) Redo Script

Redo Script tab generates the insert operation script.

The status bar of the application gives information of the database name,user name,name by which the filter file is saved,the total number of log entries returned and the maximum log entries allowed.

5. Menus in the ApexSQL Log tool

File -> New Log

The File -> New Log opens the Log Selection wizard. Please refer

section 3 for more details.

File -> Recovery Wizard

Choose the authentication method and the database to be used in the recovery wizard.

ApexSQL Log offers 5 types of recovery options.

1. Recover the data which was lost due to DELETE operations.
2. Recovery of data lost due to TRUNCATE operations.
3. Recovery of data lost due to DROP TABLE operations.
4. Recover data lost from DETACHED databases
5. Recover lost objects (tables, procedures etc.) from drop operations.

1) Recover the data which was lost due to DELETE operations - Suppose the user issues a delete statement for the "Suppliers" table in "Pubs" database and forgets to mention the criteria in the where statement,all the data in Suppliers table gets deleted.

We can recover the data lost due to this delete operation with the help of recovery wizard.

Step 1:

Click the first option and click the "Next" button.

Step2:

The user can choose the options for data availability. The user can choose the option "Relevant data is available" if log backups or detached log files or online logs set to full recovery mode is known to exist. The other option is selected when the logs have been truncated or recovery model of the database is set to simple or if there are no backups.

The user can mention the time range. The data lost only during this span will be recovered.

Click "Next" button.

Step 3:

We have attached the ApexSql Log to the online logs of the pubs database. So we will recover the data from the live log of the database. The users have options to recover it from detached log file or the transaction log backup.

Click "Next" button.

Step 4:

Click the "Suppliers" table.

Click "Finish" button.

Since only the evaluation version is installed on the server,only every 10^th row is recovered.

2) Recovery of data lost due to TRUNCATE operations.

Step 1:

Choose the second option.

Step2:

Select the table to recover

Click "Finish" button.

Click the "Execute" button present in the top portion of the Recovery wizard script window. The script is executed and results are as below:

3) Recovery of data lost due to DROP TABLE operations

Step 1:

Select the third option

Click "Next" button.

Step 2:

Click any of the options depending upon the data availability.

Step 3:

The user can add backup file or detached log file. Click "Finish" button.

Click "Finish" button.

Issues with the recovery script:

1)Here the table is created in the script with not null constraints for all the columns.The identity key is not created for any column. But just before inserting the rows the identity insert is set to on. This generates an error.

2)Now,assuming we remove the set identity_insert statements and then run the script.The script tries to insert null values.This is because the dropped rows contained null value columns.This again generates an error.The table structure script is not generated correctly by the tool.

Issue with the Recovery Wizard

This happened a lot of times.Whenever I click recovery wizard a message box as shown below appears.

4) Recover data lost from DETACHED databases

The Detached databases can be created by

1) Detaching the database from the server

2) Taking the backup of the MDF or NDF files by copying it to any location other than the default one.

Step 1:

Select the 4^th option and click "Next" button.

Step 2: Add the database file.

Click "Finish" button.

The script is generated for table structure and data.

The table structure is not generated correctly.The columns which cannot allow null values in SQL server are generated as columns that can allow null values and vice versa.

For example in the above script,in the "Orders" table OrderID is the primary key as in SQL Server database.Here the script has generated it as a column which can allow null values.

5) Recover lost objects (tables, procedures etc.) from drop operations.

Step1:

Select the 5^th option and click "Next" button.

To demonstrate this restore feature,I dropped a procedure in the "Northwind" database.

Step2:

We have attached the tool to the online log of the database. So we have relevant data available.

Click "Next" button.

Step3:

Click "Finish" button.

The script recovered the earlier dropped table "test8" and has not recovered the dropped stored procedure.

File -> Export As

The log records can be exported to XML,CSV or SQL Script file.

Only the checked records can also be exported.

File -> Export DDL -> Undo Script

We had earlier dropped the table "Suppliers". So in the undo script, the table is created and all the other tables are dropped. Similarly redo script can be generated.

Edit Menu

Edit -> Refresh Rows

It's used for refreshing the logs.

The remaining functionalities in the edit menu are very simple.

Select all Rows -> Select all the rows in the grid

Check All -> The checkbox next to the transaction is checked for

all rows.

Uncheck all -> the reverse of check all

Select all rows in this transaction -> all rows of a particular transaction are selected.

Grid Menu

Grid -> Find

Find, as the name suggests, searches a word in the grid or details tab or the history tab.

Grid -> Show group by

The transactions in the grid can be grouped by state, begin time, operation, schema, table, user etc. by dragging the column header and placing it above all the columns.

This can be done for all the other columns also.

Sub grouping also can be done.

Data can be filtered as follows:

Grid -> Multi-column Sort

The transactions in the grid can be sorted in ascending or descending order.Sorting according to multiple columns can also be done.

Grid -> Multi Column Group by

Grouping can be done from the grid menu without dragging the column header.

Grid -> Show Column Chooser

The user can choose the columns they want to see in the grid.

Tools Menu

Tools -> Options

It specifies the editor to be used by Apex SQL Log to generate the scripts.Current selection is MSSQL Query Analyzer.

Tools -> Create Undo Script

Click the check box of any row in the grid. Go to Tools -> Create Undo Script.

>

Similar way the redo script can be generated for a single row or range of rows.

Command Line Interface

The ApexSQL Log provides a powerful command line interface. The user can perform many operations from the command line.

Type ApexSQLLog.com at the command prompt

The various options are listed out as the output.

Now let's connect to the server.

/S - The server to which the interface has to connect

/login - Login name

/password - Password for that login

/d - Database Name

/O - Online Log File's Logical Name

/xml - Export the audited records in XML format to a particular file

The command line interface can be attached to backup file using /B (Log backups) or to /D (Detached Log File) options.

The operations the user can perform are

1) Generate Undo scripts using /undo

2) Generate Redo Scripts using /redo

3) Generate the DDL scripts using /ddl

4) Generate undo DDL scripts using /undo-ddl

The users can filter the log records by using the filtering options

Now we will filter the log records of "Pubs" database to have only "Insert" operations

We can know whether the server components are installed or not using the following command.

If the user wants to print the server messages it can be done as follows:

Issues:

1)I get a system exception sometimes when I try to attach the tool to the online log.

2) The table structure in the recovery script is not generated correctly.They are all created with Not null constraints. This creates a problem when null values have to be inserted.

3) Recovery scripts are not accurate. They generate errors. User has to modify the queries for the successful execution.

Conclusion:

ApexSQLLog is a great tool having no competitor as of now in the SQL Server 2005 area. It has issues but I guess it will surely be fixed in the future releases. This tool will be of great help for DBAs working on 7.0,2000 and 2005 versions.