Something a little different today.

Since SQL Server 2012 SP1 it has been natively supported to run a disk backup of databases and logs directly into Azure Blob storage.

For organisations with on-prem SQL Servers but a desire to improve their backup capability by using the safe, secure Azure platform, this functionality is a dream come true despite the fact that no SSMS support was initially provided.

Fortunately, the PowerShell provider and cmdlets for SQL Server introduced in 2012 make it easy to leverage the capability.

Here’s a quick and dirty way to backup all of your databases in a given instance.

Function New-SMSQLCred { # variables $SecureKey = ConvertTo-SecureString $AZStorageKey -AsPlainText -Force # do the work New-SqlCredential -Name $CredName -Path $SQLInstance -Identity $AZStorageAccount -Secret $SecureKey } Function Backup-SMDatabaseToAzure { # variables $ContainerURL = "https://$AZStorageAccount.blob.core.windows.net/$Container/" # do the work Set-Location $SQLInstance $BackupFile = $ContainerURL + ($db.Name).Replace(" ","_") + (Get-Date -Format "_ddMMyy_HHMMss") + "a.bak" # we're quick and dirty here, so the backup command will overwrite existing backup files - be cautious! Backup-SqlDatabase -Database $db.Name -backupFile $BackupFile -SqlCredential $CredName -CompressionOption On -FormatMedia -Initialize -SkipTapeHeader } # lots of preparatory variables # insert your Azure details below! $AZStorageAccount = "" $AZStorageKey = "" $SQLInstance = "SQLSERVER:\SQL\\" # call the below what you like $CredName = "azure_cred" # this container MUST exist $Container = "sql-backups" # do the work Clear-Host # check for the existence of the credential to access Azure, if it's not present, create it Set-Location $SQLInstance If (!(Get-SqlCredential -Name $CredName)) { New-SMSQLCred } # change context to the databases container, enumerate them and then back them all up Set-Location $SQLInstance\databases $AllDatabases = Get-ChildItem ForEach ($db in $AllDatabases) { Backup-SMDatabaseToAzure }

Why is it quick and dirty? You can tidy it up, you might even learn something… šŸ™‚

Usual caveats apply – think before you execute!

more to follow…