Recover Deleted Records or a Dropped Table in SQL Server

A DELETE without a WHERE clause, a TRUNCATE TABLE run on the live database instead of the test copy, or a DROP TABLE on the wrong table: in SQL Server each of these finishes in a moment, and there is no recycle bin to undo it. The data is still not gone. SQL Server does not overwrite the rows when they are deleted or when a table is dropped; it marks the space as free. Until new data is written there, the rows are still in the MDF file. This guide explains what to do first, when a backup can bring the data back, and how the rows are recovered from the database pages when it cannot.

Every insert or update in the same database can reuse the pages that still hold the lost rows. Stop the application, the jobs and the integrations that write to the database. Do not run DBCC SHRINKFILE or SHRINKDATABASE, which moves pages around and overwrites free space, and do not rebuild indexes. Do not switch the database to the SIMPLE recovery model to 'clean up' the log, because that throws away the log records that may still describe the deleted rows.

If the database uses the FULL recovery model, take a transaction log backup straight away: it keeps the log records of the delete safe for a point-in-time restore. Then copy the MDF, NDF and LDF files themselves, by taking the database offline for a moment or stopping the SQL Server service. A new full backup is not enough on its own. A backup contains only the pages that are in use, so the pages that held a dropped or truncated table, which SQL Server has already released, are left out of it.

If the database is in FULL recovery and there is a full backup from before the mistake plus every log backup since, the clean answer is a point-in-time restore. Restore the full backup under a new database name WITH NORECOVERY, then the differential if there is one, then the log backups in order, the last one WITH STOPAT set to a time just before the DELETE, TRUNCATE or DROP, and WITH RECOVERY. Never restore over the live database. Compare the restored table with production, then copy the missing rows or the whole table back with INSERT ... SELECT.

If you do not know the exact time, the log can be searched for the moment of the delete, and a log backup can be restored WITH STOPBEFOREMARK when a transaction mark was used. With only a full backup, or in the SIMPLE recovery model, you can get back to the time of the last backup, and the rows added after it have to come from somewhere else.

One case is easy: TRUNCATE TABLE inside an explicit transaction that has not been committed can still be rolled back. Once committed, it cannot.

A DELETE marks each row as a ghost record. Within seconds a background task removes the ghost from the page's slot array and adds its space to the page's free space, but the bytes of the row are usually still on the page until a new row is written over them. On a table that receives few inserts, deleted rows can be read back weeks later.

TRUNCATE TABLE does not touch the rows at all. It releases the table's pages and extents in the allocation maps, so the table appears empty, while the pages keep their content until they are given to another table and written. DROP TABLE does the same and also deletes the table's rows in the system catalog, which is where SQL Server keeps the table name, its columns and their types. Those catalog rows are themselves deleted rows on system pages, and they can be recovered in the same way as user data.

When there is no usable backup, the copied MDF and NDF files are read directly, without attaching them to SQL Server. Every 8 KB page carries a header with the object it belonged to. The recovery reads the system catalog, including the deleted catalog rows of a dropped table, to learn the table's columns, collects every page that still carries that table's object number, and decodes the rows on it, including the space where deleted rows were. Rows that were partly overwritten are reported as such, not guessed.

We test this on databases whose contents we know exactly before relying on it in a real case. On our SQL Server test databases, 300 of 300 deleted rows and 1,500 of 1,500 rows of a dropped table came back correct, with the dropped table's name and columns rebuilt from the deleted catalog rows. On a real database the result depends above all on how much has been written since the mistake, which is why the copy in the first step matters more than anything that follows.

The recovered rows are exported to a new database or to files, so that they can be compared with production before anything is merged back.

AS SQL Recovery & Repair Studio opens MDF, NDF and BAK files directly, without SQL Server, and recovers deleted records and dropped tables from pages SQL Server has not reused. The trial analyses the database files and shows the tables it finds before you buy a licence to export them.

For ERP and accounting databases and other business-critical systems, our SQL Server database recovery service does the work remotely or in our laboratory. We have solved many SQL Server cases; contact us for a free assessment, and if no data can be recovered, there is no charge. Afterwards, the lasting fix is simple: use the FULL recovery model with regular log backups for databases that matter, keep a copy of the backups off the server, and test a restore on a schedule.