sql-backup-upload

Imported from panchalvimaldev/sql-backup-upload (AGENTS.md). Install upstream with npx skills add panchalvimaldev/sql-backup-upload. Copyright stays with the author.

SqlBackupService Agent Guide

Scope

This file applies to the complete SqlBackupService/ directory. The project is a classic .NET Framework 4.5.1 executable hosted as a Topshelf Windows Service, with a --runnow console mode. Ignore bin/ and obj/; they are generated. There is no automated test project.

Treat App.config as sensitive. It contains deployment-specific, live-looking SQL, Azure, and SMTP credentials. Never quote or duplicate those values in output, documentation, patches, or tests. Credential rotation is an external deployment task.

Build and verification

Dependencies are restored from packages.config into ..\packages. Build from this directory:

dotnet msbuild SqlBackupService.csproj /t:Build /p:Configuration=Debug /v:minimal

This is an old-style project. Add new C# files explicitly to the <Compile Include="..."> list in SqlBackupService.csproj.

Never run --runnow merely to test a build. It performs real SQL backups, local file writes/deletes, Graph uploads/deletes, and possibly email. Topshelf install/start/stop/uninstall commands also change machine state.

Architecture

• Program.cs: TLS 1.2, configuration validation, --runnow, and Topshelf setup. Service name is SqlOneDriveBackup, running as LocalSystem.
• BackupService.cs: exact-clock scheduling, timer lifecycle, active-task tracking, cancellation, and bounded shutdown wait.
• Orchestrator/BackupOrchestrator.cs: separates SQL capture from compression/upload, owns concurrency gates, local/optional remote retention, email, and resource telemetry.
• Backup/SqlBackupManager.cs: parameterized/escaped T-SQL backup paths, immutable naming, recovery-model validation, and optional RESTORE VERIFYONLY.
• Compression/FileCompressor.cs: cancellable streaming GZip with an 80 KiB buffer; incomplete GZip output is removed while the source is retained.
• Upload/OneDriveUploader.cs: long-lived HTTP connection pools, OAuth client credentials, Graph upload sessions, sequential chunks, optional remote retention, and throughput logging.
• Infrastructure/AppConfig.cs: eager typed validation, defaults, and %ENVIRONMENT_VARIABLE% expansion.
• Infrastructure/DatabaseBackupConfigSection.cs: custom per-database schedule section.
• Infrastructure/RetryHelper.cs: three total attempts with 2- and 4-second retry delays.
• Infrastructure/Logger.cs: console and daily lock-protected UTF-8 log files; also defines JobTranscript, an explicit (non-ambient) per-job log capture object passed as an optional trailing parameter through the pipeline and attached verbatim to that job's completion email.

Pipeline and concurrency invariants

BackupOrchestrator.RunAsync has two stages:

1. Acquire the per-database SQL lock and global _sqlBackupSlots; create one uniquely named .bak; release both locks immediately after SQL capture.
2. Wait for global _processingSlots; optionally GZip, upload, apply retention, then (Full jobs only) fetch OneDrive quota and send an HTML completion email carrying the full JobTranscript for that job.

RunAsync creates one JobTranscript per call and threads it as an optional trailing parameter into every _logger.Info/Warn/Error call it makes directly and into every downstream method (TakeBackupAsync, CompressAsync, UploadAsync, both Purge* remote calls, RetryHelper.ExecuteAsync). This is deliberate, explicit parameter passing rather than ambient/async-local state, because concurrent jobs (different databases, or SQL capture overlapping another job's processing stage) share the same Logger instance and must not have their log lines cross-contaminate each other's transcript. When adding a new logged step anywhere in the pipeline, thread transcript through it the same way — a step that silently logs only to console/file without accepting transcript will be missing from the completion email.

Defaults are one SQL capture and one processing job at a time. Preserve this separation: slow cloud upload must not hold the database SQL lock or suppress a future log capture. Waiting jobs retain raw immutable files on disk, so log queue depth and free disk space.

Do not revert to a lock keyed by database plus backup type. Full, differential, and log SQL commands for one database must serialize. Do not revert to fixed log filenames: every transaction-log artifact is part of an ordered restore chain.

The processing concurrency setting bounds both GZip and upload. Each active upload allocates one UploadChunkSizeMB buffer on the large-object heap. Increasing chunk size or processing concurrency increases memory; it cannot increase the capacity of a saturated uplink.

The shared OneDriveUploader is intentionally long-lived. Graph API requests carry bearer authorization, but PUT requests to the pre-authenticated uploadUrl must not. Dispose every request/response/content object, but do not create an HttpClient per chunk or job.

Scheduling

• Full jobs use a rearmed one-shot timer for the next local fullBackupTime; this avoids elapsed-24-hour DST drift.
• Differential and log timers align to each database's full time rather than service startup.
• An interval occurrence within 15 minutes of full time is skipped. Thus a 04:00 full with a four-hour differential interval produces differentials at 08:00, 12:00, and so on; a 05:10 full with hourly logs produces logs at 06:10, 07:10, and so on.
• logIntervalMinutes="0" disables logs.
• Different databases can queue together, but configured global gates bound actual expensive work.

When changing timer code, preserve stable wall-clock alignment across service restarts, the full-time exclusion, cancellation-safe rearming, and immediate-return timer callbacks.

Artifact and restore semantics

All backup types use:

yyyy_MM_dd_HHmmss_fff_<DatabaseName>_<Full|Differential|Log>.bak

GZip changes the extension to .gz; if GZip is disabled, .bak is uploaded directly. The OneDrive destination is /<DatabaseName>/<filename>, and that database folder must already exist.

Log backups allow FULL and BULK_LOGGED recovery models. A usable restore chain needs the selected full, an optional applicable differential, and every required log in LSN order. Any change to naming, deletion, conflict behavior, queuing, or retention must be evaluated against that entire chain.

Local retention runs after every successful upload and uses last-write time for .bak/.gz, governed by BackupRetentionDays across all backup types. Two further remote-retention layers are opt-in and independent of each other and of local retention:

• PurgeSupersededDiffAndLog + DiffAndLogGraceDays: after a Full succeeds, deletes that database's Differential/Log artifacts (local + OneDrive) whose file time is before (this Full's capture completion) - DiffAndLogGraceDays. The databaseLock-serialized per-database SQL capture guarantees anything before the Full's own capture is genuinely from the dead chain; the grace-day subtraction only makes the cutoff more conservative (keeps more, never deletes something that would otherwise be kept), so it can't undermine that safety property. The grace period exists deliberately — it is not a same-day purge — to keep a point-in-time recovery chain available for a disaster noticed a day or two late.
• PurgeOldFullBackupsRemote + FullBackupRemoteRetentionMonths: deletes only _Full artifacts on OneDrive older than the configured number of months. Does not touch local Full backups or Differential/Log artifacts.

Remote deletion is externally destructive; never enable or test either setting without explicit authorization and folder validation.

VerifyBackup=true runs RESTORE VERIFYONLY ... WITH CHECKSUM, but this is not a substitute for a real test restore.

Configuration defaults and constraints

• BackupRetentionDays=30, positive integer.
• UploadChunkSizeMB=10, range 1-60 and byte size divisible by 320 KiB. With whole MiB, valid values are 5, 10, 15, ... 60.
• MaxConcurrentSqlBackups=1, allowed 1-10.
• MaxConcurrentProcessingJobs=1, allowed 1-4.
• ShutdownWaitSeconds=30, allowed 1-600.
• UseBackupCompression=false controls native SQL COMPRESSION.
• UseGZipCompression defaults to the opposite of native compression, avoiding redundant double compression unless explicitly overridden.
• VerifyBackup=false; it adds material disk I/O.
• PurgeSupersededDiffAndLog=false; DiffAndLogGraceDays=2, positive integer (no "purge immediately" value — smallest is 1 day).
• PurgeOldFullBackupsRemote=false; FullBackupRemoteRetentionMonths=5, positive integer.
• ServiceLogRetentionDays defaults to BackupRetentionDays (the app's own rolling .log files, unrelated to transaction-log backups).
• LogResourceUsage=true logs private bytes, working set, managed memory, backup-disk free space, chunk size, and upload MB/s.
• OneDriveLowSpaceThresholdGB=10, positive integer. Below this many GB of drive.quota.remaining (fetched via Graph /drive?$select=quota), the storage line in the completion email renders in red.
• Email is enabled by nonempty EmailTo; EmailFrom is then required. SMTP connection settings come from system.net/mailSettings. The message is HTML (MailMessage.IsBodyHtml = true); all dynamic values (database name, machine name, failure text, transcript) are passed through WebUtility.HtmlEncode before being embedded — never interpolate untrusted/dynamic text directly into the HTML body.

Configuration accepts %VARIABLE_NAME% references in app settings and connection strings and fails startup if a referenced variable is missing. SMTP section protection/secret handling remains a deployment concern.

Known limits and operational work

• Total upload time remains bounded by compressed bytes divided by available uplink bandwidth. Serialization stabilizes throughput and memory but cannot reduce that mathematical minimum.
• If backup generation continuously exceeds upload capacity, immutable raw files queue on disk. There is telemetry but no configured maximum queue size or automatic capacity escalation.
• Failed uploads retain the artifact but are not automatically rediscovered after process restart; a durable queue/manifest would be needed for guaranteed later delivery.
• Graph upload sessions are retried within a running job but are not persisted for cross-process resume.
• The service can perform optional remote VERIFYONLY-independent retention, but actual restore drills, SQL maintenance analysis, bandwidth provisioning, OneDrive permission setup, and secret rotation are external operations.
• Native SQL compression support is not auto-detected. Enable it only for a known supporting SQL Server edition and benchmark CPU versus disk/network savings.

Change checklist

• Preserve .NET Framework 4.5.1 compatibility.
• Preserve immutable names and complete transaction-log chains.
• Preserve bounded memory: streaming file operations, reusable small buffers, and configured gates.
• Propagate cancellation through SQL, compression, retry delay, token, Graph, and chunk operations.
• Retain completed artifacts on upload/cancellation failure so they can be recovered manually.
• Redact credentials and connection strings in diagnostics.
• Update README.md, config defaults, and this guide when runtime behavior changes.
• Run the MSBuild command and git diff --check. Report that no real backup/upload/delete/email operation was executed.