Requirement is to backup on-premises database on Windows Azure Storage. To perform this task you need the below

 

1. Windows Azure subscription
2. Storage Account (Login to Azure Portal –> +New –> Browse Data +  Storage –> Storage)
   
3. Click on Home and browse the .
4. Copy the [STORAGE ACCOUNT NAME], [PRIMARY ACCESS KEY]

 

Open SSMS (SQL Server Management Studio)—> Click on connect—>Select Azure Storage

 

Provide the Storage Account  and Access Key (Requirement 4) then click connect

Once connected you will see the containers .

Under containers you need to create your own containers.

To create the storage container (login to the portal >>  >> >> )

 

Create container window will appear. For security always choose Private.

After the container is created, refresh the SSMS object explorer and container should appear.

 

 

Now we have the [STORAGE ACCOUNT NAME],[PRIMARY ACCESS KEY] , [Containers] and [URL] .Lets configure couple of things at on-premises SQL Server Instance

The first thing you have to do here is create the SQL credential objects necessary to properly access the Azure blob container you
created before. A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. The credential stores the URI path of the storage container and the shared access signature key values. For each storage container used by a data or log file, you must create a SQL Server Credential whose name matches the container path. Please, read it again: the credential name string must be exactly the same as the Blob container path, otherwise SQL and Azure will not match the security information.

CREATE CREDENTIAL mycredential WITH IDENTITY = ‘STORAGE ACCOUNT NAME’
        ,SECRET = ‘PRIMARY ACCESS KEY’;

 

This credential will be used in the backup command to connect to the Azure Storage &  Container

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

SET @storageAccount = ‘dbproxazurestorage’;
SET @container = ‘containerstorage’;
SET @dbname = ‘AdventureWorks2012’;

SET @credential = ‘mycredential’
SET @filename = ‘https://’ + @storageAccount + ‘.blob.core.windows.net/’ + @container + ‘/’ + @dbname + ‘_3_FULL.bak’;

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