Before restoring over a failed SQL Server database, determine whether unbacked-up log records can still be preserved. A tail-log backup may extend recovery beyond the latest existing log backup, but it depends on recovery model, database state and the condition of the log. Do not overwrite the original database or change its recovery model while that decision is unresolved.1
The purpose is to preserve an option, not to promise recovery of every recent transaction. This article is a SQL Server 2022 incident decision guide. It supplies no executable tail-log or restore command, and no damaged database or laboratory restore was tested.
Establish the required recovery boundary
Name the latest independently usable backup set and the business boundary the owner needs. Distinguish backup completion time from the transactions actually recoverable through the complete sequence. A scheduled log job that failed does not create a later recovery point simply because it was due to run.
Ask whether the required point is already contained in an earlier retained log backup. Microsoft states that a tail-log backup is not needed in every restore scenario, including when the desired point is already covered by an earlier log backup.1 Do not introduce additional state changes without a reason tied to the recovery objective.
If transactions after the latest available log backup matter, assess the tail before taking actions that could destroy it. Record when new application writes stopped, if they have stopped, and whether other components can continue sending work. The final boundary must account for those operations and their external effects.
Keep data preservation and service recovery as separate milestones. Preserving a log tail is useful evidence, but the business service is not recovered until the required backups have been applied and the resulting application state accepted.
Confirm model, state and original files
Microsoft scopes tail-log recovery to full and bulk-logged recovery models.1 A database currently using simple recovery does not gain an earlier recoverable log chain by changing its setting after the incident. Record the actual recovery history instead of treating a current option as retrospective protection.
Identify the exact SQL instance, database and original file locations. Preserve the error log, storage errors, backup history and the preceding full/differential/log media. Record whether the database is online, offline, unable to start or damaged; these states influence the documented branch.
Do not conflate a missing data file with a damaged log file. Microsoft's damaged-database guidance allows some tail capture when data files are unavailable, but requires an accessible, undamaged log and other stated conditions.1, 2 A log filename present in a directory does not demonstrate readable contents.
Where the underlying storage is failing, coordinate preservation with the storage and database specialists. Repeated unplanned retries can consume time and complicate the evidence. A source-researched article cannot determine whether a particular damaged medium should be read again.
Choose the documented branch
The tail-log reference distinguishes these situations:1
| Database condition | Documented decision and consequence |
|---|---|
| Online and about to be restored | Tail capture uses NORECOVERY so the database enters restoring state and cannot accept subsequent changes. |
| Offline and unable to start | The documented NO_TRUNCATE branch can apply; no transactions are occurring, so NORECOVERY is optional in that stated case. |
| Damaged database | Specialist assessment may use CONTINUE_AFTER_ERROR, subject to log integrity, supported state and no bulk-logged changes. |
| Desired point already in an earlier log backup | A tail backup is not required merely to reach that earlier point. |
These are option names and decision boundaries, not commands to copy into an incident. A qualified DBA must match the exact state, required endpoint and version before selecting a procedure.
NORECOVERY is especially consequential: it takes the database into restoring state. Clicking a backup option that includes it is therefore an availability change, not just creation of another file. The service owner must understand that the application will not continue writing normally afterwards.1
Microsoft advises against routine use of NO_TRUNCATE outside the damaged or offline cases, and confines CONTINUE_AFTER_ERROR here to tail capture from a damaged database.1 Neither should become a generic “make the backup work” switch.
Preserve a distinct output and the preceding chain
Prepare a destination that will not overwrite existing recovery media. Confirm capacity, naming and permissions for the SQL Server service identity, not just the interactive operator. Microsoft's guidance distinguishes database backup privileges from the physical device access needed by the service.2
Record backup-set identity, start/finish time, selected options and exact result. Keep the original diagnostic error as well as any later successful message. Do not publish connection credentials or secret key material in the incident report.
The tail is the final part of a recovery sequence, not a substitute for the full backup and required intermediate logs. Preserve every set needed to reach it and any encryption dependencies. If the preceding sequence has a gap, the mere existence of a new tail file does not establish an end-to-end restore route.
Likewise, a successful backup does not prove that a required copy has reached the recovery site or remains accessible without the failed server. Independent access and complete media custody belong in the recovery acceptance record.
Understand damaged-database limitations
For a damaged database, Microsoft lists three important conditions: the log files must be undamaged, the database must be in a state that supports tail-log backup, and it must not contain bulk-logged changes for that damaged-tail scenario.1 The article does not weaken those conditions into a blanket promise for any full or bulk-logged database.
A tail backup from an offline or damaged database may have incomplete backup metadata. Microsoft describes the has_incomplete_metadata and HasIncompleteMetadata indicators and explains that this condition can coexist with a complete, usable captured log.1
Do not interpret that fact in either extreme. Incomplete metadata does not automatically make the log unusable, but it also does not prove that an arbitrary backup completed correctly. Preserve the operation result and have the DBA assess the exact media and restore sequence.
If the tail cannot be captured, state the latest demonstrable recovery boundary and the unprotected transaction interval. Do not claim that no data is lost because a file exists, nor guess the number of lost records from elapsed minutes alone. Business reconciliation may need independent application, payment or integration records.
Plan the isolated restore and acceptance
The recovery team should prepare a separate destination with suitable SQL version, storage, access and key custody. Keep it isolated from production integrations until ownership and reconciliation are accepted. A restored application must not automatically resend messages or repeat payments.
The planned sequence should name every full, differential if used, log and tail set, with the intended stopping/recovery decision. Validate media identities and dependencies through the established SQL chain-verification process. This article deliberately does not repeat a generic restore script because the incident state and target boundary determine the correct sequence.
Acceptance should include application-level records immediately before and after the required boundary where independent evidence exists. Database availability and integrity checks are necessary parts of assessment, but the business owner must also confirm whether the recovered transactions are coherent with external systems.
Record elapsed milestones honestly: incident, write stop, preservation, authorisation, restore start, database availability and application acceptance. A quick tail backup is not a measured full-service recovery time.
Return decisions and escalation
Once the database has entered a recovery workflow, returning to normal operation is not equivalent to undoing a settings checkbox. The DBA must decide the supported recovery transition, and the application owner must control which copy becomes authoritative. Do not improvise a return by attaching files, rebuilding a log or overwriting another database.
Escalate when log integrity, database state, media identity or bulk-logged content is uncertain. Preserve the original files and the last proven recovery route while support assesses the case. A failed tail attempt changes the known options; it is not justification to apply increasingly destructive recovery commands.
Learning notes
- Tail capture belongs before actions that overwrite the original recovery evidence.
- The correct option depends on database state and can stop application access.
- Preserved log records still need a complete restore sequence and business reconciliation.
Acceptance checklist
- [ ] Database state, intended recovery point and original log evidence are recorded.
- [ ] The DBA has determined whether tail capture is necessary and which supported conditions apply.
- [ ] A complete usable backup/log sequence and its access dependencies are established.
- [ ] Isolated recovery evidence and any unrecoverable interval or external effects are explicitly recorded.
References
- Tail-log backups (SQL Server) - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- Back Up the Transaction Log When the Database Is Damaged (SQL Server) - SQL Server | Microsoft Learn. Accessed 2026-11-01.