Recovery

dbtrail can generate SQL to reverse accidental database changes — undoing DELETEs, reverting UPDATEs, or removing unintended INSERTs.

How recovery works

dbtrail indexes the full before/after state of every row change. These before/after row images are captured by the open-source bintrail CLI as it parses MySQL binary log events. When you request recovery, dbtrail uses this data to generate the inverse SQL:

The generated SQL uses primary-key-only WHERE clauses when a schema snapshot is available, falling back to all-columns WHERE otherwise.

Foreign key awareness

Recovery is FK-aware by default. When you recover a deleted row, dbtrail automatically checks whether any parent rows referenced by foreign keys were also deleted — and includes them in the recovery SQL in the correct order.

Why this matters

A naive single-table recovery can fail silently. If you deleted a customer and their orders in the same transaction, re-inserting the orders first would violate the foreign key constraint on orders.customer_id. dbtrail prevents this by:

1. Resolving parent rows — walks up the FK chain to find deleted parents that must be restored first (enabled by default via resolve_fk)
2. Optionally including dependents — walks down the FK chain to include deleted child rows (opt-in via include_dependents)
3. Topologically sorting — uses Kahn's algorithm to order INSERT statements so parents are always restored before children
4. Handling circular and self-referencing FKs — when a cycle is detected (e.g. employees.manager_id → employees.id), the SQL is wrapped with SET FOREIGN_KEY_CHECKS=0 / SET FOREIGN_KEY_CHECKS=1

Resolution is recursive up to 5 levels deep. If your schema has deeper FK chains, the remaining rows are skipped with a warning.

Example

Given this schema:

customers (id, name)
orders (id, customer_id → customers.id, total)
order_items (id, order_id → orders.id, product, qty)

Recovering a deleted customer with include_dependents: true generates:

-- FK-aware recovery generated by bintrail SaaS
-- Recovery steps: 4

BEGIN;

-- Step 1: customers (pk={'id': 42}) — requested recovery target
INSERT INTO `mydb`.`customers` (`id`, `name`) VALUES (42, 'Acme Corp');

-- Step 2: orders (pk={'id': 100}) — dependent of mydb.customers via customer_id
INSERT INTO `mydb`.`orders` (`id`, `customer_id`, `total`) VALUES (100, 42, 250.00);

-- Step 3: orders (pk={'id': 101}) — dependent of mydb.customers via customer_id
INSERT INTO `mydb`.`orders` (`id`, `customer_id`, `total`) VALUES (101, 42, 75.00);

-- Step 4: order_items (pk={'id': 500}) — dependent of mydb.orders via order_id
INSERT INTO `mydb`.`order_items` (`id`, `order_id`, `product`, `qty`) VALUES (500, 100, 'Widget', 3);

COMMIT;

Notice that customers is inserted before orders, and orders before order_items — matching the FK dependency order.

Set resolve_fk: false to get single-table recovery without any FK resolution.

MySQL 8.x CASCADE limitation

Using recovery

Via the API

The recover endpoint returns the generated SQL, an affected row count, and a list of operations. See the Recover API reference for full request/response details.

Via Claude

Ask Claude:

"Generate recovery SQL for the deleted order 12345 in the orders table"

Claude will call the recover tool and present the generated SQL for your review.

Recovery parameters

Best practices

1. Always review the SQL before executing — recovery SQL can have unintended side effects if the data has changed since the original event
2. Use specific filters — narrow down by table, primary key, and time range to avoid generating more statements than needed
3. Test in a staging environment first when recovering large batches
4. Run inside a transaction — the generated SQL is already wrapped in BEGIN/COMMIT, so you can ROLLBACK if something looks wrong

Permissions

Recovery requires the recover:execute permission, available to owners, admins, and operators.