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 . 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.:

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.

Comments

[Andy, CloudBerry Lab]

Want to learn another way to backup the data to Azure? Try CloudBerry Backup. It is powered by Azure Blob Storage. Unlike other vendors we have one time fee and no recurring charges. Besides, there is no proprietary data format and you can access your data using other Azure tools.

[Andy, CloudBerry Lab]

BTW - we would be happy to grant you a free license if you consider to review CloudBerry Backup on your blog. Sorry for such a comment but we didn't find the contact form.

[TBA]

Hi Christian,

nice script, thx for sharing.
Do you also have experience with restoring such a backup?

TBA

[TBA]

@Andy: As I can see your program is build to backup blob storage data but not SQL Azure databases. Correct if I'm wrong. If not, are you also planing to develop a SQL Azure backup and recovery system?

[Amita]

Hi Christian,
When I specify the SaveToBlobStorage parameter with the required details, I get error "The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or a non-white space character among the padding characters."
Any idea on this?