Painless Azure SQL Database Backups

Windows Azure

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:

  1. Make a copy of the SQL Database
  2. Backup the database copy to Azure Storage
  3. 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.

SQL Database Information

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.

Azure Storage Information

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.

Advertisements

3 thoughts on “Painless Azure SQL Database Backups

  1. Pingback: Painless File Backups to Azure Storage | blog.nwoolls.com

  2. Chad

    Hello,

    Thank you for posting the AzureStorageCleanup project. I compiled it, specified the necessary parameters, however, when running it it states that there are not files to delete which are X days old. All files are older than 1 day old. What could I be missing?

    Thank you for your time!

    Reply
    1. nwoolls Post author

      Hi Chad. Thanks for the feedback and I’m sorry to hear you are having troubles. Unfortunately I am not able to reproduce this issue. Can you try setting a breakpoint in StorageCleaner.Cleanup and stepping through? The code is pretty brief and simple so may help to clarify what is going wrong.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s