In this article we show you how to do a SQL backup and restore from an Azure Blob Storage Service. You can achieve this without needing to map any virtual drives, directories, without attaching/detaching or downloading anything. In the code examples below, replace anything within angled brackets '<...>' with the appropriate details.

Setting things up

We are assuming that you have a container set up in Azure Blob Storage Service already. We firstly need to store some authentication credentials in your SQL server.

DECLARE @storageAccount VARCHAR(255);
DECLARE @credential VARCHAR(100);

SET @storageAccount = '&lt;StorageAccountName&gt;';

SET @credential = @storageAccount + 'credential'

IF NOT EXISTS
    (SELECT * FROM sys.credentials 
        WHERE credential_identity = @credential)
        CREATE CREDENTIAL mycredential WITH IDENTITY = @storageAccount
        ,SECRET = '&lt;PrimaryAccessKey&gt;' ;

This will set up a new authentication record in the sys.credentials table.

Performing a full backup

To perform a backup of your database and store it in a blob storage service, you need to know the URI of your storage container. The URI is build from the storage account name, container name, and a filename:

https://storageaccountname.blob.core.windows.net/container/filename.bak

First we set up some variables and then run the BACKUP DATABASE command:

DECLARE @storageAccount VARCHAR(255);
DECLARE @container VARCHAR(50);
DECLARE @dbname VARCHAR(50);
DECLARE @credential VARCHAR(100);    
DECLARE @filename VARCHAR(255);

SET @storageAccount = '&lt;StorageAccountName&gt;';
SET @container = '&lt;ContainerName&gt;';
SET @dbname = '&lt;MyDatabaseName&gt;';

SET @credential = @storageAccount + 'credential'    
SET @filename = 'https://' + @storageAccount + '.blob.core.windows.net/' + @container + '/' + @dbname + '_FULL.bak';

BACKUP DATABASE @dbname 
    TO URL = @filename 
    WITH CREDENTIAL = @credential, COMPRESSION, STATS = 5;
GO 

Full backup with transaction logs

The above will do a full backup of your database. To perform a full backup and log backup, you first need to ensure the database is set to use the full recovery mode:

USE master;
GO
ALTER DATABASE &lt;MyDatabaseName&gt;
    SET RECOVERY FULL;
GO

You can then set some variables for the filename. Once you've set up these variables you can backup the database and logfile using a similar script to before. You may want to modify the filename/logname variables to include a date/time.

DECLARE @storageAccount VARCHAR(255);
DECLARE @container VARCHAR(50);
DECLARE @dbname VARCHAR(50);
DECLARE @credential VARCHAR(100);
DECLARE @filename VARCHAR(255);
DECLARE @logname VARCHAR(255);

SET @storageAccount = '&lt;StorageAccountName&gt;';
SET @container = '&lt;ContainerName&gt;';
SET @dbname = '&lt;MyDatabaseName&gt;';

SET @credential = @storageAccount + 'credential'
SET @filename = 'https://' + @storageAccount + '.blob.core.windows.net/' + @container + '/' + @dbname + '_FULL.bak';
SET @logname = 'https://' + @storageAccount + '.blob.core.windows.net/' + @container + '/' + @dbname + '_LOG.trn';

BACKUP DATABASE @dbname
    TO URL =  @filename
    WITH CREDENTIAL = @credential, COMPRESSION
GO

BACKUP DATABASE @dbname
    TO URL =  @logname
    WITH CREDENTIAL = @credential, COMPRESSION
GO

Restoring from a backup

Restoring a database is simple, except you need to first know the location to where you want to restore your database files to.

DECLARE @storageAccount VARCHAR(255);
DECLARE @container VARCHAR(50);
DECLARE @dbname VARCHAR(50);
DECLARE @credential VARCHAR(100);
DECLARE @filename VARCHAR(255);

SET @storageAccount = '&lt;storage account name&gt;';
SET @container = '&lt;ContainerName&gt;';
SET @dbname = '&lt;MyDatabaseName&gt;'; 

SET @credential = @storageAccount + 'credential'
SET @filename = 'https://' + @storageAccount + '.blob.core.windows.net/' + @container + '/' + @dbname + '_FULL.bak';

RESTORE DATABASE @dbname 
    FROM URL = @filename
    WITH CREDENTIAL = @credential
        ,MOVE @dbname + '_data' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\' + @dbname + '.mdf'
        ,MOVE @dbname + '_log' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\' + @dbname + '.ldf'
        ,STATS = 5 

The easy way out

Microsoft have released a tool which you can use to more easily backup your SQL database to Windows Azure Blob Storage. But, if you're more hardcore, do it the proper way and code it all by hand!