DBAtlas · Database operations atlas

Restore to a point in time (point-in-time restore, STOPAT)

SQL ServerRestore & Recovery

Command

-- Restore under a side name, keep the original intact:
RESTORE DATABASE [Musteri_PITR]
FROM DISK = N'D:\Backup\Musteri_full_20261008.bak'
WITH MOVE N'Musteri'     TO N'D:\Data\Musteri_PITR.mdf',
     MOVE N'Musteri_log' TO N'D:\Log\Musteri_PITR_log.ldf',
     NORECOVERY, STATS = 10;

RESTORE DATABASE [Musteri_PITR]
FROM DISK = N'D:\Backup\Musteri_diff_20261008.bak' WITH NORECOVERY;

-- All log backups in order, each with the same STOPAT:
RESTORE LOG [Musteri_PITR] FROM DISK = N'D:\Backup\Musteri_log_1200.trn'
WITH NORECOVERY, STOPAT = '2026-10-08T14:29:00';
RESTORE LOG [Musteri_PITR] FROM DISK = N'D:\Backup\Musteri_log_1500.trn'
WITH NORECOVERY, STOPAT = '2026-10-08T14:29:00';

RESTORE DATABASE [Musteri_PITR] WITH RECOVERY;

-- Alternative: step forward with STANDBY and read data at each point:
-- RESTORE LOG [Musteri_PITR] FROM DISK = N'...' WITH STANDBY = N'D:\Backup\Musteri_PITR_undo.dat', STOPAT = '...';

What it does

Applies FULL + (optional) DIFF + log backups WITH NORECOVERY and stops log replay at the given time; returns the database to just before the bad operation (wrong DELETE/UPDATE, DROP).

When to use it

To recover accidentally deleted/updated data, to inspect the data at a specific moment, or for an audit.

Caution

Restore under a side name rather than over the original, then copy the needed rows back; if you must restore over it, take a tail-log backup first. STOPAT is in server local time. The recovery model must be FULL; if the log backup containing the STOPAT time includes minimally logged operations under BULK_LOGGED, you cannot stop inside that backup. After RESTORE ... WITH RECOVERY no more logs can be applied.

Open in DBAtlas →

Related