Book a Demo Free Trial

Backup SQL Azure Database

Gaurav Mantri

Feb 2, 2011

Category: General

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:

S.No Parameter Name Description
1.        -Name Name of the database which needs to be backed up. It is a required parameter.
2.        -DownloadLocation  Name of the directory where the tables data will be saved. If this directory does not exist, it will be created by the cmdlet. It is a required parameter.
3.        -BatchSize This cmdlet makes use of “bcp” utility to download the table data. This parameter specifies the maximum number of rows bcp utility should fetch from a table in a single call to the database. Default value for this parameter is 1000.
4.        -AbortOnError This parameter indicates if any downloadable component fails to download, then the process should be terminated. If this parameter is not specified, then process continues even if one or more downloadable components fail to download.
5.        -SaveToBlobStorage This parameter specifies if the table data downloaded should also be saved in blob storage.
6.        -AccountName Name of your Azure Storage account. If this parameter along with “AccountKey” parameter is not specified and “SaveToBlobStorage” parameter is specified, tables backup data will be saved in development storage.
7.        -AccountKey Key of your Azure Storage Account. If this parameter along with “AccountName” parameter is not specified and “SaveToBlobStorage” parameter is specified, tables backup data will be saved in development storage.
8.        -BlobContainerName Name of the blob container where this back up data should be uploaded. If the specified blob container does not exist, it will be created. If this 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.
9.        -CompressBlobs This parameter specifies if the backup files should be compressed using GZIP compression before being uploaded to blob storage.
10.    -LoggingLevel Specifies the kind of logging desired when invoking this cmdlet. Possible values are:

  • None: No logging is done.
  • Summary: Only the start and end of each process is logged. This is the default logging level.
  • Detailed: Every action of the process is logged.

The log file is created in user’s temp directory under “Cerebrata AzureManagementCmdlets\logs” folder.

11.    -Server Name of the database server. You can find this value by logging into SQL Azure Portal at https://sql.azure.com.Please specify only the name of the database server. Application will automatically create a fully qualified connection string at the time of connection. For example, if the database server name is “abcdefgh”, please specify that only for this parameter. Please do not specify “abcdefgh.database.windows.net” for this parameter. It is a required parameter.
12.    -Username Username to connect to the database server. You can find this value by logging into SQL Azure Portal at https://sql.azure.com. While specifying the username, there is no need to append “@[databaseservername]” to the username as it will be appended automatically when connecting to the database. For example, if your username is “myusername”, specify this value only as the value for this parameter instead of “myusername@databaseservername”. It is a required parameter.
13.    -Password Password to connect to the database server. You can find this value by logging into Sql Azure Portal at https://sql.azure.com. It is a required parameter.
14.    -ConnectionTimeout Timeout interval in seconds after which an attempt to connect to a SQL Azure database will be timed out. Default value is 30 seconds.
15.    -CommandTimeout  Timeout interval in seconds after which a command against a SQL Azure database will be timed out. Default value is 30 seconds. Keep this value longer for operations which you know will take more time to execute.
16.    -Verbose This is the common parameter. Displays detailed information about the operation performed by the command.

 

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