SQL Server Backup Automation: Azure, S3, and Beyond

SQL Server Backup Challenges

SQL Server environments present unique backup challenges. Native backup tools are powerful but require significant configuration. Cross-platform backup and restore needs careful handling. And Azure and AWS have different storage APIs that complicate multi-cloud strategies.

Native SQL Server Backup Options

Full Backups

BACKUP DATABASE [MyApp]
TO DISK = N'/backups/myapp_full.bak'
WITH COMPRESSION, ENCRYPTION (
  ALGORITHM = AES_256,
  SERVER CERTIFICATE = BackupCert
);

Differential Backups

Capture only changes since the last full backup:

BACKUP DATABASE [MyApp]
TO DISK = N'/backups/myapp_diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

Transaction Log Backups

For point-in-time recovery with minimal data loss:

BACKUP LOG [MyApp]
TO DISK = N'/backups/myapp_log.trn'
WITH COMPRESSION;

Cloud Storage Integration

Backup to Azure Blob Storage

SQL Server 2016 and later support direct backup to Azure Blob Storage:

BACKUP DATABASE [MyApp]
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/myapp.bak'
WITH CREDENTIAL = 'AzureStorageCredential', COMPRESSION;

Backup to S3 via BackupAgent

For S3 or multi-cloud setups, BackupAgent handles the export, compression, encryption, and upload:

jobs:
  - name: sqlserver-nightly
    engine: sqlserver
    database: MyApp
    schedule: "0 2 * * *"
    storage:
      provider: s3
      bucket: backups-sqlserver
      encryption: AES-256

Automated Restore Verification

SQL Server backups can be verified using Docker containers with the official Microsoft SQL Server image:

1. Pull mcr.microsoft.com/mssql/server:2022-latest
2. Start container with SA password
3. Restore backup using sqlcmd
4. Run integrity checks
5. Destroy container

This ensures every backup is recoverable, not just a file sitting in storage.

Recommended Strategy

Key Takeaway

SQL Server backup automation in 2026 should include cloud storage, encryption, and automated restore verification. Whether you use Azure, S3, or both, the principle is the same: prove every backup works.