Backup SQL Azure Database

SQL Azure database is a cloud based relational database service built on Microsoft SQL Server technologies. It provides a highly available database service hosted by Microsoft in the cloud.

One major drawback currently with SQL Azure is that there is no option for backup. Database Copy TSQL command has given at least one option for directly backing up a SQL Azure database by copying a source Azure database to a newly created Azure database. Using “COPY DATABASE” TSQL command you can copy database to a different Azure server but this does not protect the data from a disaster recovery point of view. If you like to copy the database to a different Azure account that would be billed separately to the source database account. In real life everyone likes to go with backup solution. So what could be the solution?

Cerebrata’s Azure Management Cmdlet provides some basic PowerShell cmdlets for managing the SQL Azure Databases. “Backup-Database” cmdlet is one of the cmdlet which to some extent is the solution for this problem. “Backup-Database” cmdlet can be used to backup your SQL Azure databases. “Backup-Database” cmdlet makes use of bcp.exe utility to download the data from tables in a SQL Azure database and saves them as text files on your local computer. This cmdlet also has the capability to store this backup data in blob storage. It first downloads all the data in text files on your computer and then uploads these files in specified blob storage. To save space in blob storage, you can also instruct this cmdlet to compress these files using GZIP compression before uploading to blob storage.

System Requirement

In order to use these cmdlets, you would need the following:

• Windows 7/Vista/XP SP2+ Operating system
• Net 4.0 installed on the computer
• PowerShell installed on the computer (PowerShell is installed by default on Windows 7)
• SQL Server 2008 R2 Client utilities (esp. bcp.exe version 10.50.1600.1 or higher) for backing up SQL Azure databases.

How to use Cmdlet

To use this cmdlet first you would need to install these cmdlets which you can download from our website at http://www.cerebrata.com/products/azure-management-cmdlets/introduction , here you select appropriate download version (32 bit or 64 bit) suitable for the computer on which you are installing this software.

After installation is complete you would need to add add PS-Snapin, launch PowerShell  and type the following command at PowerShell command prompt:

Add-PSSnapin AzureManagementCmdletsSnapIn

Add-PSSnapin command adds the AzureManagementCmdletsSnapIn snap-ins to the current session. If you wish then you can add this command in PowerShell profile file (Microsoft.PoerShell_profile.ps1).This file is located under MyDocument\WindowsPowerShell folder.

·         After adding the AzureManagementCmdletsSnapIn snap-ins to the current session, you can run any command supported by AzureManagementCmdletsSnapIn. If you want to see all command list, type the following at PowerShell command prompt:

Get-Command –PSSnapin AzureManagementCmdletsSnapIn

Examples

Now we’ll talk about different scenarios in which a SQL Azure database can be backed up:

Backup SQL Azure database to local disk only

Command example:

C:\PS>Backup-Database -Name “database1” -DownloadLocation “D:\temp” -Server “mydatabaseserver” -UserName “username”  -Password “password” -Verbose

Backs up all tables in “database1” database and saves the data in “D:\temp\database1” folder. All files will be saved in [schema name]_[table name].txt format. Here no need to define storage account information, because all files will be saved to local disk and not uploaded to blob storage.

Backup SQL Azure database to local disk and upload backup files to blob storage

This cmdlet also has the capability to store this backup data in blob storage. For that it first downloads all the data in text files on your computer and then uploads these files in specified blob storage.

Command example:

C:\PS>Backup-Database -Name “database1” -DownloadLocation “D:\temp” -Server “mydatabaseserver” -UserName “username”   -Password “password” -SaveToBlobStorage -BlobContainerName “myblobcontainer” -AccountName “myaccount” -AccountKey  “base64 encoded accountkey” -Verbose

Backs up all tables in “database1” database and saves the data in “D:\temp\database1” folder. All files will be saved in [schema name]_[table name].txt format. After all tables are downloaded from the database server, they will be uploaded in “myblobcontainer” blob container in “myaccount” storage account. Here you need to define correct storage account information. If –BlobContainerName parameter is not specified, then this cmdlet auto-assigns the name of the blob container which matches [database name]-backup-[backup start date time] pattern.

Backup SQL Azure database to local disk and upload compressed backup files to blob storage

To save space in blob storage, you can also instruct this cmdlet to compress these files using GZIP compression before uploading to blob storage. For that you need to specify –CompressBlobs parameter to compress the files before uploading to blob storage.

Command example:

C:\PS>Backup-Database -Name “database1” -DownloadLocation “D:\temp” -Server “mydatabaseserver” -UserName “username”   -Password “password” -SaveToBlobStorage –CompressBlobs  -AccountName “myaccount” -AccountKey  “base64 encoded accountkey” -Verbose

Backs up all tables in “database1” database and saves the data in “D:\temp\database1” folder. All files will be saved in [schema name]_[table name].txt format. After all tables are downloaded from the database server, compress all the files using GZIP compression then they will be uploaded in “myblobcontainer” blob container in “myaccount” storage account.

Parameters use in “Backup-Database” Cmdlet

Following table summarizes the “Backup-Database” Cmdlet parameters:

Sample File

We have also included sample file for Backup-Database command. This sample file is located under “Samples” folder in the installation directory; here you can get sample files for all supported command. In order to use this sample file, just change the values appropriately and execute them. Shown below is an example of Backup-Database sample file for backs up all tables from specified database and save the data in specified location.

#############################################################################################

#### Sample powershell script to backup a SQL Azure database by downloading its table’s data to   #############

#### Local disk using “bcp” utility. Optionally this cmdlet can also upload these data files to ##################

#### Azure Blob Storage. #####################################################################

#### Replace the parameters specified in <> with actual parameters.####################################

#############################################################################################

# 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 database 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 password>”;

# Name of the database e.g. mydatabase

$databaseName = “<name of the database>”;

# Download location where the contents will be saved e.g. “D:\Temp”

$downloadLocation = “<download location>”;

# Optional parameters for saving data files to blob storage

# -SaveToBlobStorage

# -BlobContainerName

# -AccountName

# -AccountKey

# -CompressBlobs

Backup-Database -Name $databaseName -DownloadLocation $downloadLocation -Server $databaseServerName -Username $userName -Password $password