Point-in-Time Restore: Getting Back Rows Deleted by Accident

An accidental DELETE can remove the right rows at exactly the wrong time. A point-in-time restore recovers a separate database to just before the mistake so you can inspect and return the missing data.

Establish What Happened Before Acting

Record the database, affected table, approximate incident time, and relevant application activity. Determine whether the DELETE is still inside an uncommitted transaction. A controlled rollback of that transaction is simpler than restoring backups. Once the change is committed, a new ROLLBACK statement does not reach backward into completed work.

I preserve the evidence before running correction queries. Keep existing backup files, record their positions and timestamps, and stop any retention task that would remove required recovery material through the approved incident process. Do not guess at missing keys and immediately insert them. First determine what the available recovery chain can reconstruct.

The examples use StoreDb as the affected database and StoreBeforeDelete as the recovery copy. They illustrate the sequence rather than an incident that happened at these dates. Replace every path, logical name, backup-set position, and target time with verified values. A timestamp copied from an article cannot identify your accident.

Check the Chain a Point-in-Time Restore Needs

Point-in-time recovery requires suitable full recovery and transaction log backup history. Changing SIMPLE to FULL after the deletion does not create missing earlier log backups. A full or appropriate data backup establishes the recovery starting point, and subsequent log coverage must reach the chosen target without a gap.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = N'StoreDb';
SELECT backup_start_date, backup_finish_date, type,
       first_lsn, last_lsn, database_backup_lsn
FROM msdb.dbo.backupset
WHERE database_name = N'StoreDb'
ORDER BY backup_start_date;

Use history as an inventory aid, then verify the physical files and restore compatibility. History can reference a file that no longer exists. A differential must belong to the selected full backup's differential base. Log order is determined by the chain, not a convenient alphabetical listing of filenames.

Bulk-logged operations introduce restrictions on stopping within affected log backups. Investigate that history before promising an exact target. If the chain cannot represent the desired time, explain the available recovery boundaries and the remaining data gap. A recovery plan needs an honest limit before it needs a confident progress message.

Pick the Point-in-Time Restore Target

Correlate application logs, monitoring evidence, and the transaction's known timing. Choose a target that excludes the destructive transaction while preserving as much earlier valid work as possible. Consider the server's time convention and any conversions in application logs. A one-second adjustment without evidence is only a guess.

Use point-in-time restore rehearsals to refine the boundary. Recover one copy, inspect the affected population, and repeat from backups with a different target if necessary. A target before the incident can also exclude unrelated valid changes. Those differences matter when deciding which recovered rows should return to the current database.

Record the accepted target alongside the evidence supporting it. If a long transaction began before the target but committed afterward, its committed effects do not belong in that earlier recovered state. Validate actual recovered business data rather than assuming a wall-clock label proves every desired row is present.

Capture the Remaining Log Coverage

A recent accident can be newer than the last scheduled log backup. Preserve the remaining log through an approved backup operation if the live database is accessible. When restoring a separate copy while live service continues, an ordinary log backup can capture that coverage without placing the live database into restoring state.

BACKUP LOG StoreDb
TO DISK = N'C:\SqlBackups\StoreDb_incident_tail.trn'
WITH CHECKSUM;

This captures the current end of the log for the copy's recovery chain. An in-place recovery that intentionally takes the original database out of service uses a different tail-log plan, commonly WITH NORECOVERY. Do not add that option to a keep-running workflow. If the original is damaged, its accessibility determines which tail-log options are possible.

Coordinate this backup with the scheduled chain and retain it in the incident inventory. Another log backup does not replace an earlier missing file. Verify permissions, capacity, and completion before depending on the newly created backup. Preserve evidence of a failure instead of quietly skipping the uncovered interval.

Restore the Full and Differential Backups

Inspect FILELISTONLY and HEADERONLY first. MOVE directs every database file to a new physical path, and the different database name keeps the operation separate from the live database. This example assumes the inspected backup has the two logical files shown and that the chosen differential matches its base.

RESTORE FILELISTONLY
FROM DISK = N'C:\SqlBackups\StoreDb_full.bak';
RESTORE DATABASE StoreBeforeDelete
FROM DISK = N'C:\SqlBackups\StoreDb_full.bak'
WITH FILE = 1,
     MOVE N'StoreDb_Data' TO N'C:\SqlData\StoreBeforeDelete.mdf',
     MOVE N'StoreDb_Log' TO N'C:\SqlData\StoreBeforeDelete_log.ldf',
     NORECOVERY, CHECKSUM;
RESTORE DATABASE StoreBeforeDelete
FROM DISK = N'C:\SqlBackups\StoreDb_diff.bak'
WITH FILE = 1, NORECOVERY, CHECKSUM;

Omit the differential restore when no suitable differential is selected. Do not use WITH REPLACE, and do not reuse the live file paths. Check storage capacity before starting; recovering a copy requires real space even when the final correction concerns a small table. Backups are compact plans until the files need somewhere to live.

Finish the Point-in-Time Restore With STOPAT

Restore the required logs sequentially, keeping NORECOVERY until the final accepted step. Use the same STOPAT target through the log sequence. The last selected log must include the target time, and RECOVERY makes the copy available for inspection after that target is reached.

RESTORE LOG StoreBeforeDelete
FROM DISK = N'C:\SqlBackups\StoreDb_log_01.trn'
WITH FILE = 1, NORECOVERY,
     STOPAT = '2026-09-20T10:14:59';
RESTORE LOG StoreBeforeDelete
FROM DISK = N'C:\SqlBackups\StoreDb_incident_tail.trn'
WITH FILE = 1, RECOVERY,
     STOPAT = '2026-09-20T10:14:59';

These two files are illustrative, not a statement that every incident needs exactly two logs. Include every intervening required backup. Inspect restore messages and the recovered data. If the target is beyond the available coverage, obtain the needed log rather than declaring the pre-incident state recovered.

Return Only the Accepted Missing Rows

I compare the recovered table with current data before designing the write. Start with an explicit key comparison, then review identities, foreign keys, triggers, and later legitimate deletions. Missing rows from an older copy are candidates, not automatic instructions to reinsert everything.

SELECT r.OrderID, r.CustomerID, r.Amount
FROM StoreBeforeDelete.dbo.Orders AS r
WHERE NOT EXISTS
(
    SELECT 1 FROM StoreDb.dbo.Orders AS l
    WHERE l.OrderID = r.OrderID
);

Use an approved staging list and an explicit INSERT inside a tested transaction for the accepted population. Preserve identity values with IDENTITY_INSERT when required, and verify related data. Which recovered rows belong to the accident rather than another valid operation? Answer that question before committing the correction.

Retain the recovery copy until the returned rows and preserved newer work are verified. Point-in-time restore provides the earlier state; the final correction still needs a controlled decision about what moves from that state into today's database.

Related reading on this blog: Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore and SQL SERVER 2022: Last Valid Restore Time: Improved Backup Metadata.

A recovery timestamp is not a completed correction, it is the boundary for a separately verified database state.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.