While the SQL Database service from Windows Azure provides resiliency and redundancy, there is no built in backup feature to guard against data loss due to user- or program-error. The advised way to handle this is to take a three-step approach:
- Make a copy of the SQL Database
- Backup the database copy to Azure Storage
- Maintain & remove any outdated backups on blob storage
The process in Windows Azure that backs up a SQL Database to blob storage is not transactionally consistent, which is why the initial database copy is required.
Richard Astbury has provided an excellent tool, SQLDatabaseBackup, that takes care of the first two steps with little fuss:
SQLDatabaseBackup.exe -datacenter eastus -server hghtd75jf9 -database MyDatabase -user DbUser -pwd DbPassword -storagename mybackups -storagekey YourSuperLongAzureStorageKey -cleanup
The data center and server name can be obtained from the SQL Databases section of the Windows Azure Portal.
The Azure storage account name and access key can be accessed in the Storage section of the portal, by clicking the Manage Access Keys button at the bottom of the portal.
Finally, by specifying the -cleanup argument, the utility will delete the SQL Database copy it creates after the backup is successfully created.
And while the pricing for Azure blob storage is very affordable, you may want to automate the process of deleting old backups. I’ve created a very simple utility that does just that. AzureStorageCleanup uses command line arguments that mirror the SQLDatabaseBackup project (as it is meant to compliment its use):
AzureStorageCleanup.exe -storagename mybackups -storagekey YourSuperLongAzureStoragekey -container sqlbackup -mindaysold 60
The above command will remove files equal-to-or-older-than sixty days from the container “sqlbackup” – the default container used by SQLDatabaseBackup. The details of each file deleted are printed to the console.
By scheduling these two utilities on an available machine you’ll have painless, affordable backups for any of your Windows Azure SQL Databases.