A script for creating a great test database to evaluate the full functionality of ApexSQL Log

Applies to
ApexSQL Log

Summary
Want to kick the tires of ApexSQL Log? This article describes the ApexSQLLogDEMO script created specifically for the purpose of a full and comprehensive evaluation that will showcase the full range of ApexSQL Log’s features

Description

When evaluating ApexSQL Log, it is recommended to use a database that will allow ApexSQL Log to be used in full, and enable the evaluator to check all main features including but not limited to: undo script creation, redo script creation, auditing results to a grid, exporting results to various formats.

For the purpose of evaluation, we’ve created a script which creates a database that will help evaluators to get by, while ensuring it is a good base to cover many real-world scenarios.

This script will make sure that all critical requirements are fulfilled:

  • The database is set to full recovery mode – this ensures that the database’s transaction log contains all the information needed by ApexSQL Log to read it in full
  • A full backup is created and transaction log is initialized
  • When the ApexSQLLogDEMO script is executed, the database will be created and include the following:
    • Basic structures like linked tables with foreign keys, indexes, and constraints (tables: dbo.authors, dbo.discounts, dbo.employee, dbo.jobs, dbo.pub_info, dbo.publishers, dbo.titleauthors, dbo.titles, dbo.roysched, dbo.sales, dbo.stores, dbo.table_0 , dbo.table_1… …dbo.table_9)
    • All SQL data types including SQL variant, geography and geometry fields (table dbo.TestAllDatatypes includes all SQL types, while many different tables include several SQL data types)
    • Sparse columns (table: dbo.TestAllTypes_Sparse)
    • Row and page compression (tables: dbo.TestAllTypes_PageCompression, dbo.TestAllTypes_RowCompression)
    • Filestream (table dbo.TestFileStream)
    • Unicode compression (table dbo. TestUnicodeCompression)
    • Clustered keys (table dbo. TestPageCompressionWithMixedClusteredKey)
    • Collation (table dbo.All_Colations)
    • Dropped and recreated tables (Table dbo.Table_9_2)
  • The script contains:
    • SELECT INTO statements (table: TestAllTypesFromSelectInto)
    • Various INSERT, UPDATE, DELETE rows which enable showcase of undo/redo scripts to simulate accidental deletes and unwanted updates (all tables)
    • DROP statements (dbo.Table_9_2)
    • Several transaction log backups are created to simulate a full transaction log backup chain

This database will enable evaluator easily simulate real world situations and showcase various ApexSQL Log features on all SQL data types including:

  • Generation of undo/redo scripts for both DML and DDL operations
  • Allow view of operation details on a transaction level
  • Exporting of audit results to various formats
  • Filtering audit results to fine tune the audit boundaries
  • Automation of exporting process via the batch file creation
  • Dropped tables recovery

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.