How to Restore a SQL Server Database into Azure SQL DB Managed Instances

You’ve built your first Managed Instance, and now you wanna play around with real live data. You’re going to need a backup in the cloud to do it – SSMS doesn’t have a drag-and-drop option.

Big picture, here’s what we’re going to do:

  1. Set up a container in the cloud to hold our backups
  2. In our regular SQL Server, set up security so we can back up to that cloud container, and then back up a database to it
  3. In our Managed Instance (MI), set up security to access the cloud container, and then restore the backup from it

For steps 1 & 2, follow Steve Thompson’s excellent checklist, Backup SQL Server to an Azure Storage Account. I’ve tried several other checklists, but as of 2018, this was the only one that worked for me out of the box. (He’s @Steve_TSQL and a nice guy.)

The restore syntax in Managed Instances is a little different.

To create the security credential, the syntax is a little different:

The portions you have to change in there are:

  • MYSTORAGEACCOUNTNAME
  • MYCONTAINERNAME
  • MYBIGLONGKEYSTRINGFROMTHEAZUREPORTAL

Then, to restore the backup, the syntax is a little different too:

The portions you have to change:

  • MYDATABASENAME – the target, new database you’re creating
  • MYSTORAGEACCOUNTNAME
  • MYBACKUPNAME
  • MYCONTAINERNAME

You can also restore databases asynchronously – meaning, fire off the restore so that it runs even when you’re not connected.

Want to check progress? This is about to get technical.

If you don’t care about checking progress, you can bail here. Happy testing.

You can’t use WITH parameters on the restore, which means you don’t get status messages. To check restore progress, use sp_WhoIsActive, which works beautifully:

Restore in progress, part 1

In that screenshot, session_id 143 is me (Doctor) running the restore command, restoring StackOverflow from backup. If I scroll across to the right of sp_WhoIsActive’s output, I can normally check restore progress, but it’s null:

Restore progress, part 2

But it’s null.

Notice how there’s another restore running from 127 at the same time, restoring a guid-based database? With the wacko service name, and program_name RestoreService? Azure Managed Instances are the ones really doing the restore, and you can see the percent_complete is populated for their session. They’re doing more than just restoring locally, too. In the error log, while a restore runs, you’ll also see interesting messages about how Azure is setting up the replication for me to the other nodes:

Seeding the secondaries

And even system databases get new names – master is replicatedmaster, for example:

Replicatedmaster

And they really do mean replicated – as of this writing, you can create tables in master, load them full of data, and get your instance into trouble on allocated disk space. (You get 32GB by default, and master is included in that number.)

Want to go from cloud to on-premises?

You can go in the other direction, too: since both your on-premises SQL Server and Azure SQL Managed Instances can access the same Azure Blob Storage, you can take a copy-only backup in Azure SQL Managed Instance:

(This syntax requires me to have already created the credential as shown at the start of this post, a SHARED ACCESS SIGNATURE credential pointing to this URL.)

Then back on-premises, just do a restore pointing to that file and…awww….

SadTrombone.com

That’s fair – MIs are a newer version of SQL Server than what we have on-premises. I wouldn’t be surprised if we got access to a FeatureSwitch to enable exporting data easier at some point in the future, though – Lord knows there’s enough backup feature switches in sys.dm_hadr_fabric_config_parameters:

DBCC WITHOUTATRACE

But that’s a story for another post, and GroupBy is about to start.

Speaking of feature switches, what trace flags are in use on Managed Instances?

Previous Post
How to Deploy Your First Azure SQL DB Managed Instance Preview
Next Post
Azure SQL DB Managed Instances: Trace Flags, Ahoy!

13 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}