Backup to Azure WITH CREDENTIAL (deprecated) or SAS Token
Since SQL Server 2012 SP1 CU2 or later and SQL Server 2014 it is possible to use the Microsoft Azure Blob storage service as a backup destination. One of the key compentents in SQL Server that you need is a credential. SQL Server backup and restore processes use this credential to authenticate to the Windows Azure Blob storage service.
For general information about credentials, see Credentials.
Once the credential is created, it must be specified in the WITH clause when issuing the BACKUP/RESTORE statements.
BACKUP DATABASE AdventureWorks2014
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2014.bak'
WITH CREDENTIAL = 'mycredential',COMPRESSION, STATS = 5;
GO
In SQL Server 2016, you still have to use a credential, however, the credential can either store the name of the storage account and the storage account access key values or container URL and its Shared Access Signature token. To create a SAS key, you can use the script that you can find on https://msdn.microsoft.com/en-us/library/dn435916(v=sql.130).aspx#SAS or use the Azure Storage Explorer if you prefer a GUI. Make sure you download version 5 because I’ve noticed that the SAS key generation in version 6 is not working correctly. In this blog post of Julie Koesmarno (t | b) you get a good explanation how to do this. When creating the credential, make sure you consider the following:
- The name of the credential must match the container path
- IDENTITY='SHARED ACCESS SIGNATURE' , this is a mandatory string and cannot be changed
- use the SAS key in the secret clause
There are several methods to create the credential. You could use the following T-SQL example
USE master
GO
CREATE CREDENTIAL 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>' -- this name must match the container path, start with https and must not contain a forward slash.
WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
, SECRET = '<SAS Token>' -- this is the shared access signature key that you obtained in Lesson 1.
GO
or you can use the GUI in the SQL Server Management Studio. Connect to your instance with the Object Explorer, select Security, right click on Credentials and select New Credential.
Once the credential is created, you can issue your BACKUP/RESTORE commands. Please note that the WITH CREDENTIAL clause is not necessary anymore.
BACKUP DATABASE AdventureWorks2016
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak';
GO
Although both WITH CREDENTIAL and SAS key are possible in SQL Server 2016, we recommend to use the SAS token when performing backups to Microsoft Azure Blob storage service. In fact, the WITH CREDENTIAL is deprecated. Currently the documentation is not updated yet, but this will be done quite soon. More information on SQL Server Backup to URL in SQL Server 2016 can be found on https://msdn.microsoft.com/en-us/library/dn435916(v=sql.130).aspx.
Pieter