Steps for Deploying Package to Azure Environment:

Step 1: Creating Storage Account

Before deploying the package to windows azure, we required to create Azure Storage service account for storing the Input XML and logging information files.

Input XML is used for informing the Worker Role about the list of databases with service name, user id, password for login to the database and where the backup files to be stored etc. There are two log files are used in the Worker Role, one is the Action Log and another is Error Log. These log files can be used for tracing what happened while doing these activities. So let’s first create a Storage Account.

  1. Open the Azure Management Portal. url -
  2. Select the Hosted Services, Storage Accounts & CDN and select Storage Accounts from the left panel. Create a new storage account by selecting the subscription and New Storage Account icon. (Here I created storage Account Name as tsqlazurebackup)
  3. Once the Status of the Storage Account become Created, select the storage account and press the Viewbutton from the Properties. Copy the Primary Key somewhere temporarily.
  4. Create a Blob Container in the Storage Account. You can use either Windows Azure Management Tool ( or Azure Storage Explore ( for doing the same.
  5. Note the following information which will be used in next step.

Storage Account Name: tsqlazurebackup

Storage Access Key:[Your Access Key]

Storage Blob Container Name: sqlazurebackup

(Note: This information I used when publishing into my subscription for temporary purpose, this won’t work future)

Step 2: Updating Storage Account information in the configuration file

  1. Download the package from the Download Page and extract it. There will be two files in the .zip file.
    • ServiceConfiguration.Cloud.cscfg – Is the configuration file, which hold the configuration for the storage account and the logging informations.
    • SQLAzureBackup.cspkg – Is the worker role package file has the logic to backup.
  2. Open the ServiceConfiguration.Cloud.cscfg file and change the DataConnectionString,InputXMLBlobConnectionString settings value from UseDevelopmentStorage=true to the storage account connection string in the below format.


    So for the Storage Account I created, the connection string would be

    DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=[Account Key]

    Below shows the DataConnectionStringInputXMLBlobConnectionString setting nodes –

    <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=[Account Key]" />

    <Setting name="InputXMLBlobConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=[Account Key]" />

  3. Replace the InputXMLBlobContainerName settings value to the container name created in Step 1.3. As I have sqlazurebackup as the container name, the settings would as below –

    <Setting name="InputXMLBlobContainerName" value="sqlazurebackup" />

  4. There are two other settings present in the configuration file which inform the worker role to log the action details and error details.

Change the LogError to True to log error occurred while taking the backup and False not required.

<Setting name="LogError" value="True" />

The same applied to the action details also

<Setting name="LogAction" value="True" />

Service Sleeping time, 60 minutes by default

<Setting name="SleepingTime" value="60" />


At the last my configuration file would as below

<?xml version="1.0" encoding="utf-8"?>
<ServiceConfiguration serviceName="SQLAzureBackup" xmlns="" osFamily="2" osVersion="*">
  <Role name="WorkerRole">
    <Instances count="1" />
      <Setting name="Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString" value="UseDevelopmentStorage=true" />
      <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=https;AccountName=tsqlazurebackup;AccountKey=[Account Key]" />
      <Setting name="LogError" value="True" />
      <Setting name="LogAction" value="True" />
      <Setting name="InputXMLBlobConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=[Account Key]" />
      <Setting name="InputXMLBlobContainerName" value="sqlazurebackup" />

Step 3: Changing the input XML

Input XML file is used for providing input to the worker role about the list of databases to take backup with login information and the place where the backup to be stored etc.

  1. Create a new xml file in any of your favorite XML editor or note pad with file name as SQLAzureBackupData.xml. If you create using notepad, save the file as XML (save the file name with double quote – “SQLAzureBackupData.xml”)
    (Note: As of now the code will look at the SQLAzureBackupData.xml from the container of the storage service configured. I will be modify the code to configure the file name also from the next version)
  2. Copy and paste the following script in the XML file and modify the list of databases as you required.

The database backup can be in two methods

  • Backup to storage account – The database backup file will be stored in blob storage with predefined formatted name. The storage account details such as storage account name, access key, and container name must be provided when preferring the backup to storage account.

    Note: There is no link between the storage account specified in this Database node and the storage account specified in the ServiceConfiguration.Cloud.cscfg configuration file. The storage account configured in Database node will be used for storing database backup and the storage account configured in the ServiceConfiguration.Cloud.cscfg file used for storing the input XML file and logging files. So each database node can refer different storage account or can be same.

  • Backup to File System – The database backup will be stored in a drive, which can be to a local system, a network drive. This method can be used when the worker role runs on on-premise server. Currently I did not verify the Azure Drives, will be updated in future.

Below picture shows the XML structure for both of the backup methods


The attributes of Database node explained below.

    • ServerName – Specifies the SQL Azure Server Name – Required. ( For Ex:
    • DatabaseName – Specifies the SQL Azure database name – Required.
    • UserWindowsAuth – does the authentication uses windows authentication for login to the database? – Required.
    • LoginId – SQL Azure login Id – Required.
    • Password – Password for login – Required.
    • ExportTo – Specifies whether the backup required being stored in Blob storage or File System – Required. (Two values acceptable – Blob or FileSystem – For Ex: ExportTo=”Blob” orExportTo=”FileSystem”)
    • BackupFileName – Specifies the file name of the backup file – Required.
    • The name can have @Date and @Time for specifying the date and time in the file name will be replaced in ddMMMyyy and hhmmss format. For Example if the file name is Customers_@Date_@Time.bacpac, then the file name will be replaced in the code to Customer_17Oct2011_072112.bacpac.
    • StorageAccountName – The blob name for storing the backup file (Required only ifExportTo=”Blob”)
    • BlobContainerName – The storage account container name (Required only if ExportTo=”Blob”)
    • BlobDefaultEndpointsProtocol – The protocol on the blob can be accessible (http / https) (Required only if ExportTo=”Blob”)
    • StorageAccessKey – The Access Key for the storage account (Required only if ExportTo=”Blob”)
    • ExportFileDirectory – The directory where the exported file will be stored (Required only ifExport=”FileSystem”)
    • TrustServerCertificate – Whether trust the server certificate? (Yes – true, No - false)
    • UseSSL – Required to use SSL? (Yes – true, No – false)
    • BackupScheduledPeriod – Specifies the period of backup interval - Required. (value will be “Daily”, "Weekly" or "Monthly" – For Ex: BackupScheduledPeriod=”Daily”)
    • BackupScheduledTime – Specifies the time of the backup – Required. The backup will be taken on or before the hour and minute defined here. (For Ex: if the backup time at 12:00 AM -BackupScheduledTime=”12:00 AM”, the backup will start when the time trigger 12:00 AM each day.)
    • LastBackupDateTime – Specifies the date and time of last backup completed. This is used to track the interval period. So to take backup on today, the LastBackupDateTime value must be on yesterday. This value will be updated automatically, but at initial this value must be updated manually.

You can have required attributes for the required backup you choose in the Database node. The following XML shows an example of my XML file which I used for testing on cloud.

<?xml version="1.0" encoding="utf-8"?>
    <Database ServerName="[ServerName]"
              BackupScheduledTime="1:57 PM" 
              LastBackupDateTime="08-Nov-2011 06:18 PM" 
              BlobAccessKey="[Access Key]"></Database>

Upload the XML file to the container of the storage account configured in theServiceConfiguration.Cloud.cscfg. The worker role will start execution and when the server time meets the time mentioned in the XML file for any of the Database node with the date it will start the backup.

Currently the backup time will be considered as per the time running on the server instance. but I will work on this to standardize the timing for various zone. But as of now the backup schedule will be handled as below.

Say, we have required the backup to be done from tomorrow (01-June-2012) and the backup should be done by 12:00AM on daily basis. The values should be


BackupScheduledTime="12:00 AM"

LastBackupDateTime="31-May-2012 12:00 AM"

When the worker role executing on 01-Jun-2012 at 12:00 AM, it will check whether the last backup date in XML equal to 31-May-2012 or less than that date. It will also check the backup scheduled time is 12 hours and minutes is less than compare to the current minute on the system. If this conditions are meet, the backup schedule will start and it will update the last backup date time to the current date time.

Upload the XML file to the blob container of the storage account configured and verify the backup file in the targeted location specified in the input XML.

Last edited Oct 7, 2013 at 12:37 PM by jaafar_abusair, version 4