Backing up SQL Server to Azure storage - doing it the long way round

So, as I can't seem to get the baked-in way of backing up to Azure Blob Storage to play ball, I've decided to do it the long way round for now, which isn't the end of the world as it gives me an opportunity to dig into PowerShell a little bit more anyway!

I'm running this on a server using Windows Server 2012 R2, so there are three things that I've installed on top of that:

• PowerShell 5.1 (this makes the environment consistent with what I've got running on the PC I'm writing the script on)
• The SQLServer PowerShell module
• The Azure.Storage PowerShell module

I've created a SQL Server user specifically for the purpose with this script:

USE [master]
GO
CREATE LOGIN [azurebackup] WITH PASSWORD=N'<PASSWORD_FOR_USER>', DEFAULT_DATABASE=[<DATABASE_TO_BACKUP>]
GO
USE [<DATABASE_TO_BACKUP>]
GO
CREATE USER [azurebackup] FOR LOGIN [azurebackup]
GO
USE [<DATABASE_TO_BACKUP>]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [azurebackup]
GO

This should grant the user just enough privileges to backup the database and not a lot of anything else. Now I've got the user and the tools, I need the script:

Import-Module SqlServer;

$sqlUsername = "azurebackup";
$sqlPassword = "<SQL_USER_PASSWORD>";

$storageAccount = "<STORAGE_ACCOUNT_NAME>";
$storageKey = "<STORAGE_ACCOUNT_KEY>";
$storageContainer = "orchard";

$sqlInstanceName = "<DATABASE_SERVER_NAME>";
$backupWorkingFolder = "C:\BackupWorkingFolder";
$databaseName = "<DATABASE_NAME>";

<# Transform $sqlUsername and $sqlPassword into a credential object that can be used to connect to SQL Server #>
$sqlPasswordSecureString = ConvertTo-SecureString $sqlPassword -AsPlainText -Force
$sqlCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlUsername, $sqlPasswordSecureString

<# Construct a "unique enough" filename for the backup, composed of the instance name (this will need tweaking if the instance is not the "default", i.e. contains a slash),
   the name of the database being backed up and the current date/time down to second precision
#>
$backupFileName = "$($sqlInstanceName)_$($databaseName)_$(Get-Date -Format "yyyyMMddHHmmss").bak";
$backupFileNameAndPath = "$backupWorkingFolder\$backupFileName";

<# Backup the database, connecting with SQL authentication and drop the backup file into the location specified #>
Backup-SqlDatabase -Credential $sqlCredential -ServerInstance $sqlInstanceName -Database $databaseName -BackupFile $backupFileNameAndPath 

<# Authenticate against Azure storage and then write the file to it#>
$storageContext = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey

Set-AzureStorageBlobContent -Container $storageContainer -Blob $backupFileName -File $backupFileNameAndPath -Context $storageContext -Force

<# Delete the backup file #>
Remove-Item -Path $backupFileNameAndPath

It was a relatively simple matter to create a Windows scheduled task that runs daily with an action of "Start a program", program/scipt of "powershell" and  arguments of "C:\BackupWorkingFolder\Backup_To_Azure.ps1", and now I've got the database in question (in fact, the one that backs my Orchard installation!) being backed up to Azure blob storage daily.