Database Maintenance

BitzOrcas.DatabaseMaintenance reuses Framework SqlSugarDatabaseBackupService and SqlSugarTableExportService for SQL Server full/differential/log backup, RESTORE VERIFYONLY, guarded restore, and CSV/JSON Lines table export. It is a privileged operations entry—not an everyday development CLI.

Operations and risk

Restore overwrites the named database using WITH REPLACE and is irreversible high risk. Verify proves SQL Server can read a backup set, not that a restore drill passed. Export uses SELECT * and may contain all PII/secrets.

Configuration and least privilege

CLI mode/parameters use a manual parser. Connection prefers --connection, then Configuration ConnectionStrings:Default; backup/export directories and database name can come from DatabaseBackup. Providers load appsettings, local, DBMAINT_ environment, and command line.

{
  "ConnectionStrings": {
    "Default": "Server=sql-ops;Database=BitzOrcas;User Id=bitz_ops;Password=Secr3t!StrongP@ss;TrustServerCertificate=True"
  },
  "DatabaseBackup": {
    "BackupDirectory": "/var/opt/mssql/backup/bitzorcas",
    "ExportDirectory": "/secure-export/bitzorcas",
    "DatabaseName": "BitzOrcas"
  }
}

Keep this in gitignored appsettings.local.json. Backup/Restore identities need their SQL Server privileges; Export should use read-only where possible. Do not grant restore to the normal application connection for convenience.

Filesystem topology is a hard contract

The SQL Server service interprets BACKUP DATABASE [<database>] TO DISK, while the CLI later calls local FileInfo/File.Exists for the same path. The backup directory must therefore be visible to both SQL Server and CLI with the same path string. Remote SQL Server plus workstation-local directory usually fails this contract.

Containers, Kubernetes, and remote SQL Server need a controlled shared volume with tested identity, UID/GID, SELinux, and path. The tool does not download .bak from the database host or upload it to object storage.

Full backup

# Validate path and permissions outside production; backup type defaults to Full.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --backup-database \
  --connection "$DB_MAINT_CONNECTION" \
  --backup-dir /var/opt/mssql/backup/bitzorcas \
  --database BitzOrcas \
  --backup-type Full

Full uses BACKUP DATABASE [<database>] TO DISK = '<path>' WITH FORMAT, INIT, SKIP, STATS=10 and names output <database>_full_<UTC timestamp>.bak. Timestamp precision is seconds, so avoid concurrent runs targeting the same name.

Success prints file name, size, and duration. Password/Pwd is masked in summary logs, but log and backup paths remain sensitive operations information.

Differential and log backup

# Differential requires a valid full base; the tool does not validate the whole chain.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --backup-database --backup-type Differential \
  --connection "$DB_MAINT_CONNECTION" --backup-dir "$BACKUP_DIR"

# Log requires FULL/BULK_LOGGED recovery model.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --backup-database --backup-type Log \
  --connection "$DB_MAINT_CONNECTION" --backup-dir "$BACKUP_DIR"

For SIMPLE recovery, Log backup returns success with Skipped=true; CLI exits 0 and prints the skip reason. Automation must alert on skip rather than treating code 0 as a healthy log chain.

Verify a backup

--backup-file is a plain filename under backup root. .., /, \, or path components are rejected; full path is normalized and checked inside root.

# VERIFYONLY is non-mutating but needs restore permission and file visibility.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --verify-backup \
  --connection "$DB_MAINT_CONNECTION" \
  --backup-dir "$BACKUP_DIR" \
  --backup-file BitzOrcas_full_20260716_020000.bak

The service executes RESTORE VERIFYONLY FROM DISK = '<path>' WITH STATS=10. Valid is code 0; SQL exceptions become IsValid=false and code 2. VERIFYONLY does not prove RTO, application smoke, logins/permissions, differential/log chain, or business-data correctness.

Restore guard

Restore requires exact --confirm RESTORE. The service first makes a full pre-restore backup of the current database, then runs RESTORE DATABASE [<database>] FROM DISK = '<path>' WITH REPLACE, RECOVERY, STATS=10 from the requested file.

# Isolated restore target only; there is no restore dry-run.
# File is a plain name in backup root; confirm database is not production.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --restore-database \
  --connection "$ISOLATED_RESTORE_CONNECTION" \
  --backup-dir "$BACKUP_DIR" \
  --backup-file BitzOrcas_full_20260716_020000.bak \
  --database BitzOrcas_RestoreDrill \
  --confirm RESTORE

If the pre-restore snapshot succeeds and restore fails, that snapshot remains and CLI returns 2. Record partial state rather than assuming nothing changed.

Restore drill

1. Copy/mount the complete backup chain into isolated SQL Server.
2. Run VERIFYONLY.
3. Restore under a new database name or dedicated instance.
4. Record start/end, size, SQL Server version, and RTO.
5. Run schema drift, seed, login, core API, JobHost, and messaging smoke.
6. Compare key table counts/checksums with source snapshot.
7. Ensure identities, keys, external connections, and broker cannot reach production.
8. Document restore failure and pre-restore snapshot rollback.

Only a real restore plus application validation proves recoverability.

Single-table export

# CSV is caret-delimited; json outputs .jsonl with one object per line.
dotnet run --project src/Tooling/BitzOrcas.DatabaseMaintenance -- \
  --export-table \
  --connection "$READONLY_EXPORT_CONNECTION" \
  --export-dir "$SECURE_EXPORT_DIR" \
  --table SysPermission \
  --format json

Table whitelist requires letter/underscore first, then alphanumeric/underscore, 1–128 characters; semicolon, slash, backslash, and .. are rejected. The tool also checks INFORMATION_SCHEMA.TABLES.

Only unqualified table names are supported. Query is fixed SELECT * FROM [table] with no column selection, WHERE, tenant filter, masking, or row limit. Streaming avoids loading the table in memory but still consumes database scan, network, and disk.

CSV and JSONL semantics

CSV uses ^, a header, and one record per line. Newlines become spaces, null becomes empty, and temporal values use O. Current formatting does not quote/escape a caret inside data, so such values can corrupt column structure.

JSON output uses .jsonl, one object per line, preserving null and basic SQL values. Neither format masks or encrypts fields; export storage needs restricted permissions, encryption, and expiry cleanup.

# Check permissions, file shape, and counts without printing data to public CI logs.
find "$SECURE_EXPORT_DIR" -maxdepth 1 -type f -ls
wc -l "$SECURE_EXPORT_DIR"/*

Exit codes

Cancelled backup/export can leave incomplete files; CLI does not delete them. Quarantine failed artifacts by run ID.

Security and operations checklist

• Confirm tool commit, SQL Server instance, database name, and directories twice.
• Backup/Restore and Export use distinct least-privilege identities.
• SQL Server and CLI share the same controlled backup path.
• Record hash, size, type, base/chain, and off-site copy for every backup.
• Real restore drill and application smoke follow VERIFYONLY.
• Restore quiesces connections, checks topology, and prepares failure rollback.
• Data owner approves Export; PII/secrets are masked or prohibited.
• Pre-check caret, schema names, and huge-table limitations.
• Alert separately when code 0 represents skipped Log backup.
• Partial files from failure/cancellation never enter retention chains.

See also

• Disaster recovery
• Production configuration
• Schema Maintenance
• Data protection