BACKUP SQL AZURE 2016 DATABASE TO BLOB STORAGE USING SAS

Starting SQL  Server 2016 we can take backups of SQL Server databases on Azure blob storage. The functionality was missing in SQL Server 2014 and it was only possible to take SQL Backups on Hot storages in Azure storage, which was more costly.

You would require Shared access signatures(SAS) to access Azure blob storage from your SQL script.

Below are the steps that needs to be performed to create a SAS token and take the backup.

1. Powershell script given below gives you a SQL command as an output. We need to change the parameter variables at the start e.g. Subscription name, container. Remember to change the policy name, if you are executing the script in the same storage account twice. This token by default is valid for next 10 years(not practical 😆). Change the duration in script if needed.

<#  

This script uses the Azure Resource model and creates a new ARM storage account. 

Modify this script to use an existing ARM or classic storage account  

using the instructions in comments within this script 

#> 

# Define global variables for the script 

$prefixName = ''  # used as the prefix for the name for various objects 

$subscriptionName='Microsoft Azure'   # the name  of subscription name you will use 

$locationName = 'East US'  # the data center region you will use 

$storageAccountName= $prefixName + 'dbablobspecific' # the storage account name you will create or use 

$containerName= $prefixName + 'container1'  # the storage container name to which you will attach the SAS policy with its SAS token 

$policyName = $prefixName + 'backupSAS' # the name of the SAS policy 

<#  

Using Azure Resource Manager deployment model 

Comment out this entire section and use the classic storage account name to use an existing classic storage account 

#> 

# Set a variable for the name of the resource group you will create or use 

$resourceGroupName=$prefixName + 'rg'  

# adds an authenticated Azure account for use in the session  

Login-AzureRmAccount   

# set the tenant, subscription and environment for use in the rest of  

Set-AzureRmContext -SubscriptionName $subscriptionName  

# create a new resource group - comment out this line to use an existing resource group 

#New-AzureRmResourceGroup -Name $resourceGroupName -Location $locationName  

Get-AzureRmResourceGroup -Name 'dbagroup' -Location 'East US'

# Create a new ARM storage account - comment out this line to use an existing ARM storage account 

#New-AzureRmStorageAccount -Name $storageAccountName -ResourceGroupName $resourceGroupName -Type Standard_RAGRS -Location $locationName  

Get-AzureRmStorageAccount  -Name $storageAccountName

# Get the access keys for the ARM storage account 

$accountKeys = Get-AzureRmStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName 

# Create a new storage account context using an ARM storage account 

$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].Value

<# 

Using the Classic deployment model 

Use the following four lines to use an existing classic storage account 

#> 

#Classic storage account name 

#Add-AzureAccount 

#Select-AzureSubscription -SubscriptionName $subscriptionName #provide an existing classic storage account 

#$accountKeys = Get-AzureStorageKey -StorageAccountName $storageAccountName 

#$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys.Primary 

# The remainder of this script works with either the ARM or classic sections of code above 

# Creates a new container in blob storage 

$container = New-AzureStorageContainer -Context $storageContext -Name $containerName 

$cbc = $container.CloudBlobContainer 

# Sets up a Stored Access Policy and a Shared Access Signature for the new container 

$permissions = $cbc.GetPermissions(); 

$policyName = $policyName 

$policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy' 

$policy.SharedAccessStartTime = $(Get-Date).ToUniversalTime().AddMinutes(-5) 

$policy.SharedAccessExpiryTime = $(Get-Date).ToUniversalTime().AddYears(10) 

$policy.Permissions = "Read,Write,List,Delete" 

$permissions.SharedAccessPolicies.Add($policyName, $policy) 

$cbc.SetPermissions($permissions); 

# Gets the Shared Access Signature for the policy 

$policy = new-object 'Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy' 

$sas = $cbc.GetSharedAccessSignature($policy, $policyName) 

Write-Host 'Shared Access Signature= '$($sas.Substring(1))'' 

# Outputs the Transact SQL to the clipboard and to the screen to create the credential using the Shared Access Signature 

Write-Host 'Credential T-SQL' 

$tSql = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sas.Substring(1)  

$tSql | clip 

Write-Host $tSql 

     2. Upon execution of above script you will receive a TSQL command as an output –

This gives you the shared access signature and a T-SQL  statement which you can execute to create a credential on your SQL server 2016 instance. Copy this statement in a new query window and execute to create the credential –

 Once credential is created, you can start your SQL backups to Azure blob storage using it.

Now go back to your storage account container and verify that backup is completed successfully.

 Hope you find the post helpful.