Database Restore Feature

Restores subdomain data from a .bak (NativeBak) or .sql (SqlScript) backup file back into the production environment. Supports two modes: Merge (selective re-import into the shared production database) and Create New (standalone restore into a fresh database).

Architecture Overview

Admin Browser
    │  POST /api/DatabaseRestore/StartRestoreJobAsync (multipart/form-data)
    ▼
DatabaseRestoreController
    │  delegates to
    ▼
DatabaseBackupService.StartRestoreJobAsync()
    │  validates, saves file to storage, creates audit record, enqueues job
    ▼
Hangfire → DatabaseRestoreBackgroundJob.ExecuteAsync()
    │  RESTORE → validate subdomains → merge/rename → cleanup
    ▼
Production SQL Server

File Structure

Layer	File	Purpose
Controller	`Src/BusinessOwners/BsuinessOwners.AdminPortal/Controllers/DatabaseRestoreController.cs`	HTTP endpoints, file upload, request routing
Service interface	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/IDatabaseBackupService.cs`	Declares `StartRestoreJobAsync`, `GetRestoreJobStatusAsync`, `GetRestoreHistoryAsync`, and shared helpers
Service impl	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/DatabaseBackupService.cs`	Validates form, saves uploaded file, resolves connection string, creates audit record, enqueues Hangfire job
Background job	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/BackgroundJobs/DatabaseRestoreBackgroundJob.cs`	Full restore pipeline (NativeBak and SqlScript paths)
Status store interface	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/BackgroundJobs/IDatabaseBackupStatusStore.cs`	Redis read/write for live job progress
Status store impl	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/BackgroundJobs/RedisDatabaseBackupStatusStore.cs`	Concrete Redis implementation (`ICacheService`)
Storage abstraction	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/Storage/IBackupStorageProvider.cs`	Unified interface for FileSystem and Azure Blob operations
Audit store interface	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DataTransfer/Audit/IDataTransferAuditStore.cs`	Persists `BulkOperationAudit` records for all bulk operations
Server DTOs	`Src/BusinessOwners/BusinessOwners.Application/AdminPortal/DatabaseBackup/Dtos/`	`StartRestoreFormDto`, `RestoreBackupRequestDto`, `DatabaseRestoreJobStatusDto`, `RestoreJobHistoryDto`
Client service interface	`Src/BusinessOwners/BusinessOwners.Admin/BusinessOwners.Admin.Client/Features/DatabaseBackup/Services/IDatabaseRestoreClientService.cs`	Blazor HTTP client contract
Client service impl	`Src/BusinessOwners/BusinessOwners.Admin/BusinessOwners.Admin.Client/Features/DatabaseBackup/Services/DatabaseRestoreClientService.cs`	Multipart upload and polling via `IAdminService`
Client DTOs	`Src/BusinessOwners/BusinessOwners.Admin/BusinessOwners.Admin.Client/Features/DatabaseBackup/Models/`	`DatabaseRestoreJobStatusDto`, `RestoreJobHistoryDto`, `RestoreMode`, (also `RestoreFormat`)
Main page	`Src/BusinessOwners/BusinessOwners.Admin/BusinessOwners.Admin.Client/Features/DatabaseBackup/Pages/DatabaseRestorePage.*`	Upload form, progress bar, mini history feed
History page	`Src/BusinessOwners/BusinessOwners.Admin/BusinessOwners.Admin.Client/Features/DatabaseBackup/Pages/RestoreHistoryPage.*`	Full audit grid at `/database-restore/history`

Key Classes and Interfaces

`DatabaseRestoreController`

Routes all REST calls to IDatabaseBackupService. All endpoints carry [Authorize] at the controller level and the Blazor pages add [Authorize(Roles = Roles.Combined.AdminsOrSupport)].

csharp

public class DatabaseRestoreController(
    IDatabaseBackupService databaseBackupService,
    ILogger<DatabaseRestoreController> logger) : BaseController

Notable attributes on the upload action:

[RequestSizeLimit(2_000_000_000)] — allows files up to 2 GB
[Consumes("multipart/form-data")]

`IDatabaseBackupService` (restore-relevant methods)

csharp

// Validates form, saves file, resolves connection string, enqueues job. Returns jobId.
Task<string> StartRestoreJobAsync(StartRestoreFormDto dto, CancellationToken cancellationToken = default);

// Low-level: creates audit record and enqueues job from a pre-built RestoreBackupRequestDto.
Task<string> EnqueueRestoreJobAsync(RestoreBackupRequestDto request, CancellationToken cancellationToken = default);

// Checks Redis first; falls back to SQL audit record if Redis TTL has expired.
Task<DatabaseRestoreJobStatusDto?> GetRestoreJobStatusAsync(string jobId);

// Returns recent restore jobs from BulkOperationAudit + AdminOperationMetadata.
Task<List<RestoreJobHistoryDto>> GetRestoreHistoryAsync(int limit = 20, CancellationToken cancellationToken = default);

// Used by the background job to enumerate tables respecting FK dependency order.
Task<List<string>> GetTablesInDependencyOrderAsync(string connectionString, CancellationToken cancellationToken = default);

// Used by the background job to detect the subdomain discriminator column on a table.
Task<string?> GetSubdomainColumnAsync(string connectionString, string fullTableName, CancellationToken cancellationToken = default);

`DatabaseRestoreBackgroundJob`

The Hangfire job that performs the actual database work. Injected dependencies:

Dependency	Purpose
`IDatabaseBackupService`	`GetTablesInDependencyOrderAsync`, `GetSubdomainColumnAsync`
`IDatabaseBackupStatusStore`	Write live progress to Redis
`IDataTransferAuditStore`	Update `BulkOperationAudit` state (Processing / Completed / Failed)
`IBackupStorageProvider`	Delete uploaded file after job finishes; `DownloadRawAsync` for SQL scripts; `EnsureSqlCredentialAsync` for Azure Blob
`ILogger<DatabaseRestoreBackgroundJob>`	Structured logging

The job's ExecuteAsync(string jobId, RestoreBackupRequestDto request) method branches on request.Format:

RestoreFormat.NativeBak → ExecuteNativeBakRestoreAsync
RestoreFormat.SqlScript → ExecuteSqlScriptRestoreAsync

The finally block always deletes the uploaded backup file from storage regardless of success or failure.

`IDatabaseBackupStatusStore` / `RedisDatabaseBackupStatusStore`

Stores job progress in Redis. The restore-specific methods use the key prefix database-restore:status:.

csharp

Task<DatabaseRestoreJobStatusDto?> GetRestoreStatusAsync(string jobId);
Task UpdateRestoreStatusAsync(string jobId, DatabaseRestoreJobStatusDto status);

Redis keys expire after 24 hours (STATUS_TTL_HOURS = 24). If a client polls after that window, GetRestoreJobStatusAsync in the service falls back to reconstructing a minimal DatabaseRestoreJobStatusDto from the SQL BulkOperationAudit record.

`IBackupStorageProvider`

csharp

bool IsAzureBlob { get; }
string BackupBasePath { get; }
string BuildPath(string name);
Task UploadAsync(string name, byte[] content, CancellationToken cancellationToken = default);
Task<byte[]?> DownloadRawAsync(string path, CancellationToken cancellationToken = default);
Task DeleteAsync(string path, CancellationToken cancellationToken = default);
Task EnsureSqlCredentialAsync(SqlConnection conn, CancellationToken cancellationToken = default);

DownloadRawAsync returns uncompressed bytes — used to read SQL script content. EnsureSqlCredentialAsync is a no-op on the FileSystem provider and creates/refreshes a SAS-based SQL Server CREDENTIAL on the Azure Blob provider.

`IDataTransferAuditStore`

The restore feature uses this shared audit infrastructure (also used by DataTransfer and QuickMigrate). For restore jobs specifically:

CreateOperationAsync(jobId, BulkOperationType.DatabaseRestore, userId, email) — called during enqueueing
AddMetadataAsync(auditOp.Id, metadata) — stores AdminOperationMetadata with serialized JSON input parameters (subdomains, mode, format, newDatabaseName)
UpdateOperationAsync(id, action) — transitions state to Processing, Completed, or Failed and sets timing fields
GetRecentOperationsAsync(limit, BulkOperationType.DatabaseRestore) — used by GetRestoreHistoryAsync

`IDatabaseRestoreClientService` / `DatabaseRestoreClientService`

Blazor client. All HTTP calls go through IAdminService:

csharp

Task<List<SubdomainDropdownItem>?> GetSubdomainsAsync(CancellationToken ct = default);

Task<string?> StartRestoreJobAsync(
    IBrowserFile file,
    List<string> subdomainNames,
    RestoreMode mode,
    string? newDatabaseName,
    CancellationToken ct = default);

Task<DatabaseRestoreJobStatusDto?> GetJobStatusAsync(string jobId, CancellationToken ct = default);
Task<List<RestoreJobHistoryDto>?> GetRestoreHistoryAsync(int limit = 20, CancellationToken ct = default);

StartRestoreJobAsync builds a MultipartFormDataContent manually — the file stream allows up to 2 GB (maxAllowedSize: 2_000_000_000L), subdomains are sent as repeated subdomainNames form fields, and mode is sent as its integer value.

API Endpoints

All routes are under [Route("[controller]")] → /api/DatabaseRestore.

Method	Route	Auth	Description
`GET`	`/api/DatabaseRestore/GetSubdomainsAsync`	Authorize	Returns subdomain dropdown list
`POST`	`/api/DatabaseRestore/StartRestoreJobAsync`	Authorize	Upload file, start background job
`GET`	`/api/DatabaseRestore/GetJobStatusAsync/{jobId}`	Authorize	Poll live job progress
`GET`	`/api/DatabaseRestore/GetRestoreHistoryAsync?limit=N`	Authorize	Paginated history (default limit = 20)

POST /api/DatabaseRestore/StartRestoreJobAsync

Request — multipart/form-data bound to StartRestoreFormDto:

Field	Type	Required	Notes
`File`	`IFormFile`	Yes	`.bak` or `.sql`, max 2 GB
`SubdomainNames`	Repeated string fields	Yes	One entry per subdomain name
`Mode`	Integer	Yes	`0` = Merge, `1` = CreateNew
`NewDatabaseName`	String	Conditional	Required when `Mode = 1`

Response — 200 OK with StartBackupJobResponseDto:

json

{ "jobId": "3fa85f64-5717-4562-b3fc-2c963f66afa6" }

Validation errors (400 Bad Request):

File is missing or empty
Extension is not .bak or .sql
No subdomains selected
Mode = CreateNew but NewDatabaseName is blank
Subdomain connection string cannot be resolved

GET /api/DatabaseRestore/GetJobStatusAsync/

Response — 200 OK with DatabaseRestoreJobStatusDto:

json

{
  "jobId": "3fa85f64-...",
  "state": "Processing",
  "progress": 30,
  "subdomainNames": ["acme", "beta"],
  "targetDatabase": null,
  "mode": "Merge",
  "format": "NativeBak",
  "startedAt": "2025-03-05T10:00:00Z",
  "completedAt": null,
  "errorMessage": null,
  "statusLabel": "Validating subdomains in backup…"
}

state values: Pending, Processing, Completed, Failed.

GET /api/DatabaseRestore/GetRestoreHistoryAsync

Query params: limit (integer, default 20, max practical limit 50 as used by the history page).

Response — 200 OK with List<RestoreJobHistoryDto>:

json

[
  {
    "id": "...",
    "jobId": "3fa85f64-...",
    "state": "Completed",
    "triggeredByEmail": "admin@example.com",
    "subdomainNames": ["acme"],
    "targetDatabase": null,
    "format": "NativeBak",
    "mode": "Merge",
    "startedAt": "2025-03-05T10:00:00Z",
    "completedAt": "2025-03-05T10:04:22Z",
    "duration": "4m 22s",
    "errorMessage": null
  }
]

Data Models

Server-side DTOs

StartRestoreFormDto — the raw multipart form bound by the controller:

csharp

public class StartRestoreFormDto
{
    public IFormFile File { get; set; }
    public List<string> SubdomainNames { get; set; } = [];
    public RestoreMode Mode { get; set; }
    public string? NewDatabaseName { get; set; }
}

RestoreBackupRequestDto — the serialisable payload handed to Hangfire:

csharp

public class RestoreBackupRequestDto
{
    // Absolute local path (FileSystem) or full blob URL (Azure)
    public string FilePath { get; set; }
    // True when FilePath is a blob URL — triggers RESTORE FROM URL
    public bool IsAzureBlobPath { get; set; }
    public RestoreFormat Format { get; set; }
    public RestoreMode Mode { get; set; }
    public List<string> SubdomainNames { get; set; } = [];
    public string TargetConnectionString { get; set; }
    public string? NewDatabaseName { get; set; }
}

DatabaseRestoreJobStatusDto — live status from Redis:

csharp

public class DatabaseRestoreJobStatusDto
{
    public string JobId { get; set; }
    public JobState State { get; set; }       // Pending | Processing | Completed | Failed
    public int Progress { get; set; }          // 0–100
    public List<string> SubdomainNames { get; set; }
    public string? TargetDatabase { get; set; } // populated for CreateNew
    public RestoreMode Mode { get; set; }
    public RestoreFormat Format { get; set; }
    public DateTime StartedAt { get; set; }
    public DateTime? CompletedAt { get; set; }
    public string? ErrorMessage { get; set; }
    public string? StatusLabel { get; set; }   // human-readable step description
}

RestoreJobHistoryDto — audit trail record returned to client:

csharp

public class RestoreJobHistoryDto
{
    public Guid Id { get; set; }               // BulkOperationAudit.Id
    public string JobId { get; set; }           // Hangfire job ID (GUID string)
    public JobState State { get; set; }
    public string? TriggeredByEmail { get; set; }
    public List<string> SubdomainNames { get; set; }
    public string? TargetDatabase { get; set; }
    public RestoreFormat Format { get; set; }
    public RestoreMode Mode { get; set; }
    public DateTime StartedAt { get; set; }
    public DateTime? CompletedAt { get; set; }
    public string? Duration { get; set; }      // e.g. "4m 22s", "1h 3m"
    public string? ErrorMessage { get; set; }
}

Enums:

csharp

public enum RestoreMode   { Merge = 0, CreateNew = 1 }
public enum RestoreFormat { NativeBak = 0, SqlScript = 1 }

How It Works — Step-by-Step

Phase 1: Enqueueing (Controller → Service)

Admin uploads a .bak or .sql file via the Blazor UI.
DatabaseRestoreController.StartRestoreJobAsync receives the [FromForm] StartRestoreFormDto and passes it to DatabaseBackupService.StartRestoreJobAsync.
The service validates:
- File present and non-empty
- Extension is .bak or .sql
- At least one subdomain name provided
- For CreateNew mode: NewDatabaseName is non-empty
The service resolves the target connection string by looking up the first subdomain name via GetSubdomainConnectionStringAsync (which decrypts the stored connection string from AdminOperationMetadata).
The uploaded file is saved to storage:
- FileSystem: written to {NativeBakDirectory}/restore_{jobId}.{ext} — this directory is already accessible to SQL Server's service account.
- Azure Blob: uploaded to restore/{jobId}/{filename} in the blob container; storage.BuildPath() returns the full blob URL.
A RestoreBackupRequestDto is assembled with FilePath, IsAzureBlobPath, format, mode, subdomains, and connection string.
EnqueueRestoreJobAsync is called, which:
- Creates a BulkOperationAudit record (BulkOperationType.DatabaseRestore, state = Pending).
- Adds an AdminOperationMetadata record with serialised JSON input parameters.
- Sets OperationName to "RestoreBAK" or "RestoreSQL".
- Calls BackgroundJob.Enqueue<DatabaseRestoreBackgroundJob>(job => job.ExecuteAsync(jobId, request)).
The jobId (a GUID string) is returned to the client.

Phase 2: Background Job Execution

The Hangfire job (DatabaseRestoreBackgroundJob.ExecuteAsync) begins by:

Setting state to Processing in Redis and in the SQL BulkOperationAudit record.

It then branches by format.

Restore Pipeline — NativeBak

[10%] Restore to Temp Database

sql

-- Step 1: Discover logical file names from the backup header
RESTORE FILELISTONLY FROM DISK = N'<filePath>'
-- (or FROM URL for Azure Blob)

-- Step 2: Restore into an isolated temp DB
RESTORE DATABASE [RestoreTemp_<8 hex chars>]
FROM DISK = N'<filePath>'
WITH MOVE N'<dataLogical>' TO N'<dataPath>RestoreTemp_<8 hex>.mdf',
     MOVE N'<logLogical>'  TO N'<dataPath>RestoreTemp_<8 hex>_log.ldf',
     RECOVERY

Before issuing RESTORE DATABASE, the job:

Calls DropDatabaseAsync on any pre-existing temp DB with the same name (leftover from a previous failed run).
Calls SqlConnection.ClearAllPools() so .NET's connection pool releases any handles to the old database name, allowing SQL Server to acquire an exclusive lock.
For Azure Blob: calls storage.EnsureSqlCredentialAsync(masterConn) to create or refresh the SAS-based SQL CREDENTIAL.

The data path for the restored .mdf/.ldf files is queried from SERVERPROPERTY('InstanceDefaultDataPath').

The temp DB name is RestoreTemp_{jobId[0..8]} (first 8 hex chars of the GUID with dashes removed).

[30%] Validate Subdomains

The job enumerates every table in the temp DB (via GetTablesInDependencyOrderAsync) and for each table checks whether it has a subdomain discriminator column (via GetSubdomainColumnAsync). It collects all distinct subdomain values found across those columns.

If any requested subdomain name is not found in the backup, the job throws:

Backup does not contain subdomain(s): <missing>. Subdomains found in backup: <found list>

The temp DB is then dropped and production is never touched.

[50%] Merge or Create New

Merge mode — ExecuteMergeAsync:

Disables all FK constraints on the production database:

sql

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Iterates tables in FK dependency order. For each table:

Tables with a subdomain column (tenant-specific data):

sql

-- Remove existing rows for selected subdomains
DELETE FROM [schema].[table]
WHERE [subdomainCol] IN (@s0, @s1, ...)

-- Preserve identity values (critical for FK integrity)
SET IDENTITY_INSERT [schema].[table] ON
INSERT INTO [schema].[table] ([col1], [col2], ...)
SELECT [col1], [col2], ...
FROM [RestoreTemp_<id>].[schema].[table]
WHERE [subdomainCol] IN (@s0, @s1, ...)
SET IDENTITY_INSERT [schema].[table] OFF

Reference/lookup tables (no subdomain column) — upsert only:

sql

INSERT INTO [schema].[table] ([col1], [col2], ...)
SELECT [col1], [col2], ...
FROM [RestoreTemp_<id>].[schema].[table] src
WHERE NOT EXISTS (
    SELECT 1 FROM [schema].[table] tgt WHERE tgt.[PK] = src.[PK]
)

Tables with no primary key and no subdomain column are skipped with a debug log entry.

Re-enables FK constraints in a finally block (even if an error occurred mid-merge).
Progress advances from 50% to 90% proportionally as tables are processed.

Create New mode:

sql

-- Pools cleared first; ALTER requires exclusive lock
SqlConnection.ClearAllPools();
ALTER DATABASE [RestoreTemp_<id>] MODIFY NAME = [<NewDatabaseName>]

The temp DB is simply renamed. No production data is touched.

[95%] Cleanup

Merge mode: SqlConnection.ClearAllPools() then DROP DATABASE [RestoreTemp_<id>].
Both modes (in finally): the uploaded backup file is deleted — storage.DeleteAsync(filePath) for blob, File.Delete(filePath) for file system. File deletion failures are logged as warnings but do not fail the job.

Restore Pipeline — SqlScript

[10%] Load Script

FileSystem: File.ReadAllTextAsync(filePath)
Azure Blob: storage.DownloadRawAsync(filePath) → UTF-8 decode

[30%] Validate Subdomains

Scans the script line by line. Any line containing the substring "Subdomain" or "SubdomainName" (case-insensitive) is searched for single-quoted string literals using the regex '([^']+)'. All matched values are collected into a set.

If any requested subdomain is absent from that set, the job fails with a descriptive error — no SQL has been executed yet.

Note: This heuristic works well for scripts exported from SQL Server Management Studio (INSERT INTO ... VALUES (...) format) but may miss subdomains in scripts with non-standard formatting.

[50%] Execute

Merge mode:

Disables FK constraints.
Splits the script on GO statement separators (regex: ^\s*GO\s*$, multiline, case-insensitive).
Executes each non-empty batch against the production connection string.
Re-enables FK constraints in a finally block.
Progress advances from 50% to 90% as batches are processed.

Create New mode:

sql

CREATE DATABASE [<NewDatabaseName>]

Then executes the GO-batched script against the newly created database.

[95%] Cleanup

Same as NativeBak: uploaded file deleted, audit record updated.

Status Tracking

Live progress is stored in Redis via IDatabaseBackupStatusStore. The Blazor client polls GetJobStatusAsync/{jobId} every 2 seconds while State is Pending or Processing.

Redis key: database-restore:status:{jobId} — TTL is 24 hours.

Progress milestones:

Progress	StatusLabel
0%	Starting…
10%	Restoring backup to temp database… (NativeBak) / Reading SQL script… (SqlScript)
30%	Validating subdomains in backup… / Validating subdomains in script…
50%	Merging data into production… / Renaming temp database… / Executing SQL script against production database… / Creating new database '…'…
50–90%	Progress increments per table/batch during merge
95%	Dropping temp database…
100%	Completed

When polling stops (state becomes Completed or Failed), the client displays a MudSnackbar notification and reloads the history feed.

Column Handling During Merge INSERT

The merge INSERT builds an explicit column list (not SELECT *) to avoid three classes of SQL errors:

Column type	Handling
Identity columns	`SET IDENTITY_INSERT ON/OFF` wraps the INSERT to preserve original PK values (required for FK integrity)
`timestamp`/`rowversion`	Excluded from column list — SQL Server manages these automatically
Computed columns	Excluded from column list — SQL Server derives their values from persisted data

The query used to build column lists for subdomain tables (GetAllColumnsAsync):

sql

SELECT c.COLUMN_NAME,
       COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME),
                      c.COLUMN_NAME, 'IsIdentity') AS IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @Schema AND c.TABLE_NAME = @Table
  AND c.DATA_TYPE <> 'timestamp'
  AND COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME),
                     c.COLUMN_NAME, 'IsComputed') = 0
ORDER BY c.ORDINAL_POSITION

For reference/lookup tables (GetNonIdentityColumnsAsync), identity columns are also excluded because the upsert does not need IDENTITY_INSERT (no subdomain discriminator means no full row replacement):

sql

AND COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME),
                   c.COLUMN_NAME, 'IsIdentity') = 0

Storage Abstraction

The feature reuses the same IBackupStorageProvider abstraction from the backup feature.

Environment	`StorageType` config value	File saved to	SQL Server reads via
Development / Staging	`"FileSystem"`	`{NativeBakDirectory}/restore_{jobId}.{ext}` (e.g. `C:\Backups\restore_abc123.bak`)	`FROM DISK = N'<path>'`
Production	`"AzureBlob"`	`restore/{jobId}/{filename}` in Azure Blob container	`FROM URL = N'<blobUrl>'` (SAS credential)

IsAzureBlobPath on RestoreBackupRequestDto tells the background job which SQL clause to use at runtime.

Exclusive Lock Strategy

SQL Server requires an exclusive lock on a database for RESTORE DATABASE, ALTER DATABASE MODIFY NAME, and DROP DATABASE. Each of these operations is preceded by:

csharp

SqlConnection.ClearAllPools();

This force-closes all .NET connection-pool entries associated with that database name. Without it, SQL Server sees active connections and returns error 5030 ("The database could not be exclusively locked to perform the operation").

The pattern appears at three points in the NativeBak pipeline:

Before RESTORE DATABASE [RestoreTemp_...] (clears any stale pool entries from a prior drop).
Before ALTER DATABASE MODIFY NAME in CreateNew mode (clears connections opened during validation).
Before DROP DATABASE [RestoreTemp_...] in Merge mode (clears connections opened during the merge).

Audit Trail

Every restore job produces:

A BulkOperationAudit record:
- JobId — the Hangfire job ID
- OperationType — BulkOperationType.DatabaseRestore
- OperationName — "RestoreBAK" or "RestoreSQL"
- State — Pending → Processing → Completed / Failed
- TriggeredByUserId, TriggeredByUserEmail
- StartedAt, CompletedAt, Duration
- ErrorMessage, ErrorStackTrace (on failure)
An AdminOperationMetadata record attached to the audit:
- MetadataType — AdminOperationMetadataType.DatabaseRestore
- ActionName — format string ("NativeBak" / "SqlScript")
- TargetResource — comma-separated subdomain names
- InputParameters — serialised JSON:
  json
```
{
  "subdomains": ["acme", "beta"],
  "mode": "Merge",
  "format": "NativeBak",
  "newDatabaseName": null
}
```
- IpAddress, UserAgent of the triggering request

GetRestoreHistoryAsync batch-loads audit records and their metadata in two queries, then joins them in memory to produce RestoreJobHistoryDto items. Duration is formatted as "{N}s", "{N}m {S}s", or "{N}h {M}m".

Blazor UI

`/database-restore` — Main Restore Page (`DatabaseRestorePage`)

Route: @page "/database-restore". Requires Roles.Combined.AdminsOrSupport.

Layout: Two-column grid — config panel on the left, progress + history on the right.

Left panel — config:

File upload area (hidden <InputFile> triggered by a clickable <label>). Accepts .bak / .sql. On selection, detectedFormat is set by file extension and a format chip (.BAK in orange, SQL in blue) is shown.
Subdomain autocomplete (MudAutocomplete). Searches allSubdomains client-side (loaded on init). Selected subdomains appear as MudChips with a remove button.
Mode selector: two card-style toggle buttons — Merge and Create New. Merge shows a warning that existing rows will be replaced. Create New reveals a NewDatabaseName text field.
Start Restore button (color Error / red). Disabled until: file selected AND at least one subdomain AND (mode is Merge OR newDatabaseName is non-empty). Shows a spinner while isSubmitting.

Right panel — progress and history:

Active job progress bar: visible while liveStatus.State is Pending or Processing. Shows MudProgressLinear with percentage and StatusLabel.
Mini history feed: loads last 5 jobs (limit: 5). Each card shows: state icon, target DB or subdomain names, email, relative time pill, state/format/mode/duration badges, subdomain chips, error box.
"View All" button links to /database-restore/history.
Refresh icon button reloads history.

Polling logic (DatabaseRestorePage.razor.cs):

On page init: subdomains and history load in parallel via Task.WhenAll. If an in-progress job is found in history, polling resumes automatically (StartPolling).

On submit (StartRestoreAsync): file uploaded, job ID received, StartPolling called. After 1.5 s, history reloads.

StartPolling uses System.Threading.Timer with a 2-second interval calling PollAsync. When the job reaches Completed or Failed, StopPolling is called, a snackbar is shown, and history reloads.

Dispose() stops the polling timer.

State-to-color helpers:

State	Border/icon color	Background tint
Completed	`#4caf50` (green)	`rgba(76,175,80,0.04)`
Failed	`#f44336` (red)	`rgba(244,67,54,0.04)`
Processing / Pending	`#2196f3` (blue)	`rgba(33,150,243,0.05)`

`/database-restore/history` — Restore History Page (`RestoreHistoryPage`)

Route: @page "/database-restore/history". Requires Roles.Combined.AdminsOrSupport.

Loads up to 50 jobs (limit: 50). State filter chips (All / Completed / Failed / Running) filter the FilteredHistory computed property client-side.

MudDataGrid columns:

Column	Data
Status	`MudChip` with color and icon
Format	Badge pill (BAK = orange, SQL = blue)
Mode	Badge pill (Merge = purple, New DB = green)
Subdomains	First 3 as chips with tooltip; overflow shown as `+N`
Target DB	Monospace text
Email	`TriggeredByEmail`
Started	Local time, full datetime in tooltip
Duration	Pre-formatted string
Error	First 40 chars with full text in tooltip

Breadcrumb: Home > Database Restore > Restore History.

Error Handling

Scenario	Behaviour
Invalid file extension	`ArgumentException` thrown in service; controller returns 400
No subdomains selected	`ArgumentException` → 400
CreateNew with blank DB name	`ArgumentException` → 400
Subdomain not found in system	`ArgumentException` → 400
Subdomain missing from backup	`InvalidOperationException` thrown in background job; state → Failed; temp DB dropped; production untouched
SQL error during merge	Exception caught; state → Failed; `ErrorMessage` + `ErrorStackTrace` stored; FK constraints re-enabled in `finally`
SQL error during rename/drop	Logged as warning; primary exception re-thrown
File deletion failure	Logged as warning; does not affect job state
Redis unavailable	Status store swallows read errors and returns null; service falls back to SQL audit record
Redis TTL expired (>24h)	`GetRestoreJobStatusAsync` reconstructs minimal status from `BulkOperationAudit`

The Blazor client surfaces errors via MudSnackbar — Severity.Error on failed job completion, Severity.Info on job start. GetSubdomainsAsync and StartRestoreJobAsync in DatabaseRestoreClientService catch all exceptions and add a snackbar directly.

Configuration

No dedicated configuration section exists for the restore feature. It reuses the backup feature's storage and connection-string configuration.

Required connection strings (appsettings.json / appsettings.user.json):

json

{
  "ConnectionStrings": {
    "SqlServerConnection": "Server=...;Database=master;..."
  }
}

SqlServerConnection is used as the base connection string. SwapDatabase() in the background job replaces InitialCatalog to connect to master, the temp DB, or the production tenant DB as needed.

Storage configuration (shared with backup feature):

json

{
  "DatabaseBackup": {
    "StorageType": "FileSystem",
    "NativeBakDirectory": "C:\\Backups\\"
  }
}

Production (appsettings.prod.json):

json

{
  "DatabaseBackup": {
    "StorageType": "AzureBlob",
    "AzureBlob": {
      "ConnectionString": "DefaultEndpointsProtocol=https;...",
      "ContainerName": "sql-backups"
    }
  }
}

Security

All four controller endpoints require [Authorize]. The Blazor pages additionally enforce [Authorize(Roles = Roles.Combined.AdminsOrSupport)].
Uploaded backup files are always deleted after the job completes (success or failure) — the finally block in ExecuteAsync guarantees this.
Subdomain validation against the actual backup content prevents accidental cross-tenant restores — a backup for tenant A cannot be used to restore tenant B without the job failing at the 30% validation step.
Reference table upsert uses NOT EXISTS — rows belonging to other subdomains in shared lookup tables are never modified.
The audit trail records the IP address and user agent of every job submission.
Target connection strings are resolved server-side from encrypted storage — clients never send connection strings.

Database Restore Feature ​

Architecture Overview ​

File Structure ​

Key Classes and Interfaces ​

DatabaseRestoreController ​

IDatabaseBackupService (restore-relevant methods) ​

DatabaseRestoreBackgroundJob ​

IDatabaseBackupStatusStore / RedisDatabaseBackupStatusStore ​

IBackupStorageProvider ​

IDataTransferAuditStore ​

IDatabaseRestoreClientService / DatabaseRestoreClientService ​

API Endpoints ​

POST /api/DatabaseRestore/StartRestoreJobAsync ​

GET /api/DatabaseRestore/GetJobStatusAsync/ ​

GET /api/DatabaseRestore/GetRestoreHistoryAsync ​

Data Models ​

Server-side DTOs ​

How It Works — Step-by-Step ​

Phase 1: Enqueueing (Controller → Service) ​

Phase 2: Background Job Execution ​

Restore Pipeline — NativeBak ​

[10%] Restore to Temp Database ​

[30%] Validate Subdomains ​

[50%] Merge or Create New ​

[95%] Cleanup ​

Restore Pipeline — SqlScript ​

[10%] Load Script ​

[30%] Validate Subdomains ​

[50%] Execute ​

[95%] Cleanup ​

Status Tracking ​

Column Handling During Merge INSERT ​

Storage Abstraction ​

Exclusive Lock Strategy ​

Audit Trail ​

Blazor UI ​

/database-restore — Main Restore Page (DatabaseRestorePage) ​

/database-restore/history — Restore History Page (RestoreHistoryPage) ​

Error Handling ​

Configuration ​

Security ​

Database Restore Feature

Architecture Overview

File Structure

Key Classes and Interfaces

`DatabaseRestoreController`

`IDatabaseBackupService` (restore-relevant methods)

`DatabaseRestoreBackgroundJob`

`IDatabaseBackupStatusStore` / `RedisDatabaseBackupStatusStore`

`IBackupStorageProvider`

`IDataTransferAuditStore`

`IDatabaseRestoreClientService` / `DatabaseRestoreClientService`

API Endpoints

POST /api/DatabaseRestore/StartRestoreJobAsync

GET /api/DatabaseRestore/GetJobStatusAsync/

GET /api/DatabaseRestore/GetRestoreHistoryAsync

Data Models

Server-side DTOs

How It Works — Step-by-Step

Phase 1: Enqueueing (Controller → Service)

Phase 2: Background Job Execution

Restore Pipeline — NativeBak

[10%] Restore to Temp Database

[30%] Validate Subdomains

[50%] Merge or Create New

[95%] Cleanup

Restore Pipeline — SqlScript

[10%] Load Script

[30%] Validate Subdomains

[50%] Execute

[95%] Cleanup

Status Tracking

Column Handling During Merge INSERT

Storage Abstraction

Exclusive Lock Strategy

Audit Trail

Blazor UI

`/database-restore` — Main Restore Page (`DatabaseRestorePage`)

`/database-restore/history` — Restore History Page (`RestoreHistoryPage`)

Error Handling

Configuration

Security