Requirement is to backup on-premises database on Windows Azure Storage. To perform this task you need the below
- Windows Azure subscription
- Storage Account (Login to Azure Portal –> +New –> Browse Data + Storage –> Storage)
- Click on Home and browse the .
- 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.
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;