Resource Manager Powershell Script to Enable SQL Server Managed Backup to Azure

The information in SQL Server Books Online about how to generate the Shared Access Signature key for SQL Server Managed Backup to Azure is (at the moment) out-of-date and also not very helpful.

https://msdn.microsoft.com/en-us/library/dn449491.aspx

So here's a Powershell script that creates the SAS key, and also will create the Storage Account and Container if they don't exist.

After creating the SAS key, this script will output a TSQL script to configure managed backup to Azure.

 
$subscriptionName = "My Azure Subscription"
$resourceGroupName = "SQLBackup"
$storageAcctName = "mysqlmanagedbackup"
$containerName= "backups"
$location = "centralus"
$storageSkuName = "Standard_LRS"

Login-AzureRmAccount -SubscriptionName $subscriptionName

$rg = Find-AzureRmResourceGroup | ? Name -eq $resourceGroupName
if ($rg -eq $null)
{
  "Creating Resource Group $resourceGroupName"
  $rg = New-AzureRmResourceGroup $resourceGroupName -Location $location 
}

$sa = Find-AzureRmResource -Name $storageAcctName -ResourceGroupName $resourceGroupName -ResourceType "Microsoft.Storage/storageAccounts"
if ($sa -eq $null)
{
   "Creating Storage Account $storageAcctName"
   $sa = New-AzureRmStorageAccount -ResourceGroupName $resourceGroupName  -Name $storageAcctName -Location $location -SkuName $storageSkuName
}

$storageKey = (Get-AzureRmStorageAccountKey -Name $storageAcctName -ResourceGroupName $resourceGroupName )[0].Value
$context = New-AzureStorageContext -StorageAccountName $storageAcctName -StorageAccountKey $storageKey


if ((get-azurestoragecontainer  -context $context | ? Name -eq $containerName) -eq $null)
{
    Write-Host "Creating New Storage Container  $containerName" 
    new-azurestoragecontainer -name $containerName -permission container -context $context
}

$fullSasToken = New-AzureStorageContainerSASToken -Name $containerName -Permission rwdl -FullUri -Context $context  
$containerUrl = $fullSasToken.Substring(0,$fullSasToken.IndexOf("?"))
$sasToken = $fullSasToken.Substring($fullSasToken.IndexOf("?")+1)


$enableManagedBackupScript = @"
--------------------
---BEGIN TSQL Script
--------------------
CREATE CREDENTIAL [$containerUrl] 
WITH IDENTITY = 'Shared Access Signature', 
     SECRET = '$sasToken' 

GO

EXEC msdb.managed_backup.sp_backup_config_basic   
 @enable_backup = 1,   
 @database_name = null,  
 @container_url = '$containerUrl',   
 @retention_days = 30
   
 --------------------
 ---END TSQL Script
 --------------------
"@


write-host $enableManagedBackupScript