Automating backup of a SQL Azure database to Azure Blob Storage with the help of PowerShell and Task Scheduler

OK, I am being a bit lazy on this one Winking smile. I am not writing any custom PowerShell CmdLets here but use the powerful Azure Management CmdLets.

  1. After you have downloaded the trial version of the CmdLets, make sure you have followed the setup instructions outlined in the “Azure Management Cmdlets - Getting Started Guide” document (which can be found in the installation folder of the CmdLets).
  2. Start PowerShell (this has been tested with PS 2.0 which is installed by default on Windows 7 and Windows Server 2008 R2).
  3. After having registered the CmdLets (again please see “Azure Management Cmdlets - Getting Started Guide”) you can run a backup script using the SQL Azure backup CmdLet (there are tons more in the package!).


Here is a sample script:

#########################################################################################
## Powershell script to backup a SQL Azure database by downloading its tables data to  ##
## local disk using "bcp" utility and uploading these data files to Azure Blob Storage.##
#########################################################################################
# Name of your SQL Azure database server. Please specify just the name of the database server.
# For example if your database server name is "abcdefgh", just specify that 
# and NOT "tcp:abcdefgh.database.windows.net" or "abcdefgh.database.windows.net"
$databaseServerName = "<your_sql_azure_server_name>";
# User name to connect to database server. Please specify just the user name.
# For example if your user name is "myusername", just specify that
# and NOT "myusername@myservername".
$userName = "<your_sql_azure_user_name>";
# Password to connect to database server.
$password = "<your_sql_azure_user_password>";
# Name of the database e.g. mydatabase.
$databaseName = "<your_sql_azure_database_name";
# Download location where the contents will be saved.
$downloadLocation = "c:\Temp\SQLAzure";
# Windows Azure Storage account name to use to store the backup files.
$storageAccount = "<your_windows_azure_storage_account_name>";
# Windows Azure Storage account primary key.
$storagekey = "<your_windows_azure_storage_account_key>";
# Blob container to use for storing the SQL Azure backup files.
$storageContainer = "sqlazurebackup";
Backup-Database -Name $databaseName -DownloadLocation $downloadLocation 
-Server $databaseServerName -Username $userName -Password $password
-SaveToBlobStorage -AccountName $storageAccount -AccountKey $storageKey
-BlobContainerName $storageContainer -CompressBlobs

The above script first backups data in all tables to a local temporary folder and then uploads the data in compressed form into blob storage. When everything worked successfully you will see something like this:

Make sure to verify with a storage explorer tool that your backup files actually ended up in blob storage – e.g.:

image


Last but not least, you could create a recurring task via Windows task scheduler to periodically run your backup PS script (e.g. http://blog.pointbeyond.com/2010/04/23/run-powershell-script-using-windows-server-2008-task-scheduler/).
I am not showing the details here – should be pretty straight-forward.


Hope this helps.