Test a restore from backup

Take a backup of order_items, simulate an accidental delete, restore the missing rows and verify the table matches the backup exactly.

1. 1ATTACH DATABASE ':memory:' AS backup; CREATE TABLE backup.order_items AS SELECT * FROM main.order_items.
2. 2Simulate the incident: DELETE FROM order_items WHERE order_id <= 30.
3. 3Log the restore: insert into audit_log ('order_items', 'restore', <number of rows to restore>, '2025-12-31') computed from backup rows missing in main.
4. 4Restore: INSERT INTO main.order_items SELECT * FROM backup.order_items WHERE id NOT IN (SELECT id FROM main.order_items).
5. 5Verify: SELECT COUNT(*) AS mismatches FROM (SELECT id, order_id, product_id, quantity, unit_price FROM main.order_items EXCEPT SELECT id, order_id, product_id, quantity, unit_price FROM backup.order_items) returns 0.

• order_items is fully restored and audit_log records how many rows were restored
• The last result set shows 0 mismatches against the backup

• Every run starts from a freshly seeded database, so your script must include its own setup statements.
• An attached ':memory:' database stands in for the backup file.
• Only the last result set of your script is compared, so finish with the verification query.