CLI preview feature – ApexSQL Log

ApexSQL Log is an auditing and recovery tool for SQL Server databases that audits, reverts, or replays data and object changes that have affected a database. Along with its GUI, ApexSQL Log provides powerful CLI support. This means that any action, option, or filter setup that can be performed via the GUI, can be done via the command-line interface (CLI) and appropriate switches. The complete descriptive list of switches is available in the Advanced features section of the ApexSQL Log online documentation

There’s a little known (but very handy) option provided in the ApexSQL Log GUI that shows what exactly the CLI command should look like after all options and filters have been set. There are several use case scenarios for this option:

  • The basic one is that the user can set all ApexSQL Log parameters and simply create the batch file that can be executed via the CLI afterwards
  • If one wants to learn ApexSQL Log switches and their usage, this preview option provides a perfect way to ensure that the CLI command performs exactly what was set via the GUI – something like WYSIWYG, where the “editor” is the ApexSQL Log GUI itself, and the result – the CLI command with all required switches included
  • If the CLI command execution is required, and the ApexSQL Log project is too complex to be created manually, use this feature as it will automatically create the CLI command free of any syntax errors

To access the CLI command preview:

  1. Start a new ApexSQL Log project, or open an existing one
  2. Review and set all the required options, data sources, and filters for the project
  3. Open the Project dialog, then the Open button’s drop-down menu

  4. Select the Create batch file menu option

    ApexSQL Log's project dialog - Create batch file option

  5. The Create batch file dialog will open. In the lower part of the dialog, the Batch script section will provide the script representing the CLI command that can be executed, producing the same result as if the GUI was used

    Create batch file dialog - the Batch script section

Here is an example of the CLI command that will instruct ApexSQL Log to audit DML operations committed over a specified database in the last 60 minutes (additional transaction log backup will be used to create a full chain of transaction logs):

  1. Connect to the database (i.e. AdventureWorks2012) to be audited

  2. Add the transaction log backup using the Transaction logs tab (i.e. AW2012Backups.bak)

    Adding the transaction log backup using the Transaction logs tab

  3. Set the Time range filter using the Filters tab to the Last 60 minutes option

    Set the Time range filter to the Last 60 minutes option

  4. Set the Operations filter by unchecking all DDL operations

    Setting the Operations filter by unchecking all DDL operations

  5. Select the Create batch file menu option from the Open drop-down menu

  6. The resulting CLI command provided by the Create batch file dialog will be:

    “C:\Program Files\ApexSQL\ApexSQLLog2011\ApexSQLLog.com” /verbose
    /server:LENOVO /database:AdventureWorks2008R2 /trusted /ec:begintime endtime lsn operation schema object transactionid user /xml:D:\DOCUMENTs\ApexSQL\ApexSQLLog2011\export.xml /online:AdventureWorks2008R2_Log /backup:C:\Backups\AW2012Backups.bak /ssa:on /operations:DMLALL /time_period:60mins /force”

As shown above, not only does ApexSQL Log provide the CLI support, but it also offers an efficient and user-friendly CLI command generation and preview via the GUI. As always, it’s up to you to choose how and when to use it

Related posts:

  1. Knowledgebase articles round-up – August 1st, 2014
  2. SQL Batch formatting feature: Sneak peek at the next release of ApexSQL Refactor
  3. ApexSQL Diff 2010 Pre-Release Preview
  4. ApexSQL vs Dell® – Log reading and Data recovery comparative feature summary
  5. ApexSQL Log 2013 sneak peek – Part 1
August 13, 2013