Dropped or Truncated a Table? What to Do First

An accidental DROP TABLE, TRUNCATE or unfiltered DELETE on a production database is one of the most common ways to lose data, and it is usually noticed within minutes. What happens next matters more than what was lost. The data is often still recoverable, but a busy database keeps writing, and every new write can overwrite the space and the log records that a recovery depends on.

If the application can be paused, or the database put into a restricted or read-only mode, do that first. Avoid maintenance jobs in the meantime: index rebuilds, VACUUM FULL, file shrinking and log truncation all reduce what can be recovered. Repeatedly restarting the database is also best avoided, because crash recovery and checkpoints rewrite files.

DELETE, TRUNCATE and DROP behave differently. A DELETE is normally logged row by row, so it is often the easiest to reverse. TRUNCATE and DROP release the table's storage in one step and log much less, which changes what can be recovered and how. Whether these statements can be rolled back also depends on the database engine: in some, they can be rolled back if the transaction is still open, and in others they take effect immediately. If the session that ran the statement is still open and uncommitted, check the engine's behaviour before closing it.

If there is a full backup and the transaction logs since then, whether SQL Server log backups, MySQL binary logs, archived PostgreSQL WAL or Oracle archived redo logs, the database can be restored to a moment just before the mistake. Do that on a separate server or instance, so the production database and the evidence in it are not overwritten, and then copy the recovered table back.

Some platforms keep a way back. Oracle's recycle bin can hold a dropped table so that it can be restored with FLASHBACK TABLE, unless it was dropped with PURGE or the space was reclaimed. Storage, virtual machine or database snapshots may hold a copy from before the mistake, if any existed. It is worth asking whether one of these was in place before assuming the only option is a backup.

Deleted rows and released pages often remain in the data files until something overwrites them, which means file-level recovery is sometimes possible even without a backup. How much can be recovered depends on the engine, on the statement, and above all on how much writing has happened since. Take an image or a copy of the data files and logs straight away, and work from the copy. Nothing about this is guaranteed.

Limit who can run DROP and TRUNCATE on production, test that backups actually restore, and keep transaction log or binary log archiving switched on so that point-in-time recovery is possible. A restore that has never been tested is a hope, not a plan.