ApexSQL Log is a powerful SQL Server transaction reader for forensic auditing and rollback of malicious and/or unintended changes for Microsoft SQL Server. It is an ideal cost solution for recovery, row changes and/or before-after auditing. ApexSQL Log uses technology which enables it to read online transaction log files or transaction log backup files in order to create auditing results including before and after views of the data as well as the full row history of all changes.
From the recovery standpoint, ApexSQL Log provides sophisticated undo/redo capabilities which enable it to create TSQL scripts which can be used to completely reverse or replay all or only selected transactions read from the online transaction log file and added transaction log backups.
To ensure that transaction log files contain the information which ApexSQL Log will use for auditing, it is important to ensure that the SQL Server doesn’t overwrite information in the transaction log file, which it does when database recovery model is set to simple. With this in mind, ensure that your databases are in the full recovery model to provide valid information for auditing and to ensure that you have the highest chance possible to recover the data when a disaster strikes and important data is lost.
To start things up, let’s quickly decide on how we want to perform the auditing: locally or remotely.
Depending on the environment, both local and remote auditing may be preferred, and the following overview of both will help you decide which to choose.
Local auditing
ApexSQL Log can be installed directly on the server which hosts the SQL Server, and auditing is hence performed locally. Since ApexSQL Log has no performance overhead during audit trail capture, it is recommended to use ApexSQL Log locally when possible, to avoid any performance issues which can be caused by network limitations or congestion in high traffic environments.
Remote auditing
If you choose to install ApexSQL Log on specific workstation, and to connect to the SQL Server remotely, it is important to know that you will need to install server-side components which enable remote auditing. These server side components are not installed on the SQL Server itself, but on the machine which hosts it, and are actually a simple service which allows remote auditing of online transaction log file. More on the components can be read in this article.
Installation of these components is straightforward as it gets. Simply start the installation executable on the server and choose the option to ‘install server-side components’. An alternative is to install them directly from the GUI when connecting to the remote SQL Server. The application will inform you when the server-side components are not installed on the remote server, and offer to install them from the GUI itself.
How it works
ApexSQL Log has a straightforward wizard which will guide the user through the auditing or recovery process.
In order to auditing specific transaction log files, it is necessary to connect to the database to which those transaction log files/backups belong to. Hence, once the application is started, user is required to provide database connection details. After choosing a SQL Server and authentication method (and providing appropriate credentials), the database can be selected from the drop-list.
Advancing through the wizard, the next step requires user to specify which data sources (transaction log files, backups or .ldf) will be used for the auditing job. ApexSQL Log automatically loads the online transaction log and all related transaction log files/backups from the default SQL Server folder. Additional files can be loaded manually by using Add file dialog, or by determining a naming pattern policy to add multiple files at once. Simply check files from the list to include them in the auditing process.
Note: when selecting transaction log files for auditing, it is important to provide a full chain of transaction log backups if you want to provide a more continuous data stream, and also provide vital transactional information. More on How to create and maintain a full chain of transaction log backups can be read here.
The next step of the wizard which follows the data sources selection process is the output selection. The choices are: Export results, before-after report, undo/redo script or to open results in grid.
Export results
This option is pretty self-explanatory – the auditing results will be exported to one of the supported formats. The following article provides detailed information on exporting in ApexSQL Log.
Creating a before-after report
This specific report with full before-after (change) details can be created as another ApexSQL Log output, which is perfect for investigating what change has occurred, and what was the original form of the changed row(s) – the ‘what, who and when’ investigation. Here is an article on how to create a data change before-after report
 
Undo/Redo script creation
This option creates a SQL Server script to revert back any unwanted changes or to replay them vs any other table/database. More on how to revert back unwanted changes or replay changes can be read here
Open results in grid
This is the option which allows in-debth analysis of audited data directly from the GUI. Auditing results will be displayed in the grid/table format where they can be further analyzed or filtered to more specific results. All three previously described outputs can be created from the grid itself, so if you are not perfectly sure on the output, and would need to inspect/modify the results before creating an export or undo/redo, opening results in the grid seems as the best option to choose here
Filtering
The final step in wizard regardless of the output type is the filter setup. ApexSQL Log offers various filters, and user can specify various date/time filters, operation filters, table filters, or use advanced options to further refine the output.
The filter setup also provides one really useful feature –continuous auditing. Continuous auditing is used in place of the date/time filter, and enables auditing job on the database to resume exactly where the previous auditing job has finished, which ensures that nothing is missed between the regular/continuous auditing jobs. More on the continuous auditing can be read in this article.
The ‘Grid’ overview
Once the auditing has been completed, all results are shown in a grid, from where they can be analyzed. Additional filtering is available in grid, so it is quite easy to refine received output or to fine tune results to see only specific transactions.
The grid overview allows the user the ‘true’ insight into the transaction log files, and various information can be found in several different panes.
Operation details
This pane shows full operation details for each and every audited transaction. This view contains full information on the before-after auditing, and you can see the before (old) and after (new) values for selected operation
Transaction information
As the name suggests, this pane contains information on the transaction in which the selected operation was included into
 
Undo & Redo script panes provide a quick glance to the rollback/replay scripts, while the complete Row history of the selected row as far as the transaction log files reach can be seen in the Row history pane.
As previously mentioned, all output options are available in the grid overview, so user can create all exports, before-after report or undo/redo scripts from the GUI. In addition to working from GUI, ApexSQL Log fully supports CLI, and all commands and operations supported in GUI are available in CLI – this enables automation of auditing with ApexSQL Log via windows scheduler or similar tools.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
