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.
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;