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:
- Set up a container in the cloud to hold our backups
- 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
- 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:
|
1 2 3 4 |
CREATE CREDENTIAL [https://MYSTORAGEACCOUNTNAME.blob.core.windows.net/MYCONTAINERNAME] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'MYBIGLONGKEYSTRINGFROMTHEAZUREPORTAL'; GO |
The portions you have to change in there are:
- MYSTORAGEACCOUNTNAME
- MYCONTAINERNAME
- MYBIGLONGKEYSTRINGFROMTHEAZUREPORTAL
Then, to restore the backup, the syntax is a little different too:
|
1 2 |
RESTORE DATABASE MYDATABASENAME FROM URL = 'https://MYSTORAGEACCOUNTNAME.blob.core.windows.net/MYCONTAINERNAME/MYBACKUPNAME.bak' |
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:
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:
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:
And even system databases get new names – master is replicatedmaster, for example:
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:
|
1 2 3 |
BACKUP DATABASE MySoul TO URL = 'https://mountchimborazo.blob.core.windows.net/sqlbackup/MySoul.bak' WITH COPY_ONLY; |
(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….
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:
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?
20 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
Glenn – you’re welcome! Yeah, check these out:
https://blogs.msdn.microsoft.com/sqlcat/2018/05/04/cpu-and-memory-allocation-on-azure-sql-database-managed-instance/
https://www.brentozar.com/archive/2018/03/azure-sql-managed-instances-brain-dump-on-cpu-memory-ha-dr-and-backups/
sys.dm_os_job_object. I have you now…
Thanks Brent.
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
Charan – for support, try a Q&A site like https://dba.stackexchange.com.
Thanks Brent..
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
No, for questions, your best bet is a QA site like https://Dba.stackexchange.com.
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 🙂
Ended Up this this error. Help
Msg 1133, Level 16, State 1, Line 1
The managed instance has reached its storage limit.The storage usage for the managed instance cannot exceed 360448 MBs. Target database size 326222, current database size 24, used storage space 308207
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Suraj – that error means that the managed instance has reached its storage limit. What specific question did you have about that?
Added some more space and restore was successful. Thank You
We are taking backup file from Microsoft SQL azure Managed instance(v-12.0.2000.8) to the blob container and in that blob .bak file was updating here on daily basis, that .bak file is not able to restored in on premise sql server.
We have received an error what you have posted as screen image: – version incompatible with the server.
Is there have possibilities for restore .bak from azure MI to on premise sql server?
No.
Why do you think SQL MIs still don’t support restoring of differential backups? Feels like something Microsoft should work on as a feature pretty soon.
Have you contacted them to ask for it? I haven’t heard any customers asking for it.