还原到某个时间点（时间点还原，STOPAT）

命令

-- 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 = '...';

作用

以 WITH NORECOVERY 应用 FULL +（可选）DIFF + 日志备份，并在指定时间停止日志重放；将数据库恢复到错误操作（错误的 DELETE/UPDATE、DROP）之前的时刻。

适用场景

用于恢复被误删/误改的数据、检查某一时刻的数据或审计。

注意

以另一个名称还原，而不是覆盖原库，然后把需要的行复制回去；若必须覆盖还原，先执行结尾日志备份。STOPAT 为服务器本地时间。恢复模式必须是 FULL；若包含 STOPAT 时间的日志备份中含有 BULK_LOGGED 下的最小日志记录操作，则无法在该备份内部停止。RESTORE ... WITH RECOVERY 之后不能再应用日志。