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

  • Thanks for that Brent, worked beautifully. We’re now in and testing our databases against their applications, should this product go GA anytime soon… One thing though, on the server properties page I noticed that I have (potentially 😉 80 cores and 442,368MB or RAM! (insert poor T-SQL here). Am I supposed to do anything with the RAM settings for Managed Instance? I can’t seem to find any articles relating to post setup config.

    Cheers,

    G

  • Hi Brent,
    I have tried the steps mentioned in the post to restore the backup(.bak) to Managed Instance but i am getting the below error
    “Msg 3201, Level 16, State 2, Line 7
    Cannot open backup device ‘https://ZZZdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Test.bak’. Operating system error 2(The system cannot find the file specified.).”
    Do you have any idea on what could be the issue.
    Thanks,
    Charan

  • Hi Brent,
    Thanks for the post, very useful as usual. I’ve been searching for similar migration / restore info but from Azure SQL DB Single Database to Azure SQL DB Managed Instance. Do you know what the options are ? As far as I could see none of Database Migration Assistant, Database Migration Service, database copy, supports that path.
    And for a bonus point :-), if you also know how to do the opposite direction …

    Thanks,
    Ferry

  • I have an SQL Server installed on Azure VM. I need to migrate all the components(My Databases, master, model, msdb and tempdb) of this SQL server to Azure SQL Managed Instance. Why I need because I have Job, mail profile, operators etc. I need all of these to be migrated to Azure SQL Managed Instance. Could you please guide me ? Thanks in advance.

    • That’s a little beyond what I can do for free in a blog post comment.

    • Try scripting out the create jobs and create operators. Then, run it in Managed Instance. For the mail profile, sorry only 1 profile is supported in Azure Managed Instance for now. For the databases, use the Azure Migration Assistant. Search it in Azure Market

  • […] NOTE: Notice that there are no WITH clauses in the restore as these are not supported in SQL Azure. Normally you would have a STATS clause to monitor the restore, but this can be done either via sys.dm_exec_requests – precent_complete column or by downloading sp_whoIsActive – check out Brent Ozar’s Post on this […]

  • This can happen if you follow the Steve Thompson’s checklist mentioned at the top of this post and use the generated key1 when creating the credential for restoring the backup. You may not be able to access the backup files. I have fixed this using Microsoft Azure Storage Explorer by connecting to the storage with key1 and generating a new Shared Access Signature for the particular backup file. Then I’ve altered the credential using the query string without the preceding question mark as the secret. Database restored 🙂

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