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.
- 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
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 –
Now go back to your storage account container and verify that backup is completed successfully.
