Find the moment before the delete
litebackup versions app/main.sqlite lists the versions your server keeps, with their times. Restore with a --time right before the delete, or a --version from that list.
Without continuous backup, use your newest copy from before the delete, from .backup, VACUUM INTO, or a snapshot. Writes between that copy and the delete are not in it.
Copy only the rows you lost
Narrow the WHERE to the rows that were deleted, for example:
WHERE id NOT IN (SELECT id FROM main.orders)
AND customer_id = 42;
When other tables point at these rows, copy the rows they point at first, in the order your app inserts them.
Recover a dropped table
CREATE TABLE AS SELECT loses the table's constraints and indexes. Recreate the table from its original schema first, then copy the rows:
sudo -u app sh -c 'sqlite3 /tmp/before.sqlite ".schema orders" | sqlite3 /var/lib/app/main.sqlite'
sudo -u app sqlite3 /var/lib/app/main.sqlite \
"ATTACH '/tmp/before.sqlite' AS before; INSERT INTO main.orders SELECT * FROM before.orders;"
Replace the whole database
If too much changed to copy rows by hand, replace the file with the restored one. Restore SQLite to a point in time has the steps.
Next time
- Litebackup keeps every change as a version within seconds, so the moment before a mistake is always there. Get started.
- How to back up SQLite compares the ways to back up.
Get started
Install your Litebackup server and back up a first database in minutes: get started, or see pricing.