Project Description

 It's a simple Azure Worker that will backup any of your SQL Azure Database tables to selected Azure Blob Storage using BCP utility.

Why BCP?

Because it is possible to backup Azure SQL Federations. You can specify every federation as a separate database (same server, different database name) and it will backup all tables of that federation.

Most of code is based on project http://azureautobackup.codeplex.com i have mostly simplified the code to support onlu Azure Blob Storage destination, added 7zip compression.

How to start?

1. Open it in Visual Studio 2012 with Azure Tools June 2012 installed
2. Add new Windows Azure Cloud Service Project (Worker Role)
3. Replace the Worker Role code with code downloaded from this project
4. Add this configuration settings to azure ServiceConfiguration.[...].cscfg file: 

   <Setting name="Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString" value="DefaultEndpointsProtocol=https;AccountName=XXX;AccountKey=XXX" />
   <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=https;AccountName=XXX;AccountKey=XXX" />
   <Setting name="LogError" value="True" />
   <Setting name="LogAction" value="True" />
   <Setting name="InputXMLBlobConnectionString" value="DefaultEndpointsProtocol=https;AccountName=XXX;AccountKey=XXX" />
   <Setting name="InputXMLBlobContainerName" value="config" />
   <Setting name="BcpLocation" value="D:\Program Files\Microsoft SQL Server\100\Tools\Binn" />
   

5. Change Azure Storage connection string (InputXMLBlobConnectionString) and blob container name (InputXMLBlobContainerName) to point where the config file will be stored.
6. Change Azure Storage connection string  (DataConnectionString) where the logs table will be saved.
7. Edit the configuration file SQLAzureBackupData.xml and put there all data of all databases you  wish to backup (like server name, database name, login, password, backup file name, maximum schedule period, preffered backup hour and connection string information for Azure Storage Blob where backup has to be saved).
8. Save configuration file to Azure Storage Blob you defined in point 5. 
9. Deploy worker to Azure.
10. Check for Log Table contents (point 6) to see if there are no errors.  

How it works?

After deployment package will automatically install BCP as a Startup Task.

Then it will download config file from Azure Blob (defined in point 5) and read backup details.

It will execute BCP for each table in each database that should be backed up (hasn't been backed up for BackupScheduledPeriod hours). It will connect to server ServerName using LoginId and Password and create .dat files. File name template is defined in config as BackupFileName

After BCP finish, it will execute 7za to compress backup file to ZIP and then upload it to Azure Blob defined by parameters StorageAccountName, StorageAccessKey, BlobContainerName.

IMPORTANT:

BCP doesn't store database definitions (tables, views, procedures). It stores only raw table data. You MUST backup you table definition some other way, like using Visual Studio Source Control or any other way. You MUST have database definitions to recover data stored by BCP.

Read more about BCP here: http://msdn.microsoft.com/en-us/library/ms162802(v=sql.105).aspx