Viewing the transaction history of your database

The situation

You have noticed that the total value of orders stored on the Invoices table in your database doesn't match the sum of individual items in the InvoiceItems table. You suspect that a developer has run a rogue query on the database, which changed one of the tables.

You need to find out whether this is the case and, if so, who did it, and whether any other rogue queries have been run by the same developer.

Key challenges

  • Finding the transaction that changed the table.
  • Locating transactions that have been performed by the developer.

How we can solve it

  1. Use SQL Log Rescue to display the transactions that have occurred since the last full backup.
    You can display all the transactions back to the full backup if you have contiguous transaction log backups.
  2. In the Log Entries table, filter the operations so that they are displayed only for the Invoices and InvoiceItems tables.
  3. Locate the rogue operation.
    You can view the details of each operation, and the history of the affected record, to help you to identify the problem.
  4. Note the time the operation occurred and the name of the user who performed it.
  5. Filter the Log Entries table to display operations performed by that user.
    For example, you could sort the list in time order to help you check that no other rogue queries were performed the same day. Or you could use the Search feature if you are concerned about a particular record or table.
  6. If required, use SQL Log Rescue's Undo wizard to undo the query.

To learn more, take a look at our worked example.

Benefits of our solution

  • SQL Log Rescue™ reads the live transaction logs and any transaction log backups, so that you can review operations since the last database backup.
  • A graphical user interface makes SQL Log Rescue easy to use, eliminating the need for expensive specialists or consultants.
  • You can install SQL Log Rescue after the event – it doesn't have to be running when the incident occurs.

Online help