Internal structure of ApexSQL Log audit database repository

ApexSQL Log, a SQL Server recovery tool capable of reading transaction log data and recovering lost SQL objects to their original state by rolling back transactions, provides various comprehensive export formats of audited data. Besides XML, CSV, and HTML format, the following formats are also available:

• The “SQL Script file output” option – generates a SQL script that inserts the transaction information and operation details into two custom ApexSQL Log’s repository tables (APEXSQL_LOG_OPERATION and APEXSQL_LOG_OPERATION_DETAIL).
• The “SQL bulk files output” option – uses and populates the APEXSQL_LOG_OPERATION and APEXSQL_LOG_OPERATION_DETAIL tables, but the approach differs – for large volumes of audited data the previous option might prove slow since each operation is being inserted separately. To overcome this problem, ApexSQL Log offers the SQL bulk files export option. It creates the files necessary for import of audited operations into repository tables through the BULK INSERT command.

The following example shows the part of the exported SQL script used to store transaction information about one of the updates:

INSERT INTO APEXSQL_LOG_OPERATION
VALUES
       (N'00000031:00000108:0002'
 , N'UPDATE'
 , N'Customers'
 , N'LENOVOIKI'
 , N'20130502 14:09:51.836'
 , N'LENOVO'
 , N'ACMEDB'
 , N'dbo'
 , 0
 , 1);
INSERT INTO APEXSQL_LOG_OPERATION_DETAIL
VALUES
       (N'00000031:00000108:0002'
 , 1
 , N'Name'
 , N'varchar(50)'
 , N'Jack'
 , N'Jack Jackson');
GO

Both the SQL script file and the SQL bulk files export options populate repository tables (APEXSQL_LOG_OPERATION and APEXSQL_LOG_OPERATION_DETAIL) with captured data

• The log sequence number (LSN) that uniquely identifies each SQL Server transaction
• The operation type (any DML or DDL operation), such as INSERT, UPDATE, ALTER TABLE, or DROP TRIGGER
• The name of the affected object, such as is a table or trigger name
• The user name
• Transaction start and end time
• Server and database names

On the other hand, the APEXSQL_LOG_OPERATION_DETAIL table includes extended transaction information, such as:

• The column (field) name and its type (i.e. varchar, int, or money)
• Field values (old and new)
• LSN number used to pair the information from the two repository tables (APEXSQL_LOG_OPERATION and APEXSQL_LOG_OPERATION_DETAIL)

Here is an example how to query captured information:

SELECT
       TRANSACTION_BEGIN AS [Time]
 , OPERATION_TYPE AS [Operation]
 , OBJECT_NAME AS Object
 , COLUMN_NAME AS [Field name]
 , OLD_VALUE AS [Old value]
 , NEW_VALUE AS [New value]
 , [DATABASE]
 FROM
       dbo.APEXSQL_LOG_OPERATION t
 INNER JOIN dbo.APEXSQL_LOG_OPERATION_DETAIL r
 ON r.LSN = t.LSN;

The result will look like this if SQL Server Management Studio is used to run the query:

Using the latter, you are able to create your own customized auditing reports (since all captured data is stored in SQL Server tables as the repository), just like presenting any other user data stored on SQL Server.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.