Restore a database backup to an Azure SQL Database Managed Instance
This quickstart demonstrates how to restore a backup of a database stored in Azure blob storage into the Managed Instance using the Wide World Importers - Standard backup file. This method requires some downtime.
For a tutorial using the Azure Database Migration Service (DMS) for migration, see Managed Instance migration using DMS. For a discussion of the various migration methods, see SQL Server instance migration to Azure SQL Database Managed Instance.
- Uses as its starting point the resources created in this quickstart: Create a Managed Instance.
- Requires the newest version of SQL Server Management Studio on your on-premises client computer
- Requires connectivity to your Managed Instance using SQL Server Management Studio. See these quickstarts for connectivity options:
- Uses a preconfigured Azure blob storage account containing the Wide World Importers - Standard backup file (downloaded from https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bak).
For more information about backing up and restoring a SQL Server database using Azure blob storage and a Shared Access Signature (SAS), see SQL Server Backup to URL.
Restore the Wide World Importers database from a backup file
With SSMS, use the following steps to restore the Wide World Importers database to your Managed Instance from the backup file.
- Open SQL Server Management Studio (SSMS) and connect to your Managed Instance.
- In SSMS, open a new query window.
Use the following script to create a credential in the Managed Instance using the preconfigured storage account and SAS key.
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'
Always remove the leading ? from generated SAS key.
Use the following script to check the SAS credential and backup validity - providing the URL for the container with the backup file:
RESTORE FILELISTONLY FROM URL = 'https://mitutorials.blob.core.windows.net/databases/WideWorldImporters-Standard.bak'
Use the following script to restore the Wide World Importers database from a backup file - providing the URL for the container with the backup file:
RESTORE DATABASE [Wide World Importers] FROM URL = 'https://mitutorials.blob.core.windows.net/databases/WideWorldImporters-Standard.bak'
To track the status of your restore, run the following query in a new query session:
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 it in Object Explorer.
- For troubleshooting backup to URL, see SQL Server Backup to URL Best Practices and Troubleshooting.
- For an overview of the connection options for applications, see Connect your applications to Managed Instance.
- To query using one of your favorite tools or languages, see connect and query.