IT Bytes

SQL Server 2016 allows you to backup a database to Azure Storage. I found the information about this to be disjoint at best. So, if you want to do an old style (non-snapshot) backup of a database this is how I achieved it;

Background

My Server is running on a Virtual Machine in on the same Azure Resource Group as the storage account, I’m not too concerned about SQL knowing the storage account keys, so I’m going to show the shortest path to getting this working

Steps

Assume we’re backing up a Database called Products.

1. Create Storage Account – Create a ‘standard’ non premium storage account. Don’t be tempted to use premium, it will not work. Let’s call it sqlnoprembackup
2. Create a container in the blob storage, perhaps ‘backupsql1’. Grab the full path to the container, if you’re using the portal select the container and view the properties. In this example it is; ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1’
3. Grab the account name and key for the storage account (Access Keys if you’re using the portal). Here it is sqlnoprembackup and the first long key.
4. Create the certificate on SQL Server, let’s call it backupstorage;

IF NOT EXISTS 
(SELECT * FROM sys.credentials  
WHERE name = ‘backupstorage’) 
CREATE CREDENTIAL [backupstorage] WITH IDENTITY = ‘sqlnoprembackup’ 
,SECRET = ‘<insert secret storage key from (3) here>’; 

5. Backup the database, use the full path to the container from (2). Note stats=5 will display the % progress at 5% internals in the messages tab;

BACKUP DATABASE Products
TO URL = ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1/products.bak’  
WITH CREDENTIAL = ‘backupstorage’  
     ,COMPRESSION 
     ,STATS = 5; 

6. Run it. Yay.

Restoring the database

To do a full restore of the database;

1. Take the database offline;
   ALTER DATABASE Products SET OFFLINE WITH NO_WAIT
2. To restore the database;

RESTORE DATABASE Products FROM URL = ‘https://sqlnoprembackup.blob.core.windows.net/backupsql1/products.bak’  
WITH CREDENTIAL = ‘backupstorage’, REPLACE, STATS=5 

NB, take a look at Snapshot backups, it’s a much better solution. Sometimes, as in my case, it’s not available and this hybrid approach works quite nicely.