This article describes how ApexSQL Log reads and shows information from SQL Server database transaction logs.
SQL Server database transaction log
Each SQL Server database is stored in a set of operating system files. Data is stored in an MDF file, while the records (transactions) about the changes applied to the database are stored in an LDF file – a transaction log file. The transaction log stores enough information to replay or undo a change, or recover the database to a specific point in time. ApexSQL Log reads and analyzes transactions, and provides an output in a human-readable format.
Note that some operations, such as UPDATE, are minimally logged in a transaction log. This means that only the change is logged so the transaction cannot provide the information about the old and new values. To provide old and new values, it’s required to read and reconstruct the whole chain of transactions.
Since a transaction log is an integral part of every database, transactions are natively recorded and available for analysis by ApexSQL Log, even when it was not installed at the time of the change.
Note that in the case where the database is using the Simple recovery model, its transaction log holds only the records which describe applied changes until a checkpoint has occurred, and the data is written to disk. Such transaction log cannot be used for analysis with ApexSQL Log.
Auditing
ApexSQL Log reads operations (both DML and DDL, 45 in total) from a SQL Server database transaction log, and provides information about the changes. The SQL Server database transaction log cannot be tampered with and it always holds accurate information about the applied database changes. This makes ApexSQL Log efficient for auditing in scenarios where changes were applied on databases which were not audited.
ApexSQL Log combines different transaction log files into one logical unit during the reading process, and it shows a complete row change history for DML and DDL operations including the login of a user who performed each operation, and the time of transaction execution.
Audited information can be reviewed in the GUI or exported for later querying and analysis using several common formats: XML, CSV, HTML, or SQL script.
Recovery
In scenarios where accidentally or maliciously data or database schema was changed (e.g. UPDATE without WHERE), ApexSQL Log reads and analyzes information stored in a transaction log file and creates an undo SQL script. The script can contain all transactions found in the transaction log or only the ones you select. Executing the script on affected database will revert the changes and recover missing data or objects.
Replaying transactions
In scenarios when it’s required to replay certain transactions (e.g. to apply later changes on a restored old database backup, or to replay transactions on some other database), ApexSQL Log can create a redo SQL script. Executing the script against the database will replay the selected transactions chronologically.
