agent restored a SQL Server backup without log files and lost the last six hours of transactions in the test

TL;DR

A database restore to a point in time needs the full backup and every log backup after it, restored in order with no recovery until the final one: the agent lost six hours because it restored the full alone and brought the database online. Build the restore sequence from the backup chain metadata, restore the full and each log in sequence leaving the database in restoring state, and only recover at the target point, verifying the database's last restored time before opening it to users.

agent restored a SQL Server backup without log files and lost the last six hours of transactions in the test

Use this when

• The job or restore fails with this symptom: agent restored a SQL Server backup without log files and lost the last six hours of transactions in the test.
• You are working on an agent performing SQL Server test restores from full and log backups and the failure repeats on the same target or schedule.
• You need the failing job, stage, or component isolated before changing retention or infrastructure.

Not for this skill when

• A restore intended to be the full backup only, where the fix is documenting that intent rather than treating it as an error.
• The error text names a different product, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Query the backup history for the database and list the full backup and the log chain after it with their finish times. Verify: the complete chain is enumerated
2. Restore the full backup with no recovery so the database stays in a restoring state. Verify: the base is restored without opening
3. Apply each log backup in order, still without recovery, up to the target time. Verify: the chain advances to the target point
4. Recover the database and immediately verify its data currency against the target time before declaring the test passed. Verify: the restored database provably contains data to the target

Variant phrasings

agent restored sql backup without logs lost transactions test

The verbatim phrasing.

sql server restore chain full plus log norecovery agent fix

The chain phrasing.

agent database restore missing log backups point in time

The point-in-time phrasing.

Compatibility: Agent restore procedures for SQL Server full plus log backup chains

Why it happens

Log backups are not decoration on a full backup; they are the transactions since it. A restore that skips them is not an incomplete version of the intended restore, it is a restore to an earlier point in time, and the loss is silent because the database opens happily at the older state.

Edge cases / pitfalls

• One broken or missing log backup caps the chain at that point; the sequence builder must detect gaps before starting.
• Test restores must verify currency with a canary record written just before the backup, not by trusting the restore log.

Provenance

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