Backup/restore and archive both govern data lifecycle, but at different scopes. Backup protects an entire database. Active archive moves historical rows for one tenant from hot tables to archive tables. Both current production adapters require SqlSugar and SQL Server; they are not multi-ORM parity features.

1. Two boundaries

Backup files are not tenant-scoped, and restore replaces the current database. Archive requires a trusted TenantId; the handwritten tenant route never invokes all-tenant execution.

2. Current backup provider

SqlSugarDatabaseBackupService is the only production implementation. It uses Microsoft.Data.SqlClient and SQL Server T-SQL directly:

full BACKUP DATABASE;
differential database backup;
BACKUP LOG;
RESTORE VERIFYONLY;
RESTORE DATABASE ... WITH REPLACE, RECOVERY.

There is no EF Core or other-provider equivalent. API composition uses UnavailableBackgroundJobPorts to fail closed when the service is absent.

3. Create backup

CreateBackup.Command.Type is parsed case-insensitively as Full, Differential, or Log. Anything else is a validation error.

Production/Staging validates an approval incident. Other environments pass. Scheduled JobHost backups call IDatabaseBackupService directly and do not traverse this HTTP approval handler.

4. Successful skipped log backup

Under SQL Server SIMPLE recovery, Log backup returns a successful result with Skipped=true, an empty filename, zero bytes, and a reason.

Monitoring must count skipped separately. Result.IsSuccess alone can report green while no log backup is created.

5. Directory and naming

The directory comes from DatabaseBackup:BackupDirectory, falling back to an application-local Backups folder. DatabaseName is configured or parsed from the connection string.

Names follow {database}_{full|diff|log}_{yyyyMMdd_HHmmss}.bak. Concurrent requests of the same type in one second compute the same name and use WITH INIT; there is no lease.

6. Create example

// ① Request a log backup; the Host environment decides whether approval is required.
var result = await mediator.Send(
    new CreateBackup.Command(Type: "Log", ApprovalTicket: ticket),
    cancellationToken);

if (result.IsFailure)
    return BackupDecision.Failed(result.Error.Code);

var backup = result.Value!;
// ② SIMPLE recovery returns success plus skipped, not a created file.
if (backup.Skipped)
    return BackupDecision.Skipped(backup.SkipReason!);

return BackupDecision.Created(backup.FileName, backup.SizeBytes);

7. Listing hides absolute paths

ListBackups enumerates every .bak file and sorts by last modified descending. Public DTOs expose filename, size, time, and a type inferred from the name, never FullPath.

Type inference checks full, diff, log, then prerestore. Unknown names remain unknown. There is no paging, so very large directories are fully enumerated.

8. Filename path defense

Verify and restore call ValidateBackupFileName. It rejects blank values, .., separators, and drive-qualified input, then checks the normalized path remains under the backup root.

This blocks common traversal. Symlink safety still depends on filesystem and deployment controls; a dedicated service account should own the directory.

9. VERIFYONLY result semantics

An invalid path or missing file is a failure. A SQL Server verification exception becomes successful Result + IsValid=false + exception message.

The Verify handler then writes audit without a protective try/catch. Audit failure can fail the request after verification completed. Create and Restore suppress audit-sink exceptions, so behavior is inconsistent.

10. Restore gates

Confirm is case-sensitive. Approval is effectively mandatory in Production/Staging, not every environment.

11. Separation of duties degrades

The handler rejects self-approval only when the gate resolves Incident.CreatedBy and current EffectiveUserId exists.

No OpsExtension store, a non-required environment, a ticket read failure, or missing identity produces null and permits the operation. This is best-effort SoD, not mandatory dual control.

12. Restore does not verify first

After confirming the file exists, restore creates a full backup of the current database and executes RESTORE ... WITH REPLACE, RECOVERY.

It does not call VERIFYONLY, validate source database identity, backup-chain completeness, encryption, version compatibility, or rehearsal evidence. The separate verify use case is not bound to restore.

13. Pre-restore snapshot

The snapshot is {database}_prerestore_{timestamp}.bak. Restore never starts if snapshot creation fails. The public response strips the absolute path.

Snapshot and restore do not form a rollback transaction. If restore fails after the snapshot, the file remains. Current retention deletes pre-restore files after three days.

14. Backup retention

PruneExpiredBackupsAsync uses:

DailyRetentionDays for recent full backups;
Sunday full backups for WeeklyRetentionWeeks;
day-one full backups for MonthlyRetentionMonths × 30 days;
daily-window retention for diff/log;
fixed three-day pre-restore retention;
warning-and-continue for individual deletion failure.

It is a file-age approximation, not a backup-chain graph, legal hold, immutable storage, or off-site-copy proof.

15. Two active archive policies

Resource	Online	Archive	Action	Archive table
NotificationInbox	365 days	1095 days	ColdStorage	`SysNotification_Archive`
LoginLog	180 days	730 days	Delete	`SysLoginLog_Archive`

They are an immutable in-code list, not tenant-configurable policies.

16. Archive transaction semantics

The SqlSugar executor supports SQL Server only. For each tenant, one transaction repeatedly executes DELETE TOP ... OUTPUT DELETED ... INTO archive in batches of 1,000 and writes SysArchiveBatch in the same transaction.

Hot-row deletion, archive insertion, and the tenant batch record commit together. The all-tenant job uses one transaction per tenant, not one transaction for every tenant.

17. Tenant boundary and the fail-closed manual-archive audit

Manual execution takes ICurrentUser.User.TenantId; blank or "0" is always rejected (Archiving.Tenant.Required), so Host identity cannot trigger a tenant archive. Batch query also filters by the current tenant. PageIndex at most zero becomes 1, PageSize at most zero becomes 20, and PageSize above 1000 is capped at 1000. PageWindow has a default maximum offset of 100000; beyond it, the current handler returns an empty page with TotalCount 0, PageIndex 1, and PageSize 20. That fallback loses the requested metadata and must not be interpreted as proof of no data.

The manual-archive guard chain runs in fixed order, and the audit is now fail-closed, not best-effort:

Tenant check (tenantId blank or "0" → Archiving.Tenant.Required);
resource-name, approval-ticket, and reason length checks;
confirm-token check (must exactly match "ARCHIVE"), before approval and any side effect;
approval-gate validation;
policy existence, and the policy Action must be Archive (e.g. ColdStorage is rejected with Operations.Archive.ManualActionUnsupported, also before audit and execution);
intent audit: writes DataRetention.Archive.Intent. If it fails, returns Operations.Archive.AuditUnavailable and the archive does not execute;
executes ArchiveTenantAsync;
completion audit: writes DataRetention.Archive.Completed. If execution succeeded but the audit write fails, returns Operations.Archive.OutcomeUnknown instead of success.

Put differently: the archive executor no longer “writes best-effort audit after completion and only warns on failure.” If the intent audit cannot be written, no data is touched. If the data was moved but the completion audit cannot be written, the caller receives a failure, not a success. This keeps audit records and data migration consistent under failure — both succeed or neither does, eliminating the gray zone where “data was archived but no operation record exists.” SysArchiveBatch still preserves actor, timestamp, count, and record-time range as more specific evidence.

18. Cold storage is not implemented

NotificationInbox declares RetentionAction.ColdStorage, but MoveToColdStorageAsync always returns ColdStorageUnavailable. The manual entry also rejects any non-Archive policy with Operations.Archive.ManualActionUnsupported before execution. Active archive only moves data to a database archive table.

The enum does not prove object storage, checksum, encryption, legal hold, query, or deletion evidence.

19. HTTP reachability

Archive has handwritten routes and tenant-boundary tests. Four backup requests only have nested [GenerateEndpoint] declarations; the generator limitation requires a mapping fix and real HTTP contract tests.

20. Disaster-recovery acceptance

Validate full/diff/log chains in an isolated environment.
Require VERIFYONLY and bind verification hash to restore.
Restore into an isolated database, not only in-place WITH REPLACE.
Measure RPO/RTO and prove encryption, access, and off-site copies.
Prevent same-second concurrent backup overwrite.
Alert and compensate for audit failures.
Make retention chain-aware and legal-hold-aware.
Rehearse archive, cold storage, delayed delete, and query paths.

21. Source inspection

# Locate the concrete SQL Server backup, restore, and archive commands.
rg -n "BACKUP DATABASE|RESTORE VERIFYONLY|WITH REPLACE|DELETE TOP" \
  src/Framework/BitzOrcas.Infrastructure.SqlSugar -g '*.cs'

# Exercise the current-tenant archive boundary.
dotnet test tests/BitzOrcas.Application.Tests \
  --filter FullyQualifiedName~ArchiveTenantBoundaryTests

Operations overview · Schema safety