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.

Prerequisites

This quickstart:

Note

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.

  1. Open SQL Server Management Studio (SSMS) and connect to your Managed Instance.
  2. In SSMS, open a new query window.
  3. 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' 
    

    create credential

    Note

    Always remove the leading ? from generated SAS key.

  4. 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'
    

    file list

  5. 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'
    

    restore

  6. 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')`
    
  7. When the restore completes, view it in Object Explorer.

Next steps