ApexSQL Log is a SQL Server database transaction log reading tool which can read online transaction logs, detached transaction logs, and transaction log backups – both native and natively compressed.
As a SQL Server transaction log viewer, it’s focused on DML (data) and DDL (structure) operations, and what data or objects were changed by the operations. In addition to showing the logical content of a transaction log file, ApexSQL Log provides more features, like a row history for DML operations, creating Undo/Redo scripts, and exporting the audited information in various formats.
ApexSQL Log is useful in addressing the following requirements:
Audit changes on a database schema or data
In scenarios where no auditing was previously set on a database, or existing auditing cannot provide sufficient information, ApexSQL Log can help. It reads an online, detached, and transaction log backups and provides detailed information about all DDL and DML operations (45 in total) that were executed on the database. The audited information can be shown either in the application main grid or exported to an HTML, XML, SQL, CSV, or SQL Bulk file.
Some of the most common auditing cases where ApexSQL Log can greatly help are:
- Forensic auditing – post incident investigation of unattended changes will result in discovering who exactly changed what, when and how
- Schema (DDL) auditing – instead of using database triggers which can be very robust, audit transaction log files to examine all schema changes
- Before-after auditing – audit all insert, update and delete operations to discover actual values of table row (field) before and after the change has been made, as well as explore full history of changes from the moment the value was initially inserted onwards
- Continuous auditing – continuously read transaction log files and audit the data directly to the SQL Server repository database and ensure that no auditing data is missed or duplicated to ensure solid foundations for reporting or similar tasks
Troubleshoot security issues
If some changes were made on a database and you need to find out who made them, use ApexSQL Log. Along with the other information about each transaction recorded within a transaction log, SQL Server records a username of a user who performed the change. More on security auditing can be found in the Audit SQL Server database security changes and Audit SQL Server permission changes to improve overall security articles.
Repair data that was inadvertently updated or deleted
One of the most common scenarios where ApexSQL Log can help is when data was inadvertently updated or deleted and it needs to be reverted to its original value. To do that, use the Create undo script option. The undo script can be created either for all transactions or just the ones you select, depending on what you need to be rolled back. The How to recover SQL Server data from accidental UPDATE and DELETE operations article provides a full guide on rolling back inadvertent changes.
Load testing
With ApexSQL Log, production server load can be recorded and replayed to the testing production where stress tests of a target database can be conducted. Users can use ApexSQL Log to perform both Load testing: Recording production transactions and replaying on a test server as well as Using transactional data replication to replay and test production loads on a staging server.
Repair a database schema was inadvertently changed
If database objects were altered or dropped accidently and you need to recover them, ApexSQL Log can provide an undo script that will re-create the dropped objects or revert the changes. In the 4 techniques for recovering lost tables due to DROP Table operation article, we showcase how to recover dropped tables, and in the How to recover views, stored procedures, functions, and triggers article we offer a guide on recovering other SQL Server objects.
Restore a database to a point in time
There are various scenarios when restoring a database to a point in time can be required. For example, a failed large batch update process, storage media failure, providing a database state from a certain time frame for auditing or developing purposes, etc. Using the Time range and other filters in ApexSQL Log, you can precisely specify the transactions you want to apply to the database which means that ApexSQL Log not only allows full database point in time recovery, but this process can be focused only on specific objects or even on specific users. Here is an article on Reverting your SQL Server database back to a specific point in time.
Database replication
Transactional replication and database consolidations are prime examples of database replication which can be achieved using ApexSQL Log. The tool can be used to read transaction log files and to replicated transactions made on the production database and apply them to the database replica as can be seen in the Hands free, no-coding SQL Server database replication of a reporting database article. Similar method can be used to replicate transactions made on multiple databases and consolidate the auditing into a single repository database, making maintenance and reporting tasks less stressful/demanding and more time efficient by working on a single repository instead of working on many databases for the same purpose. The Synchronizing databases using the transaction log – SQL Server Log shipping article offers a good starting point for the second use case.
Zero data loss migrations and restores
Migrating databases and restoring database backups have one major flaw in common – in both cases, the new database changes which occur during the migration/restore process are lost which can be a major issue, especially with the fact that some migration processes may take hours, or even days. Luckily, ApexSQL Log can be used in order to fully fill this gap and audit all database changes which occur during migration or restore processes and once the process is finalized to add the ‘missing’ data/schema changes to the new database and ensure absolutely zero data loss. The SQL Server database migrations with zero data loss and zero downtime article offer guide on how to complete mentioned processes while ensuring zero losses.
April 28, 2015
