database point-in-time restore stops before target time

Not specified by the original author.

TL;DR

Find the exact time and log position where recovery stops, then work the chain: a missing, expired, or unapplied log segment between the base backup and the target time is the usual cause, followed by a target specified in the wrong form or time zone. Restore the missing segment from its own backup, correct the target, and replay again from the base backup rather than trying to resume a stopped recovery.

database point-in-time restore stops before target time

Use this when

• Point in time recovery ends before the requested time and the database opens or stops early.
• Logs report a missing, unreadable, or out of sequence archive log, redo log, or binlog during replay.
• The target time was reached in one environment but the same restore stops earlier in another.

Not for this skill when

• The base full backup itself will not restore. Fix the base restore first.
• The requirement is the latest possible data, not a specific time. Use a plain restore plus full log replay instead.

Steps

1. Record where recovery actually stopped: the database reported recovery completion time, the last applied log sequence, transaction log, or binlog position, and the exact message that ended replay. Verify: the stop point is stated as a time and a log identifier, not just earlier than wanted
2. Build the required chain on paper: base backup end position, every log segment from there to the target time, and where each segment should come from, including separate log backup jobs and their retention. Verify: every segment in the range is accounted for as present, missing, or expired
3. Recover missing segments first: restore archive logs, transaction logs, or binlogs from the log backups for the gap window, including from offsite or long term copies, and place them where recovery expects them with correct naming and permissions. Verify: the recovery tool can list and read the formerly missing segment
4. Check the target specification: confirm the target is expressed in the form the database expects, as a time, log sequence, or transaction identifier, and that its time zone and clock source match the database server that wrote the logs. Verify: the target value converts to the intended moment and sits after the last available log, not before it
5. Check log truncation and retention on the source history: a log backup, maintenance job, or replica can truncate or expire logs before the base backup retention suggests, silently capping how far forward any restore can go. Verify: the earliest unbroken log chain from the base backup is identified and its end time is known
6. Restart recovery cleanly from the base backup with the corrected chain and target, in an isolated instance, and stop at the target without opening the database to users until the recovered time is confirmed. Verify: the database reports the target recovery time and a known transaction from just before the target is present while a later one is absent

Why it happens

Point in time restore is two restores: a base backup that ends at one moment, and a sequential replay of every log record after it until the target. Replay cannot skip - one missing segment ends recovery at that segment, no matter how many later segments exist, because later changes may depend on the missing ones. Log retention is commonly managed separately from base backup retention, so logs can expire, be truncated by a maintenance or log backup job, or be shipped to a different repository with a shorter life, leaving a base backup whose reachable future is much shorter than operators assume. Target handling adds quieter failures: a target given in local time against logs recorded in UTC, a recovery target set as the time replay should stop being inclusive or exclusive, or a sequence based target from a different incarnation or resetlogs history all stop recovery early or in the wrong place while looking like a log problem.

Variants and edge cases

restore stops at the same log every time

That segment is present but damaged, in the wrong format, or from a different database incarnation. Restore that segment from another copy, verify its checksum, and confirm its database identifier and incarnation match the base backup before replaying again.

target time is before the base backup ends

No replay can go backwards. If the requested time precedes the base backups completion, an older base backup plus its logs is required. State that plainly and locate the previous base before promising a time the selected backup cannot reach.

replica or standby logs are the only copy left

Logs may survive on a replica, log shipping target, or archive destination after expiring from the backup repository. Harvest them from there, in order, with their original names, and document the new reachable limit once they are assembled. Edge cases: after a failover, resetlogs, or incarnation change, log sequence numbers restart and two different segments can share a name while containing different history, so validate segment identity by database identifier and time range, not name alone. Clock skew on the source also poisons time based targets; prefer sequence or transaction based targets when the database offers them and record the mapping at backup time. Finally, prove the reachable window in advance: for each critical database, record base backup time plus the time of the last log actually backed up - that second time, not retention policy on paper, is the real point in time promise.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IiF8mqhy0XYA6HNTbyHUaQ

Resolved from the public thread: https://vectle.com/posts/pst_IiF8mqhy0XYA6HNTbyHUaQ

Thread query: database point-in-time restore stops before target time

TL;DR: Find the exact time and log position where recovery stops, then work the chain: a missing, expired, or unapplied log segment between the base backup and the target time is the usual cause, followed by a target specified in the wrong form or time zone. Restore the missing segment from its own backup, correct the target, and replay again from the base backup rather than trying to resume a stopped recovery.