Using microsoft azure for SQL database backup and restore.

msSQL2014 has a feature to backup local database into azure storage container.

Quick steps:

1. Create a microsoft azure account, login, go to storage, create a storage, inside the storage create a container.
2. Create a credential in SQL server for azure using azure storage account and access key.
3. Backup database to storage url

In detail:

1. Create a azure storage container.

1. Get the azure access key.

Note from above we see the following parameters:( this is for my test installing and surely would be different for yours)

azure url : https://mssql2014bck.blob.core.windows.net/sqlbackup-1
azure storage account name : mssql2014bck
azure access key: Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==

1. create local credential by the following SQL

CREATE CREDENTIAL azure_bckup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

1. using management studio backup database to azure.

the same backup can be taken using SQL as follows

BACKUP DATABASE [azureDB]
TO URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/azuredb.bak’
WITH CREDENTIAL = ‘azure_bckup’;
GO

1. Go to azure interface to check the backup

The database can be downloaded into a local copy. which can be used to restore this database into another server or directly from azure this database can be restored into any SQL database having azure credential and URL.

In any SQL server create azure credential

CREATE CREDENTIAL azure_bckup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

using azure interface the backup file URL can be copied as follows:

then restore from azure by the following:

RESTORE DATABASE azureDB
FROM URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/dbbck.bak/azureDB_backup_2015_01_22_095246.bak’
WITH CREDENTIAL = ‘azure_bckup’;
GO

Once restore is successful you can see the database into management studio.

using azure container for backup and restore for SQL database is a very easy and cheap option to be considered.

However as this backup is unencrypted so security is a major concern and it is highly recommended to encrypet the database and then do the backup.

Also once a full backup is take  then differential backup in periodic interval can assure better point in time recovery as well as less storage utilization alone with less unlink bandwidth usage.

One of the other benefit of using azure storage container is, the backup database in azure can be very easily imported into a SQL server in azure, which can be considered as a fault tolerance idea or disaster recovery mechanism.

If you have backup a encrypted database into azure then dont forget to backup the certificate and key. without the certificate and key, you cant restore the database.

I have done a encrypted database backup to azure using the procedure stated above and I have restored the database in another server using the T-SQL as follows:

1. first create a master key and then then import the certificate into the database server( has to be in master database). I have put the key and certificate in C:\ drive as an arbitrary name AA.

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’;
go
CREATE CERTIFICATE cryptdbcert
FROM FILE = ‘c:\AA.crt’
WITH PRIVATE KEY (FILE = ‘c:\AA.key’,
DECRYPTION BY PASSWORD = ‘V3RyC0mplicated#K3Y’);
GO

then restore database from azure, first copy the azure container URL.

CREATE CREDENTIAL azure_backup
WITH IDENTITY= ‘mssql2014bck’
, SECRET = ‘Hu0Sm0Qd7QhU/eSZL+E1VrpNEh7szJFodTb434YGq2+q7qur+zY7XscWw953CTee29aKKV89v1amgfsT++tHzg==’
GO

RESTORE DATABASE azureDB
FROM URL = ‘https://mssql2014bck.blob.core.windows.net/sqlbackup-1/dbbck.bak/azureDB_backup_2015_01_22_095246.bak’
WITH CREDENTIAL = ‘azure_backup’;
GO

As we can see both non encrypted and encrypted databases can be backup and restore using azure storage container.

Cheers
Jamal Ahmed
jahmed@zoonetix.com