Point-in-Time Restore With a Log Backup Gap
A SQL Server 2019 database is protected by NDB with a time machine. The SLA takes a full snapshot every six hours and transaction-log backups every 15 minutes, with seven-day retention. At 14:22 a release script executes a DELETE against a core table without a WHERE clause. The application team notices missing rows at 14:40 and asks for a point-in-time restore to 14:20. NDB shows snapshots at 12:00 and 18:00. The transaction-log backup history shows successful backups through 13:15, a failed job from 13:15 to 13:45 because the log-backup network path was unavailable, and successful log backups from 13:45 onward. The database is 800 GB and sits on a cluster with 3 TB free. The business wants the smallest possible data loss and no overwrite of the production database.
1. Can NDB restore this database to 14:20? Explain the log-chain rule and the earliest/latest achievable target.
Read the worked answer
No. Point-in-time recovery requires an unbroken log chain from a base snapshot to the requested target. The chain here is 12:00 snapshot to logs through 13:15, then a gap until 13:45. Logs from 13:45 onward cannot be applied to the 13:15 state because the transactions between 13:15 and 13:45 are missing. NDB should not skip the gap or invent a target that the chain cannot support.
The latest achievable target before the incident is 13:15, using the 12:00 snapshot plus the logs through 13:15. That implies roughly one hour and five minutes of data loss relative to 14:20. The 18:00 snapshot is later than the deletion and includes the damage plus subsequent changes, so it is not a useful recovery point for this request. A restore to 14:20 would only be possible if the missing log range were recovered from another copy or if a usable snapshot existed within the gap; neither is present in the scenario.
The team should state the achievable RPO explicitly, restore to 13:15, and then reconcile the missing rows from application-level records if possible.
2. Describe the restore workflow and the controls that would have prevented this exposure.
Read the worked answer
Restore to a new database or clone rather than overwriting production. Select the 12:00 snapshot as the base, choose the point-in-time target of 13:15, and let NDB apply the available logs. Validate row counts, key business queries, and application connectivity on the restored copy before switching users. Keep the damaged production database intact for forensic review or manual reconciliation.
The exposure was caused by a silent gap in the log chain. Controls should include an alert on any failed or missed transaction-log backup, with escalation inside one backup interval; a secondary network path or retry policy for the log-backup target; and a dashboard that shows the last successful log backup per time machine. Test PITR regularly to a clone and verify the achievable target, not just that a snapshot exists. Retention must cover both the base snapshot and the log chain; seven days is adequate only if the full chain is continuous. Finally, set an explicit RPO and make sure the application team understands that a 15-minute log schedule does not guarantee a 15-minute RPO when a gap occurs.