Home > Windows & SQL Server AZURE > MS SQL Server Database Backup to Azure Storage

MS SQL Server Database Backup to Azure Storage

 

image

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)
    image
  3. Click on Home image and browse the image .
  4. Copy the [STORAGE ACCOUNT NAME], [PRIMARY ACCESS KEY]

 

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

 

image

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

image

Once connected you will see the containers .

image

Under containers you need to create your own containers.

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

 

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

image

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

 

image

 

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;

 

image

 

image

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: