usp_RestorePointInTime.md

Home | Tables | Procedures

cfg.usp_RestorePointInTime

Restore Layer - Execution

Overview

cfg.usp_RestorePointInTime is the core restore execution engine of the framework.

It is responsible for executing deterministic restore workflows using FULL, DIFF, and LOG backups, supporting both STOPAT and STOPBEFOREMARK recovery scenarios.

This procedure consumes the restore chain generated by [cfg].[usp_GetLatestBackupFiles], transforms it into an executable restore plan, applies each restore step in sequence, persists execution telemetry, and optionally validates database integrity through DBCC CHECKDB.

Responsibilities

• Execute deterministic restore workflows using FULL / DIFF / LOG backup chains
• Support both STOPAT and STOPBEFOREMARK recovery modes
• Resolve target data and log file destinations for restore testing
• Build step-by-step restore commands from the restore plan
• Execute restore steps in sequence with execution tracking
• Persist restore header and step-level telemetry
• Optionally validate the restored database with DBCC CHECKDB
• Return a structured execution contract for operational review

Parameters

Parameter	Type	Description
@SourceDB	SYSNAME	Source database used to resolve the restore chain.
@TargetDB	SYSNAME	Target database name to be created during restore execution.
@StopAtDate	DATETIME2(3)	Target point in time used for STOPAT recovery scenarios.
@StopBeforeMark	NVARCHAR(128)	Transaction mark used for STOPBEFOREMARK recovery scenarios.
@DoCheckDB	BIT	Indicates whether DBCC CHECKDB should be executed after recovery.
@ReplaceTarget	BIT	Indicates whether the target database should be replaced if it already exists.
@Debug	BIT	Enables debug output and additional execution visibility.
@RunID	BIGINT OUTPUT	Returns the restore execution identifier stored in [log].[RestoreTestRun].

Execution Flow

The procedure follows a deterministic restore execution pattern:

1. Normalize and validate input parameters
2. Resolve source logical file names and target restore paths
3. Determine recovery mode (STOPAT or STOPBEFOREMARK)
4. Retrieve the restore chain from [cfg].[usp_GetLatestBackupFiles]
5. Build executable restore commands for each step
6. Prepare the target environment
7. Persist restore header telemetry into [log].[RestoreTestRun]
8. Execute each restore step while capturing execution detail
9. Persist step-level telemetry into [log].[RestoreStepExecution]
10. Optionally run DBCC CHECKDB
11. Return execution history and restore header contract

Example Usage

DECLARE @RunID BIGINT;

EXEC cfg.usp_RestorePointInTime
    @SourceDB = 'AdventureWorks2022',
    @TargetDB = 'AdventureWorks2022_RestoreTest',
    @StopAtDate = '2026-03-04 10:00:00.000',
    @DoCheckDB = 1,
    @ReplaceTarget = 1,
    @Debug = 1,
    @RunID = @RunID OUTPUT;

Marked-Based Example

DECLARE @RunID BIGINT;

EXEC cfg.usp_RestorePointInTime
    @SourceDB = 'AdventureWorks2022',
    @TargetDB = 'AdventureWorks2022_RestoreTest',
    @StopBeforeMark = 'RT_158202348',
    @DoCheckDB = 1,
    @ReplaceTarget = 1,
    @Debug = 1,
    @RunID = @RunID OUTPUT;

Outputs

Each execution generates outputs at three levels:

1. Persisted telemetry

• Header-level in [log].[RestoreTestRun]
• Step-level in [log].[RestoreStepExecution]

2. Structured result sets

The procedure returns two main contracts:

1 - Steps	2 - Results
Sequence order	Execution summary
Commands executed	Restore status
Execution & Error timestamps	Validation flags
Recovery boundary information (STOPAT or MARK)	Final error state

3. Runtime execution trace

During execution, the procedure also emits operational progress messages to the console output.

These messages provide real-time visibility into the restore workflow, including:

• current execution phase
• restore step being executed
• target recovery mode
• progress checkpoints
• validation and completion messages
• immediate error visibility during execution

This runtime trace is intended for operational observability and troubleshooting, while persisted telemetry remains the source of auditable evidence.

Related Components

• [cfg].[usp_GetLatestBackupFiles] → Restore planning engine
• [cfg].[usp_ValidatePitrCanary] → Recovery validation engine
• [cfg].[usp_RunRestoreTests] → Recovery orchestration engine
• [log].[RestoreTestRun] → Restore execution header telemetry
• [log].[RestoreStepExecution] → Restore chain execution detail
• PRINTS - DEMO

Design Notes

This procedure represents the execution layer of the restore subsystem.

Its design separates restore planning from restore execution, allowing the framework to remain deterministic, traceable, and auditable.

By persisting both header-level and step-level telemetry, the procedure converts restore execution into a measurable operational process rather than an opaque administrative action.

Source Code

View full implementation

---

Home | Tables | Procedures