How to Do a Point-in-Time Restore in SQL Server (Step by Step)

Someone ran an UPDATE without a WHERE clause at 14:32. With the right backups, you can bring the database back to 14:31 and lose only a minute of work. That is a point-in-time restore. Here is how to do it calmly and correctly.

SQL Server point-in-time restore step by step

What you need

  • The database in the FULL (or BULK_LOGGED) recovery model.
  • A full backup taken before the mistake.
  • An unbroken chain of transaction log backups from that full backup up to and past the mistake.

In the SIMPLE recovery model there are no log backups, so you can only go back to the last full or differential backup.

Step 1: find the exact time

Pin down when the mistake happened as precisely as possible: application logs, the user's report, or job history. Restore to a moment just before it. All times in STOPAT are server local time.

Step 2: take a tail-log backup

If the database is still online, back up the part of the log not yet backed up. This captures everything up to now, including the mistake, so no later work is lost while you investigate:

BACKUP LOG [Payroll]
TO DISK = N'D:\Backup\Payroll_TAIL.trn'
WITH NORECOVERY;   -- leaves the database in RESTORING state

WITH NORECOVERY takes the database offline for users, which is often what you want during recovery. If you prefer to keep it online and restore a copy instead (recommended, see step 7), omit NORECOVERY.

Step 3: list the backups you need

SELECT
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE bs.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS type,
    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 = 'Payroll'
ORDER BY bs.backup_finish_date;

You need the last full backup before the mistake, the last differential after that full (if any), and every log backup after that differential, up to the first log backup that ends after the mistake.

Steps 4 to 6: restore in order

-- 4. Full backup, leave it ready for more restores
RESTORE DATABASE [Payroll]
FROM DISK = N'D:\Backup\Payroll_FULL.bak'
WITH NORECOVERY, REPLACE, STATS = 10;

-- 5. Latest differential (skip if you have none)
RESTORE DATABASE [Payroll]
FROM DISK = N'D:\Backup\Payroll_DIFF.bak'
WITH NORECOVERY;

-- 6. Log backups in order, all but the last WITH NORECOVERY
RESTORE LOG [Payroll]
FROM DISK = N'D:\Backup\Payroll_LOG_1415.trn'
WITH NORECOVERY;

-- The last log: stop just before the mistake and bring the database online
RESTORE LOG [Payroll]
FROM DISK = N'D:\Backup\Payroll_LOG_1430.trn'
WITH STOPAT = '2026-10-24 14:31:00', RECOVERY;
  • NORECOVERY keeps the database in a restoring state so more backups can be applied.
  • RECOVERY on the final step rolls back incomplete transactions and opens the database. Once recovered, you cannot apply more logs.
  • If you are not sure which log contains the target time, you can add STOPAT to every log restore; SQL Server only stops at the backup that contains that moment.

Step 7: restore as a copy first (recommended)

Rather than overwriting production, restore to a new database name, check the data, and copy back only what was damaged. Everything done since the mistake stays intact:

RESTORE DATABASE [Payroll_Recovery]
FROM DISK = N'D:\Backup\Payroll_FULL.bak'
WITH NORECOVERY,
     MOVE 'Payroll'     TO N'D:\Data\Payroll_Recovery.mdf',
     MOVE 'Payroll_log' TO N'D:\Log\Payroll_Recovery.ldf';
-- then the differential and logs as above, using [Payroll_Recovery]

Use RESTORE FILELISTONLY on the backup to see the logical file names for MOVE. Then compare and fix the affected rows with ordinary UPDATE or INSERT statements.

Azure SQL Database

In Azure SQL Database you do not run these commands. Use point-in-time restore in the Azure portal, CLI or PowerShell, pick the time, and Azure creates a new database as of that moment, within your backup retention period. Then copy the data you need back.

Practise before you need it

The worst time to learn this is during an incident. Rehearse a point-in-time restore on a test server, time how long it takes, and write the steps down. That is how you find out about missing log backups while it is still harmless.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more