SQL Server is a powerful database management system used by many organizations to store and manage their data. However, it is important to have mechanisms in place to audit and recover from any unintended or malicious changes that may occur. This is where ApexSQL Log comes in.

ApexSQL Log is a robust SQL Server transaction reader that enables forensic auditing and rollback of changes made to the database. It can read online transaction log files or transaction log backup files to provide before and after views of the data, as well as the full row history of all changes.

From an auditing perspective, ApexSQL Log is a cost-effective solution that allows you to track and analyze changes made to your SQL Server database. By using the technology provided by ApexSQL Log, you can easily identify and investigate any unauthorized or unintended modifications to your data.

When it comes to recovery, ApexSQL Log offers sophisticated undo/redo capabilities. It can generate TSQL scripts that can be used to completely reverse or replay selected transactions from the transaction log file or transaction log backups. This ensures that you have the highest chance possible to recover your data in the event of a disaster.

One important consideration when using ApexSQL Log is the recovery model of your SQL Server database. To ensure that transaction log files contain the necessary information for auditing, it is crucial to set the database recovery model to “full”. This ensures that the SQL Server does not overwrite information in the transaction log file.

Performing Auditing: Locally or Remotely

ApexSQL Log provides the flexibility to perform auditing either locally or remotely, depending on your environment and preferences.

If you choose to install ApexSQL Log directly on the server hosting the SQL Server, you can perform local auditing. This is recommended to avoid any performance issues that may arise from network limitations or congestion in high traffic environments.

Alternatively, you can install ApexSQL Log on a specific workstation and connect to the SQL Server remotely. In this case, you will need to install server-side components on the machine hosting the SQL Server. These components enable remote auditing of the online transaction log file.

How It Works

ApexSQL Log has a user-friendly wizard that guides you through the auditing or recovery process. To audit specific transaction log files, you need to connect to the database to which those transaction log files or backups belong.

After providing the necessary database connection details, you can select the data sources (transaction log files, backups, or .ldf files) to be used for the auditing job. ApexSQL Log automatically loads the online transaction log and related transaction log files/backups from the default SQL Server folder. You can also manually add additional files using the “Add file” dialog.

Once the data sources are selected, you can choose the desired output format. ApexSQL Log offers options to export results, create before-after reports, generate undo/redo scripts, or open results in a grid for in-depth analysis.

Regardless of the output type, ApexSQL Log provides various filters to refine the auditing results. You can specify date/time filters, operation filters, table filters, or use advanced options to further customize the output. The filter setup also includes a useful feature called continuous auditing, which allows the auditing job to resume where it left off, ensuring that no changes are missed between regular auditing jobs.

The Grid Overview

Once the auditing is completed, all results are displayed in a grid for analysis. The grid overview provides a comprehensive view of the transaction log files, with various information available in different panes.

The operation details pane shows the full details of each audited transaction, including the before and after values for the selected operation. The transaction information pane contains details about the transaction in which the selected operation was included.

ApexSQL Log also provides undo/redo script panes for quick access to rollback or replay scripts. Additionally, the row history pane displays the complete history of the selected row as far as the transaction log files reach.

From the grid overview, you can create exports, before-after reports, or undo/redo scripts. ApexSQL Log also supports command-line interface (CLI), allowing for automation of auditing tasks through tools like Windows Scheduler.

In conclusion, ApexSQL Log is a powerful tool for SQL Server auditing and recovery. It provides the ability to track and analyze changes made to your database, as well as the capability to recover from any unintended or malicious modifications. By using ApexSQL Log, you can ensure the integrity and security of your SQL Server data.