How to audit operations for dropped or re-created tables with Old Table ID mapping

Applies to
ApexSQL Log
ApexSQL Recover

This article describes how to retrieve schemas and objects for the records that are shown as UNKNOWN in the Main grid. This happens when the table with the specific ID doesn’t exist in the database anymore, and the transactions for that object ID exist in the transaction logs used.
This happens when:

  • the table was dropped
  • the table was re-created (new ID was assigned to it)

The Old table ID needs to be mapped to the existing table. In case you need to see the tables created in the database, do the following:
Run ApexSQL Log, in the Filter setup tab, Operations sub-tab, uncheck all DML and DDL operations and check only CREATE table.

The list of all created tables for the time range selected, or the whole transaction log, will be shown. Selecting a Create table operation from the grid will show the Table ID in the Operation details tab:

Now you can select the table you will map:

  1. Copy the ID from the Undo or Redo script tab in the Row details pane:

  2. Click Old table ID mapping on the Home tab, in the Tools group, to map the table to the old ID. Paste old ID in the Old table ID field. In case the same table was dropped and recreated several times, you can map one table to several old IDs:

  3. After clicking refresh schema all table names will appear instead of UNKNOWN:

About ApexSQL Log
ApexSQL Log is a SQL Server auditing and recovery solution. Use ApexSQL Log to recover data, undo INSERT, UPDATE and DELETE commands and audit schema changes. Read the transaction log to find out who created, changed or dropped a database object.

Last updated
June 23, 2018