A recovery can be aimed at a wall-clock timestamp, at a transaction-log position, or at a named marker created earlier. How do you choose between them, and why is a timestamp target less precise than it appears?

## The available target kinds Engines differ in naming, but the family of recovery targets is consistent: 1. **End of available log.** Replay everything you have. This is the disaster-recovery case: media loss, not logical damage. It maximises recovered data and is not useful when the problem is a bad statement. 2. **Timestamp.** Stop at the last transaction that committed at or before a given time. 3. **Log position.** Stop at a specific location in the transaction-log stream. 4. **Transaction identifier.** Stop relative to a specific transaction's commit. 5. **Named restore point / marker.** Stop at a label that someone recorded in the log stream in advance. ## Why timestamps mislead A timestamp target is compared against the **commit timestamp** written into the log by the server at commit time. That creates several sources of imprecision: - **Clock source.** It is the database server's clock, not the application's or the incident channel's. Skew of even a second, an NTP step, or a mismatch between local time and UTC in how you specify the target can move the boundary by more transactions than you expect. - **Resolution versus density.** A busy OLTP system may commit thousands of transactions per second. Whatever the stored precision, the practical effect is that specifying a time picks a boundary among a crowd of transactions you cannot distinguish, so unrelated work gets included or excluded arbitrarily. - **Commit time is not statement time.** A long transaction that started at 14:10 and committed at 14:33 is included by a 14:33 target and excluded by a 14:32 one, even though most of its work happened long before. Reasoning about 'when the damage started' by statement time therefore does not translate cleanly. - **Ordering.** Recovery stops on **commit order**, which is the log order. Commit timestamps are broadly consistent with that order but should not be treated as a fine-grained sort key. Because of this, the practical method is: use the approximate time to locate the region of the log, inspect the log to identify the offending transaction, then convert to a log-position or transaction-identifier target. ## Why named markers are the best answer Everything above describes forensic work performed under pressure. A restore point created deliberately before a schema migration, a bulk data fix or a risky release removes all of it: the target is a label with unambiguous placement in the log stream. It costs one statement, and it is the single highest-value habit around planned risky changes. The obvious limitation is that it only helps for changes you knew were risky; accidents still require forensics. ## Two settings that decide correctness - **Inclusive versus exclusive.** Most engines let you say whether the transaction matching the target is itself applied or not. When recovering from a damaging transaction you want it **excluded** - stop *before* it. Getting this backwards replays exactly the statement you were trying to escape, and the mistake is easy to make because the default is often inclusive. - **Action on reaching the target.** Typical choices are pause, promote or shut down. **Pause** is the right default during an incident: recovery halts, the database can be inspected read-only, and if you overshot or undershot you can adjust and re-run without having produced a new branch of history. Promoting immediately makes the outcome final; it also forks the history, after which changing your mind can mean restoring from scratch. ## Additional constraints - Recovery always stops on a **transaction boundary**. Uncommitted work at the target is rolled back; you cannot land mid-transaction. - The target must fall inside the recoverable window: at or after the consistency point of the base backup being used, and at or before the end of the contiguous archived log chain. A target earlier than the backup means you need an older base backup. - Some effects are outside the logged stream - engine-excluded operations, files stored outside the database, external systems already notified. Recovery moves the database back; it does not move the outside world back, so reconciliation with queues, caches and downstream consumers is part of the plan. ## Choosing, in one line each Planned risky change: create a marker first and use it. Accidental damage you can pinpoint in the log: use the log position, excluded. Damage you can only bound in time: use a timestamp to find the region, then convert. Media loss with no logical damage: replay to the end of the log.