Trying to troubleshoot Windows Azure Compute role startup issues
Hosting a public web site and an internal services site in one Windows Azure web role

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.

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

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?

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Comments are moderated, and will not appear until the author has approved them.

Your Information

(Name is required. Email address will not be displayed with the comment.)