Quickstart: Restore a database to a Managed Instance
In this quickstart, you'll use SQL Server Management Studio (SSMS) to restore a database (the Wide World Importers - Standard backup file) from Azure Blob storage into an Azure SQL Database Managed Instance.
For more information on migration using the Azure Database Migration Service (DMS), see Managed Instance migration using DMS. For more information on various migration methods, see SQL Server instance migration to Azure SQL Database Managed Instance.
- Uses resources from the Create a Managed Instance quickstart.
- Requires your computer have the latest SQL Server Management Studio installed.
- Requires using SSMS to connect to your Managed Instance. See these quickstarts on how to connect:
- Enable public endpoint on Managed Instance - this is recommended approach for this tutorial.
- Connect to an Azure SQL Database Managed Instance from an Azure VM
- Configure a point-to-site connection to an Azure SQL Database Managed Instance from on-premises.
Restore the database from a backup file
In SSMS, follow these steps to restore the Wide World Importers database to your Managed Instance. The database backup file is stored in a pre-configured Azure Blob storage account.
Open SMSS and connect to your Managed Instance.
From the left-hand menu, right-click your Managed Instance and select New Query to open a new query window.
Run the following SQL script, which uses a pre-configured storage account and SAS key to create a credential in your Managed Instance.
CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/databases] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'sv=2017-11-09&ss=bfqt&srt=sco&sp=rwdlacup&se=2028-09-06T02:52:55Z&st=2018-09-04T18:52:55Z&spr=https&sig=WOTiM%2FS4GVF%2FEEs9DGQR9Im0W%2BwndxW2CQ7%2B5fHd7Is%3D'
To check your credential, run the following script, which uses a container URL to get a backup file list.
RESTORE FILELISTONLY FROM URL = 'https://mitutorials.blob.core.windows.net/databases/WideWorldImporters-Standard.bak'
Run the following script to restore the Wide World Importers database.
RESTORE DATABASE [Wide World Importers] FROM URL = 'https://mitutorials.blob.core.windows.net/databases/WideWorldImporters-Standard.bak'
Run the following script to track your restore's status.
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
When the restore completes, view the database in Object Explorer. You can verify that database restore is completed using sys.dm_operation_status view.
Database restore operation is asynchronous and retriable. You might get some error is SQL Server Management Studio if connection breaks or some time-out expires. Azure SQL Database will keep trying to restore database in the background, and you can track the progress of restore using the sys.dm_exec_requests and sys.dm_operation_status views.
In some phases of restore process you will see unique identifier instead of actual database name in the system views. Learn about
RESTORE statement behavior differences here.
- For troubleshooting a backup to a URL, see SQL Server Backup to URL Best Practices and Troubleshooting.
- For an overview of app connection options, see Connect your applications to Managed Instance.
- To query using your favorite tools or languages, see Quickstarts: Azure SQL Database Connect and Query.