Microsoft SQL Server backup to Azure

I documented my experience implementing scheduled backups from Microsoft SQL Server 2012 running on an Azure VM to Azure Storage Blobs. While SQL 2012 supports this functionality, it requires executing T-SQL BACKUP statements directly rather than using SQL Server Management Studio.

Key Challenges & Solutions

Recovery Model Issue

The initial setup failed when a new database with "simple" recovery model was added. Log backups require the "full" recovery model to function properly.

Performance Consideration

Full recovery model isn't always necessary, as it generates significant log file activity. I adapted the original database query to filter only databases with full recovery enabled:

SELECT name FROM master.sys.databases DB WHERE database_id > 4 and recovery_model=1

Implementation

I created two stored procedures executed via scheduled jobs:

  1. stp_backuptoazure — Performs full database backups to Azure blob storage with compression
  2. stp_backuptoazure_log — Executes transaction log backups for databases with full recovery model

Both procedures use cursors to iterate through applicable databases and generate timestamped backup files in Azure blob containers using credential-based authentication.