Home / Databases / SQL Server

DatabasesRunbookIntermediate

Restoring SQL Server to a point in time after accidental data loss

Restore full, differential and log backups side by side to the moment before a bad DELETE or UPDATE, then copy the lost rows back, without overwriting the production database.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016–2022 (FULL recovery model), Azure SQL Managed Instance (native PITR differs)

The customer says: "someone deleted orders at about 14:05". Don't restore over production. Restore a copy next to it, then copy the rows back.

1. Find the backup chain

SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date,
       bs.first_lsn, bs.last_lsn, bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'Sales' AND bs.backup_start_date > DATEADD(day, -2, GETDATE())
ORDER BY bs.backup_start_date;
-- type: D = full, I = differential, L = log

You need: the last full before 14:05, the last differential after it (if any), then every log backup up to and including the one covering 14:05.

2. Take a tail-log backup (optional but wise)

If the incident is recent, back up the log now so nothing after the last scheduled log backup is lost:

BACKUP LOG Sales TO DISK = N'E:\Backup\Sales_tail.trn' WITH COPY_ONLY;

3. Restore as a new database

RESTORE FILELISTONLY FROM DISK = N'E:\Backup\Sales_full.bak';   -- logical file names

RESTORE DATABASE Sales_PITR FROM DISK = N'E:\Backup\Sales_full.bak'
WITH MOVE 'Sales' TO 'E:\Data\Sales_PITR.mdf',
     MOVE 'Sales_log' TO 'E:\Log\Sales_PITR.ldf',
     NORECOVERY, STATS = 10;

RESTORE DATABASE Sales_PITR FROM DISK = N'E:\Backup\Sales_diff.bak' WITH NORECOVERY;

RESTORE LOG Sales_PITR FROM DISK = N'E:\Backup\Sales_log_1400.trn' WITH NORECOVERY;
RESTORE LOG Sales_PITR FROM DISK = N'E:\Backup\Sales_log_1415.trn'
WITH STOPAT = '2026-10-22T14:04:00', RECOVERY;

4. Don't know the exact time?

Restore with STANDBY instead of RECOVERY. The database is readable between log restores, so you can step forward and check:

RESTORE LOG Sales_PITR FROM DISK = N'E:\Backup\Sales_log_1415.trn'
WITH STOPAT = '2026-10-22T14:03:00', STANDBY = N'E:\Backup\Sales_undo.dat';
SELECT COUNT(*) FROM Sales_PITR.dbo.Orders WHERE OrderDate >= '2026-10-01';

5. Copy the lost rows back

SET IDENTITY_INSERT Sales.dbo.Orders ON;
INSERT INTO Sales.dbo.Orders (OrderID, CustomerID, OrderDate, Amount)
SELECT p.OrderID, p.CustomerID, p.OrderDate, p.Amount
FROM Sales_PITR.dbo.Orders p
WHERE NOT EXISTS (SELECT 1 FROM Sales.dbo.Orders o WHERE o.OrderID = p.OrderID);
SET IDENTITY_INSERT Sales.dbo.Orders OFF;

For an UPDATE gone wrong, join on the key and update the changed columns from the copy. Check foreign keys and triggers first.

6. Clean up

Drop Sales_PITR once the customer confirms the data. Record the root cause (who ran what) and the recovery time in the incident.

Tip

Test this procedure quarterly with a real backup. A restore you've never tested is a hope, not a recovery plan.

Related fixes