Restoring SQL 2014 Managed Backups to another SQL server

Background

Managed or “Smart” backup is a new offering for SQL Server starting in 2014 where you setup a storage device in Azure, setup credentials to that storage device, and then turn it on for the entire server. From that point forward any new database created on the the server, with Full recovery, will do the full and incremental backups, based on the database needs, automatically to your Azure storage. I’m guessing, but this must have been something they built for the SQL Databases product in Azure that they added to SQL Server. SQL databases in Azure allows you to create a stand alone database without an actual SQL Server. Microsoft Azure takes care of the rest, including backups. It is pretty straight forward to setup up a Managed Backup on SQL Server, and you can find more information here SQL Server Managed Backup to Microsoft Azure.

Problem

I had setup a managed backup for our production SQL server running 2014 in Azure. I needed to be able to restore those databases from our production environment to our development environment on premises.

My Solution

There are a few different ways to approach this. You can get to the files on Azure through C# and PowerShell, but there is a lot of data needed to put the backups back together. The data needed to restore the databases is stored in the server that did the backup in the msdb database. In my case this is the production server.

Step 1

My production server and development server are both running on the same domain. So step one is to create a linked server on my development server to my production server. I run the linked server with a SQL user that has read rights to the msdb database.

Step 2

Next, you need to create a SQL Credential that will allow you to connect to the Azure Storage. To do this open the Azure Portal and navigate to the Azure storage that holds you backups. From there you will need to click on All settings and then click on Keys. Under Keys will be a Primary and Secondary Key for the storage. Copy one of the keys, and run this SQL:

CREATE CREDENTIAL BackupStorageCredential WITH IDENTITY= 'YourStorageAccount'
, SECRET = '<storage account access key>'

Step 3

Now we have a connection to the Database with our linked server. This will give us our file names. Next we have our Storage credentials that will allow us to open our File Store on Azure. Our last step is to write a stored proc that will restore the first full database file, and then apply the incremental backups to the database to get our full restore. The log files and how they are arranged is somewhat complex. If you want more info it can be found here.

The Code

You can find the Stored proc on my GitHub account here

Other Considerations

I think SQL Server 2016 will have some code to help with restoring databases that are in the cloud, but for now this seems to do the trick!