Related SQL databases need a common logical recovery boundary when their records must agree. A deliberately coordinated marked transaction can provide such a boundary, provided its mark exists in every participating database's usable log sequence. Restoring databases to similar clock times is not a substitute for that coordination.1, 2
Marks must be designed and recorded before the incident. They cannot be added retrospectively to missing history. Recovery to a chosen mark also discards later committed work from the restored state, so the business owner must accept and reconcile that consequence.1
This article uses SQL Server 2022 with full recovery as the reference design. It is a planning guide, not an executable multi-database restore runbook. No marks were created and no recovery was tested.
Define which databases form one recovery unit
Start with the business relationship. An order database and a stock database may need to agree about the same operation, while an unrelated reporting copy may have a different recovery contract. List every database whose consistency matters and identify transactions that cross those boundaries.
Do not infer the recovery unit from which databases share an instance. One application can span several instances, and two databases on one server can be independent. The application owner and DBA should draw the actual update and integration paths.
Then identify external effects: payments, messages, files or calls to another service. A SQL transaction mark does not automatically reverse those systems. A coordinated database restore still needs a rule for effects that happened after the mark or were only partly acknowledged by an external service.
Write the acceptance conditions in business terms. For example, every retained order should have the corresponding stock allocation, and later external transactions should be reconciled rather than replayed blindly. These are proposed design checks, not claims about an existing application.
Understand what a mark records
Microsoft documents a named transaction with the WITH MARK clause. The mark name is the transaction name; the optional description is descriptive text, not the identifier used to select the mark during restore. The log records identifying information, including the name, time and log sequence number.2
This distinction matters because the companion “Use marked transactions” page contains an example whose restore label appears inconsistent with its declared transaction name. This guide follows the explicit syntax explanation in the recovery reference and does not reproduce that example as a tested recipe.1, 2
A mark must be recorded in the relevant database log as part of the coordinated work. Merely naming a transaction does not establish a recoverable common boundary in every database. Microsoft's creation guidance requires updates in the participating databases, and its recovery guidance ties usable marks to committed marked transactions.1, 2
After a marked transaction commits, msdb records an entry in logmarkhistory. Preserve that catalogue information, but remember that recovery depends on the actual retained database and log backups, not only a history row.1, 2
Coordinate across databases and instances
On one instance, the mark is placed in the logs of databases updated by the marked transaction. For work spanning instances, Microsoft explicitly warns that a transaction's mark name does not automatically propagate to another server. The remote participants require the documented coordinated marking design.1
Creating unrelated transactions with the same friendly name at approximately the same time is not equivalent to establishing the required common transaction boundary. The design must explain how participation and commit are coordinated, how failures are detected and what evidence shows every required database received the usable mark.
The source discusses distributed two-phase commit and warns that marking transactions can affect transaction processing. It also advises against concurrent distributed marking transactions because they can deadlock.1 Therefore, mark frequency and placement are application design choices, not a harmless monitoring task to schedule at arbitrary intervals.
Keep distributed-transaction infrastructure, identities and connectivity within a separately reviewed design. This article does not prescribe enabling remote transaction services or changing firewall/authentication settings. A mark strategy that depends on unsupported infrastructure is not ready merely because its SQL syntax is valid.
Make the backup plan preserve the boundary
Each database needs a valid base and log sequence reaching the chosen mark. Microsoft notes that the databases' log backups do not have to occur simultaneously, because the transaction mark provides the logical boundary.1 They do, however, have to preserve the required log records.
Keep a per-database recovery inventory:
| Evidence | Purpose |
|---|---|
| Database and instance identity | Prevent recovery of a similarly named but different participant. |
| Full/differential base and complete log sequence | Establish the route to the mark. |
| Exact mark name and occurrence | Select the intended common boundary. |
| Copy location, media completeness and key custody | Demonstrate that recovery remains possible after the planned failure. |
| Business reconciliation checks | Establish that the recovered databases agree in useful application terms. |
Retention must preserve the complete recovery set for every participant. Keeping the newest log for one database and an older unrelated sequence for another does not establish a common usable mark. The latest acceptable common point may be older than the newest individual backup.
For this reference design, use full recovery and a supported log-backup strategy. Microsoft discusses full and bulk-logged models but specifically limits marked recovery when operations are bulk-logged.2 Do not treat the broader model list as permission to stop at any mark inside any bulk-logged interval.
Choose whether the marked work is included
The recovery reference distinguishes STOPATMARK, which includes the marked transaction, from STOPBEFOREMARK, which excludes it. Both can use an AFTER time qualifier to select an occurrence when the name has been reused. Without that qualifier, the first matching mark is selected; with it, the first matching mark at or after the specified time is selected.2
The operational decision must specify both the mark occurrence and whether its work belongs in the recovered state. A name alone is ambiguous if the same name was used repeatedly. The optional description is not a substitute for the correct identifier.
Record the expected business results on both sides of the boundary. If a marked batch is itself the unwanted change, recovery before the mark has a different meaning from recovery including it. Apply the same agreed interpretation across the related databases.
No restore statement is supplied here. A production sequence also needs media selection, database state, keys, file mapping and a final recovery decision. Those details must come from a case-specific runbook rather than a shortened article example.
Rehearse consistency, not just database startup
Use an authorised isolated environment with synthetic transactions before, within and after a coordinated mark. Preserve independent expected results. The rehearsal should show which records survive when including the mark and which survive when stopping before it.
Include a deliberately incomplete recovery set as a negative test design: for example, a participant without the required log segment. The acceptance process should stop rather than declare the surviving databases collectively consistent. No such test was run for this article.
After restoring, keep application integrations disabled until reconciliation is accepted. Check cross-database keys, business totals and dependent records, not only whether all databases are online. Evaluate messages or payments outside the SQL recovery unit using their independent records.
Record the full recovery sequence and the selected occurrence of the mark. A test that happened to use a unique name does not validate a production design that reuses the same name daily without an occurrence-selection rule.
Limits and return planning
Marking creates a recovery option; it does not eliminate data loss after the selected boundary. The owner must decide whether that loss is acceptable and how later work will be re-entered or reconciled.1
Changing or removing the marking procedure affects future recovery points, not marks already present in retained logs. Preserve the prior procedure and backup coverage during a design change. If a marking transaction fails, record the failure and verify participant state rather than announcing a common point from partial evidence.
For an actual incident, preserve original databases and possible tail logs before overwriting anything, under the separate tail-log decision process. Escalate ambiguous participant, mark or log-chain evidence to a qualified recovery specialist.
Learning notes
- A common mark must exist in each participant's usable recovery sequence.
- Mark name, description and repeated occurrence are different fields.
- Coordinated database recovery still needs reconciliation with external systems and later work.
Acceptance checklist
- [ ] The intended mark name and occurrence are identified in every participating database.
- [ ] Each usable restore sequence reaches the same agreed before/at-mark boundary.
- [ ] An isolated recovery validates cross-database business relationships.
- [ ] Later work and external effects are reconciled before activation.
References
- Restore databases to marked transaction - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- Recover related databases with marked transaction - SQL Server | Microsoft Learn. Accessed 2026-11-01.