Point-in-Time Recovery: Choose and Prove the Restore Target

A point-in-time restore is a claim about which transactions belong in a recovered database. "Just before the deletion" is not yet an executable instruction. The operator needs a trustworthy timestamp, a continuous backup chain, and a business check that distinguishes the intended state from a merely online database. This guide assumes the full recovery model and already established log backups.

Define the target before choosing files

Suppose a deployment deletes the wrong invoices at 14:30. An application log may record the request arrival, while the database transaction commits later. A timestamp from a customer browser may use a different time zone. Correlate database evidence, application request IDs, and audited business changes before choosing the boundary. Preserve the original evidence instead of overwriting the damaged database immediately.

Prefer restoring alongside production under a different database name and separate physical file paths. That preserves evidence and lets the business inspect the proposed result. It also needs enough storage and a way to prevent scheduled jobs or external integrations from treating the restored copy as production. Restoring data does not reverse an email, payment, or message already sent outside SQL Server.

Document the clock convention used by the recovery procedure. The example timestamp below is a placeholder on the verified server timeline, not a declaration that the time is UTC. Ambiguous daylight saving times deserve an explicit rehearsal. Choosing a whole second before an incident can also discard legitimate commits in that second; business reconciliation must account for them.

Assemble and apply the chain

Start with a full backup whose recovered endpoint precedes the target. Add a compatible differential if it reduces restore time without advancing beyond the target, then every required log backup through the one containing that time. The target follows committed transactions, not the moment a statement began. A long transaction started earlier but committed after the target should not appear as committed work.

Inspect actual backup headers and file lists. Do not infer chain continuity from filenames or backup finish times alone. Identify backup set positions, all striped media, encryption keys, and logical file names for MOVE. A full backup after the unwanted change cannot be made earlier simply by attaching STOPAT to a later log restore.

The following is a log-restore template for an isolated database that has already received the correct full and optional differential with NORECOVERY. Replace every path and repeat it for each required log in sequence. Use the same target for the entire sequence.

RESTORE LOG [RecoveryPractice]
FROM DISK=N'D:\Restore\required_log_001.trn'
WITH NORECOVERY, STOPAT='2025-05-12T14:29:59';
-- Repeat for every required log, with the same STOPAT.
-- Only after confirming that the target was reached:
-- RESTORE DATABASE [RecoveryPractice] WITH RECOVERY;

Keeping the database unrecovered leaves room for additional required logs. Confirm that the selected chain actually reaches the requested target before the separate final recovery step. If the target is later than the last available log, an offline restore is not evidence of data loss or success: investigate which backup is missing. In bulk-logged recovery, a log backup containing minimally logged bulk work restricts point-in-time choices within that backup.

Prove the business state before release

If the original database remains accessible, assess whether a tail-log backup can preserve transactions not yet backed up before any destructive restore. The right procedure depends on database condition and the chosen target; do not improvise an overwrite with WITH REPLACE during an incident.

After recovery, check integrity and then test business invariants: the affected invoices exist, the bad operation is absent, known earlier commits are present, and related totals reconcile. Record deliberately excluded later transactions for replay or manual resolution. An online database alone proves none of those conditions.

Measure discovery, file retrieval, restore, verification, and application reconnection separately. A fast restore with missing credentials can still miss the recovery-time objective. Save the exact media sequence and verification queries from the rehearsal so another operator can repeat the decision without relying on memory.

Technical references: Microsoft Learn: Point-in-time restore · Microsoft Learn: Tail-log backups.