A colleague ran DELETE on the customers table without a WHERE clause. 40,000 rows gone, transaction committed. Database is in full recovery model with nightly fulls and hourly log backups. Best route back?
How to recover deleted records from a SQL Server table?
Solved SQL & Databases
MS
Michael Scofield April 30, 2019
2 replies
9,110 views
Reviewed by moderators
Full recovery with log backups means point in time restore, which is the clean fix.
1
Find the deletion time from the person or the default trace.
2
Restore last night's full plus log backups WITH STOPAT one minute before the delete, into a NEW database name. Never over production.
3
INSERT the 40,000 rows back from the restored copy into production, then drop the copy.
No usable backups in some future disaster: SysTools SQL Log Analyzer reads the LDF and reconstructs deleted rows straight from the log, worth knowing exists.
STOPAT restore to a side database and re-insert took 90 minutes. All rows back. Solved.