DBAtlas · Database operations atlas

Back up to URL (Azure Blob SAS / S3-compatible storage)

SQL ServerBackup & Log

Command

-- A) Azure Blob: SAS credential (named after the container URL, SECRET without a leading '?')
CREATE CREDENTIAL [https://dbabackup.blob.core.windows.net/sql]
WITH IDENTITY = N'SHARED ACCESS SIGNATURE',
     SECRET   = N'sv=2022-11-02&ss=b&srt=co&sp=rwdlac&se=2027-01-01T00:00:00Z&sig=XXXX';

BACKUP DATABASE [Musteri]
TO URL = N'https://dbabackup.blob.core.windows.net/sql/Musteri_full_1.bak',
   URL = N'https://dbabackup.blob.core.windows.net/sql/Musteri_full_2.bak'
WITH COMPRESSION, CHECKSUM, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, STATS = 10;

-- B) S3-compatible storage (2022+):
CREATE CREDENTIAL [s3://s3.eu-central-1.amazonaws.com/dba-backup/sql]
WITH IDENTITY = N'S3 Access Key',
     SECRET   = N'<AccessKeyID>:<SecretKeyID>';

BACKUP DATABASE [Musteri]
TO URL = N's3://s3.eu-central-1.amazonaws.com/dba-backup/sql/Musteri_full.bak'
WITH COMPRESSION, CHECKSUM, FORMAT, STATS = 10;

-- Verify (uses the same credential):
RESTORE VERIFYONLY
FROM URL = N's3://s3.eu-central-1.amazonaws.com/dba-backup/sql/Musteri_full.bak'
WITH CHECKSUM;

What it does

Writes the backup directly to object storage: to Azure Blob as block blobs with a SAS credential, or on 2022 to S3 API-compatible storage (AWS S3, MinIO etc.) with an access key credential. The credential is picked automatically by matching the URL prefix.

When to use it

For off-site backup requirements, cloud migration/hybrid DR scenarios, or when local backup storage is insufficient.

Caution

On Azure a single block blob holds at most 50,000 blocks; with 4 MB MAXTRANSFERSIZE that is roughly 195 GB per file, so stripe large databases across several URLs. On S3 a file has at most 10,000 parts; for large databases raise MAXTRANSFERSIZE (up to 20 MB, requires COMPRESSION) and stripe across multiple URLs (up to 64). S3 does not support INIT/append; FORMAT overwrites an existing backup with the same name. When the SAS expires, backup jobs fail with an error; put the expiry date in the calendar. The credential secret is stored in master; principals with CONTROL SERVER can use it.

Open in DBAtlas →

Related