Applies to
ApexSQL Log
Summary
This article explains how to create and maintain a daily audit trail for your databases using ApexSQL Log
Description
We will address two scenarios in this article:
Incremental approach
To employ the incremental approach for a single database please:
Copy and paste following lines into a text editor of your choice:
@ECHO OFF CD <full_path_to_apexsql_log_installation_folder> ECHO apexsqllog.com /S:<sql_server_name> /D:<database_name> /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat START runreport.bat
Replace <full_path_to_report_folder> with the full path to the folder of your choice that will be used to store the reports
Please note:
In this example the report will be created in HTML format. To change the format of the report to CSV or XML use the /CSV: or /XML: switches respectively.
Incremental approach can be employed for multiple databases even on different SQL Servers or instances. To do so, add the following block of code as many as times as there are different databases you want the report to be generated for.
ECHO apexsqllog.com /S:<sql_server_name_1> /D:<database_name_1> /HTML:<full_path_to_report_folder><database_name_1>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport1.bat START runreport1.bat
Let’s say we want to audit three databases named A, B, and C. A is located on a local SQL Server which has no named instances installed, and B is located on the instance INST1 and C is located on the instance named INST2 of a remote SQL Server called SRVR. Furthermore, let’s say that ApexSQL Log is installed in its default location and that reports should be stored in D:\DatabaseAudit folder.
The batch file should look like this:
@ECHO OFF CD C:Program FilesApexSQLApexSQLLog2010 ECHO apexsqllog.com /S:(local) /D:A /HTML:D:DatabaseAuditA_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport1.bat START runreport1.bat ECHO apexsqllog.com /S:SRVRINST1 /D:B /HTML:D:DatabaseAuditB_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport2.bat START runreport2.bat ECHO apexsqllog.com /S:SRVRINST2 /D:C /HTML:D:DatabaseAuditC_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport3.bat START runreport3.bat
Please note:
To exit all the command prompt windows that’ll spawn as a result of this process add
TASKILL /IM cmd.exe /F
command at the end of the batch file. Please note, that adding this command will kill all instances of the cmd.exe process, even those unrelated to this particular batch file and therefore caution is advised if you decide to use this command.
Differential approach
To employ the differential approach for a single database you only need to add the /FROM: switch to the apexsqllog.com command discussed earlier. This means that instead of
ECHO apexsqllog.com /S:<sql_server_name> /D:<database_name> /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat
the batch files should contain the following line
ECHO apexsqllog.com /S:<sql_server_name> /D:<database_name> /FROM:%date:~10,4%-%date:~4,2%-%date:~7,2% /HTML:<full_path_to_report_folder><database_name>_%date:~10,4%-%date:~4,2%-%date:~7,2%.html > runreport.bat
while the rest of the batch file content should remain unaffected.
This way, each day at the time scheduled to run the task, an HTML report will be created containing detailed information on all of the transactions that have occurred on the database on that day only.
For differential reports to be generated for multiple databases just add
/FROM:%date:~10,4%-%date:~4,2%-%date:~7,2%
to each instance of the ECHO apexsqllog.com... line in the batch file.
About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object
Last updated
July 23, 2012
