Restoring a SQL Server Database to a Point in Time

2017-05-25 · mssql, backup, restore

Somebody ran an UPDATE without a WHERE clause at 14:37. The nightly full backup is from 02:00 and the log backups have been running every 15 minutes. This is the exact case point-in-time restore is for: restore the full, roll forward through the logs, stop just before the bad statement.

It isn't hard, but the order of operations and the NORECOVERY/RECOVERY options trip people up, especially with someone standing behind your chair. I restore to a copy with a different name first, pull the lost rows out, and leave production alone.

You need Full (or Bulk-Logged) recovery with an unbroken log chain, a full backup from before the target time, every log backup after it, and enough disk for a second copy of the database.

Find the backups you need

Ask msdb which backups exist for the database:

SELECT bs.type, bs.backup_start_date, bs.backup_finish_date, bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = 'Sales'
  AND bs.backup_start_date >= DATEADD(DAY, -1, GETDATE())
ORDER BY bs.backup_start_date;

You need the most recent full backup (D) before the incident, optionally the latest differential (I) after that full, and all log backups (L) from the last full/differential up to the first one that finishes after the target time.

Tail-log backup, if the source is still there

If the original database is still online and you intend to restore it in place, back up the tail of the log first so you do not lose the transactions since the last scheduled log backup:

BACKUP LOG Sales TO DISK = N'D:\Backup\Sales_tail.trn' WITH NORECOVERY, CHECKSUM;

WITH NORECOVERY puts the database into the restoring state. Skip this if you are restoring to a new database name, which is what I recommend when you are unsure.

Restore the full backup with NORECOVERY

Restoring to a new name requires MOVE so the files do not collide with the live database:

RESTORE DATABASE Sales_PIT
FROM DISK = N'D:\Backup\Sales_full.bak'
WITH NORECOVERY, CHECKSUM,
     MOVE N'Sales' TO N'D:\Data\Sales_PIT.mdf',
     MOVE N'Sales_log' TO N'L:\Log\Sales_PIT.ldf';

If you do not know the logical file names, get them from the backup itself:

RESTORE FILELISTONLY FROM DISK = N'D:\Backup\Sales_full.bak';

If there is a differential, restore it next, also with NORECOVERY.

Roll the logs forward

Restore each log backup in order with NORECOVERY. On the final one, the one that spans your target time, add STOPAT:

RESTORE LOG Sales_PIT FROM DISK = N'D:\Backup\Sales_log_1400.trn' WITH NORECOVERY;
RESTORE LOG Sales_PIT FROM DISK = N'D:\Backup\Sales_log_1415.trn' WITH NORECOVERY;
RESTORE LOG Sales_PIT FROM DISK = N'D:\Backup\Sales_log_1430.trn' WITH NORECOVERY;
RESTORE LOG Sales_PIT FROM DISK = N'D:\Backup\Sales_log_1445.trn'
    WITH STOPAT = '2017-05-25 14:36:30', RECOVERY;

You can put STOPAT on every RESTORE LOG statement; SQL Server ignores it on backups that finish before the target time, which is convenient when scripting. I always do it that way now, after once forgetting it on the last file and having to start over.

Bring it online

If you used RECOVERY on the last statement, the database is already online. If you finished with NORECOVERY and want to recover now:

RESTORE DATABASE Sales_PIT WITH RECOVERY;

Check the data, then merge back

Confirm the state and look at the rows just before the incident:

SELECT name, state_desc FROM sys.databases WHERE name = 'Sales_PIT';
SELECT TOP 10 * FROM Sales_PIT.dbo.Orders ORDER BY ModifiedDate DESC;

The most recent modification should be earlier than the bad statement. Now copy the rows you need back into production:

UPDATE p SET p.Status = c.Status
FROM Sales.dbo.Orders p
JOIN Sales_PIT.dbo.Orders c ON c.OrderID = p.OrderID
WHERE p.ModifiedDate >= '2017-05-25 14:37:00';

When you are done, drop the copy.

When it goes wrong

Msg 4305: The log in this backup set begins at LSN ..., which is too recent to apply to the database. You skipped a log backup. Go back to the msdb query and restore the missing one.
Msg 4326: The log in this backup set terminates at LSN ..., which is too early to apply to the database. You are trying to apply a log backup that was already restored, or the backups are out of order.
Msg 3117: The log or differential backup cannot be restored because no files are ready to rollforward. The previous restore was done with RECOVERY. Once recovered, no more logs can be applied; start over from the full backup.
Msg 4338: The STOPAT clause specifies a point too early to allow this backup set to be restored. Your target time is before the start of the log backup you named, meaning it belongs to an earlier log backup. This only fails when STOPAT points before the first log record of the very first log you restore, so check the target time.
Msg 3154: The backup set holds a backup of a database other than the existing database. You restored over an existing database without REPLACE. Either drop it or add WITH REPLACE (carefully).

Practise this once on a test server before you need it. Restoring to a copy is slower than restoring over production, but a wrong STOPAT then costs ten minutes instead of a day's data.