Click here to monitor SSC
  • Av rating:
  • Total votes: 7
  • Total comments: 0
Mike Wood

Automating the Retrieval of SQL Backups from Windows Azure BLOB Storage

14 February 2014

If your database server is in Azure, then it makes sense to do backups into Azure  too. SQL Server 2014  supports backups to the cloud, and particularly well with Managed Backup.  Once your backups are safely in an Azure BLOB, then what? Mike Wood takes up the story.

SQL Server 2014 now provides you new ways to handle backups, especially when it comes to using the cloud as a storage location.  One feature enhancement is the ability to back up and restore using a URL endpoint, which will make storing your backups at various providers much easier.  To build on top of that feature they also offer Managed Backups which coordinates backups in Windows Azure.  You can do this from an on-premises server as well as from SQL Servers running on Windows Azure VMs in the cloud.  It’s a great way to keep a backup stored off site, or in another region for disaster recovery purposes. 

If you use Azure for your URL endpoint, or if you configure the Managed Backup feature, the result is that your backup is saved in Windows Azure BLOB storage (if you are unfamiliar with Windows Azure BLOB storage there is An Introduction to Windows Azure BLOB storage article on Simple Talk). There have been several blog posts and MSDN tutorials on getting a backup like this set up (see the summary below for some links), as well as how to restore these databases, but what if you just want to get a copy of that backup file? 

There are a variety of reasons why you may want to pull this file directly.  For example, the Managed Backup feature has a retention policy you can set, but it has a maximum of 30 days at the time this article was written.  You may need your backups for longer.  Another example would be to pull a copy as part of a continuous dev/test integration workflow.  Finally, you may wish to have a backup of the files locally for auditing or compliance reasons, or simply just another aspect of your disaster recovery plans.

No matter what the reason you need the file, rest assured there are multiple ways to retrieve it. 

Using Tools

When the SQL Server completes the backup operation the resulting file is stored in Windows Azure BLOB storage.  This means that we could get at that file with any of the tools useful for working with BLOB storage.  Below I’m using the free Cerebrata Azure Explorer to view a backup file. 

If you need to manage the backup files, or really any set of files, in your storage account a BLOB storage tool like this will make your life a lot easier, and almost all of them will let you download the files.  A tool like this works quite well if you need to browse your backups, sort out a specific problem, or deal with one off operations against a file; however, if you were going to be automating the regular retrieval of these backups then you’ll want to look at a scripting solution. 

Using PowerShell

Once a backup file is in BLOB storage it is really no different than any other file stored there.  We can use PowerShell to automate the process of pulling down our recent backups.  To get started you’ll want to install the Windows Azure PowerShell Cmdlets, which you can do using the Web Platform Installer. 

Once the Windows Azure PowerShell Cmdlets are installed the following script can be used to retrieve files from BLOB storage:

$accountName = "<name of your Storage Account>"

$sourceContainer = "sqlbackup"

$localDestination = "c:\temp\localbackup"

$keyfilePath = "c:\temp\storageKeyFile.txt"

$securedStorageKey = Get-Content $keyfilePath | ConvertTo-SecureString

$accountKey = [Runtime.InteropServices.Marshal]::PtrToStringAuto ([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securedStorageKey));

$accountContext = New-AzureStorageContext -StorageAccountName $accountName -StorageAccountKey $accountKey

Get-AzureStorageBlob -Container $sourceContainer -Context $accountContext | `

   Where-Object { $_.LastModified -gt (Get-Date).Date.AddDays(-1) } | `

   Get-AzureStorageBlobContent -Destination $localDestination -Force

To execute this script update the variables at the top with your own values.  Use the same storage account name you used to set up your Managed Backup or credential in SQL Server.  Make sure to replace the angle brackets as well.  Provide the source container name of where the backup files live and an existing local destination you wish to pull the file to.   Note that the storageKeyFile.txt is storing the actual storage account key, but it does so in an encrypted form.  We’ll cover how to generate that file later in the article.

The script creates a Storage Context object using the account name and key, which is used to indicate what storage account we wish to access and to authenticate our requests.  The next step is we look at all BLOBs within the source container using the Get-AzureStorageBlob cmdlet which pulls back the list of BLOBs along with a little information about each.  This script then filters down that list to only look at those BLOBs that have a last modified time of greater than yesterday.  Finally that list is piped along to the Get-AzureStorageBlobContent cmdlet which will actually download the file.  The Force switch is used to overwrite existing files if they already exist.

The filtering by date is somewhat arbitrary, and in truth may result in missed files if the script fails to run one day.  It is better to either keep track of the last time the script run and use that specific date in the comparison, or determine the best way to decide which files you want to pull for your scenario. 

Now that the files are local you then incorporate them into any process you wish.

A Comment about Security

The script sample above stored the account key, which is a secret, into a key file.  If someone were to get a hold of the key they could do just about anything they wanted to with the storage account shy of deleting the account itself.  Obviously, just storing that key in a file doesn’t secure it, but the contents of that file are actually encrypted.

To generate the key file log in to the machine that will be executing this script and log in as the user account which will be performing the automated task, i.e., the one which will execute the scheduled task.   Run the following PowerShell and provide the account storage key when prompted.

$securedStorageKey = Read-Host -Prompt "Enter Storage Key" -AsSecureString

ConvertFrom-SecureString $securedStorageKey | Set-Content c:\temp\storageKeyFile.txt

The account key is accepted as a SecureString object and then written out to a key file using the ConvertFrom-SecureString Cmdlet.  Without a –Key parameter provided to this CmdLet, the Windows Data Protection API is used which means the string cannot be decrypted unless done so by the same user on the same machine.  By using this approach we do not need to embed the account key into the script.  If anyone got a hold of the script they would also need the key file and be able to execute the script as the account which originally encoded the key file.    It can be said that if someone already was able to access files owned by another user and execute operations as that user your ability to stop them is actually very limited.

Just like any script that contains secrets or needs to be secure, be aware of what is contained within the script and what could be done with that script if it were to be found by someone else. 

Summary

To summarize, if you back up your SQL Server database to Windows Azure BLOB storage you  have many options on managing those files, including the ability automate the retrieval of the files using PowerShell. 

To learn a little more about managed backups you can read Grant Fritchey’s blog post on How to Set up Managed Backups in SQL Server 2014, as well as the MSDN Documentation. 

Mike Wood

Author profile:

Michael Wood is a Technical Evangelist for Cerebrata working with Windows Azure tools and services. He describes himself as a problem solving, outdoorsy, user group advising, dog-loving, blog writing, solution creating, event planning, married, technology speaking, father of one kind of guy. When he's not living up to that title he's an avid reader, (horrible) violinist and gamer. Michael is one of the Founding Directors of the Cincinnati .NET User Group as well as the founder of the Cincinnati Software Architecture Special Interest Group. Michael has been awarded a Microsoft MVP in Windows Azure for his contributions to educating the community on the cloud platform. You can catch up with Michael on his blog at http://mvwood.com/blog and on twitter under the handle @mikewo.

Search for other articles by Mike Wood

Rate this article:   Avg rating: from a total of 7 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Top Rated

Data Science Laboratory System – Object-Oriented Databases
 Object-Oriented Databases (OOD) avoid the object-relational impedence mismatch altogether by tightly... Read more...

Tales from a Cloud Software Firm
 Following on from a discussion about how people are using the cloud, the Simple-Talk Editorial Team sat... Read more...

Data Science Laboratory System – Document Store Databases
 A Document Store Database (DSD) is similar to a Relational Database Management system with the... Read more...

Data Science Laboratory System - Instrumentation
 It is sensible to check the performance of different solutions to data analysis in 'lab' conditions.... Read more...

Testing the StreamInsight Service for Windows Azure
 Getting 'up to speed' with StreamInsight is easier if you take the time to run it and test it out.... Read more...

Most Viewed

Windows Azure Virtual Machine: A look at Windows Azure IaaS Offerings (Part 2)
 We continue our introduction of the Azure IaaS by discussing how images and disks are used in the Azure... Read more...

PHPFog and Pagoda Box: A Look at PHP Platforms
 Cloud platforms such as Heroku, AppEngine, PHPFog and Pagoda Box are ideal for companies who just want... Read more...

An Introduction to Windows Azure BLOB Storage
 Azure BLOB storage is persistent Cloud data storage that serves a variety of purposes. Mike Wood shows... Read more...

Managing session state in Windows Azure: What are the options?
 Because you can't maintain session state for ASP.NET applications in Azure using the default in-process... Read more...

Creating a custom Login page for federated authentication with Windows Azure ACS
 Windows Azure Acess Control Service (ACS) provides a way of authenticating users who need to access web... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.