[Update: When i wrote this post, the SQL Azure import and Export CTP was not available. But now it is – and it would let you export a SQL Azure database on local disks too. And since it is command driven, you’ll be able to automate the process on a local server]

In this article, I am going to discuss automating SQL Azure backup to an on premise location using a SQL Azure management cmdlet. Alternatively, you can also schedule a backup that backs up your SQL Azure data to Azure storage. But before we discuss how to go about it – couple of disclosures 1. Powershell cmdlet is to be executed from on premise machine as SQL Azure does not natively support Powershell but you can always choose SQL Azure as your target for your Cmdlet running locally 2. I am going to use a third party Azure management cmdlet from Cerebrata. You can download it from https://www.cerebrata.com/Products/AzureManagementCmdlets/Download.aspx

A complete setup guide is available at https://www.cerebrata.com/Docs/AzureManagementCmdlets/readme.pdf

Now, once you are done with installation and post installation steps – you will be able to see the list of cmdlet’s by running

Get-Command –PSSnapin AzureManagementCmdletsSnapIn

For automating the SQL Azure backup we are interested in the cmdlet named “Backup-Database” that let’s you back up SQL Azure to a local disk using bcp.exe utility. It also allows you to back up the database to blob storage. But remember the SQL Azure data transfer rates apply

Now, to backup a SQL Azure database to local disks run:

Backup-Database -Name “database1” -DownloadLocation “D:temp” -Server “mydatabaseserver” -UserName “username” -Password “password” –Verbose

here,

Backup-Database s the name of the cmdlet

-Name is the name of the database to be backed up

-DownloadLocation is the local location where the data will be backed up

-Server is the name of the server in which the database to be backed up is located

-Username is the username to login to the specified SQL Azure server

-Password is the password corresponding to the specified username used to login to the specified SQL Azure server

-Verbose is the parameter that will display detailed information about the operation performed by the cmdlet

After the back up is done, you will receive following message:

Now, if you wish to backup only specific table that you could do that by run the command:

Backup-Database -Name “database1” -DownloadLocation “D:temp” -Server “mydatabaseserver” -UserName “username” -Password “password” –TablesToInclude “<name1>,<name..n>” –Verbose

Now, one can schedule a PowerShell command – do that to automate your back up process!

If you wish to backup the database to blobs instead of local files. please read: http://parasdoshi.com/2011/07/03/sql-azure-backup-to-windows-azure-blobs-using-azure-management-cmdlet-powershell/

Just note that:

For now, the restore database through the Management CmdLet is not available – But basically the bigger picture of this post is to point out that – using Powershell CmdLet’s we will be able to schedule SQL Azure tasks! and that is definitely a good news for an IT pro.

Related Posts:

• Step by Step guide to Import a bacpac to SQL Azure Database via Import and Export CTP
• My Experiments with Enzo Backup Tool for SQL Azure