Restore a database backup to an Azure SQL Database Managed Instance

This tutorial 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 varous migration methods, see SQL Server instance migration to Azure SQL Database Managed Instance.

  • Download the Wide World Importers - Standard backup file
  • Create Azure storage account and upload backup file
  • Restore the Wide World Importers database from a backup file

Prerequisites

This tutorial uses as its starting point the resources created in this tutorial: Create an Azure SQL Database Managed Instance.

Download the Wide World Importers - Standard backup file

Use the following steps to download the Wide World Importers - Standard backup file.

Using Internet Explorer, enter https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bak in the URL address box and then, when prompted, click Save to save this file in the Downloads folder.

Log in to the Azure portal

Log in to the Azure portal.

Create Azure storage account and upload backup file

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Locate Storage and then click Storage Account to open the storage account form.

    storage account

  3. Fill out the storage account form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    Deployment model Resource model
    Account kind Blob storage
    Performance Standard or premium Magnetic drives or SSDs
    Replication Locally redundant storage
    **Access tier (default) Cool or hot
    Secure transfer required Disabled
    Subscription Your subscription For details about your subscriptions, see Subscriptions.
    Resource group The resource group that you created earlier
    Location The location that you previously selected
    Virtual networks Disabled
  4. Click Create.

    storage account details

  5. After the storage account deployment succeeds, open your new storage account.

  6. Under Settings, click Shared Access Signature to open the Shared access signature (SAS) form.

    sas form

  7. On the SAS form, modify the default values as desired. Notice that the expiry date/time is, by default, only 8 hours.

  8. Click Generate SAS.

    sas form completed

  9. Copy and save the SAS token and the Blob server SAS URL.

  10. Under Settings, click Containers.

    containers

  11. Click + Container to create a container to hold your backup file.

  12. Fill out the container form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    Public access level Private (no anonymous access)

    container detail

  13. Click OK.

  14. After the container has been created, open the container.

    container

  15. Click Container properties and then copy the URL to the container.

    container URL

  16. Click Upload to open the Upload blob form.

    upload

  17. Browse to your download folder and select the WideWorldIimporters-Standard.bak file.

    upload

  18. Click Upload.

  19. Do not continue until the upload is complete.

    upload complete

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. In SSMS, open a new query window.
  2. Use the following script to create a SAS credential - providing the URL for the storage account container and the SAS key as indicated.

    CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>] 
       WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
       , SECRET = '<shared_access_signature_key_with_removed_first_?_symbol>' 
    

    credential

  3. Use the following script to create check the SAS credential and backup validity - providing the URL for the container with the backup file:

    RESTORE FILELISTONLY FROM URL = 
       'https://<storage_account_name>.blob.core.windows.net/<container>/WideWorldImporters-Standard.bak'
    

    file list

  4. 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://<storage_account_name>.blob.core.windows.net/<container>/WideWorldImporters-Standard.bak'`
    

    restore executing

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

    restore percent complete

  6. When the restore completes, view it in Object Explorer.

    restore complete

Next steps

In this tutorial, you learned to restore a backup of a database stored in Azure blob storage into the Managed Instance using the Wide World Importers - Standard backup file. You learned how to:

  • Download the Wide World Importers - Standard backup file
  • Create Azure storage account and upload backup file
  • Restore the Wide World Importers database from a backup file

Advance to the next tutorial to learn how to migrate SQL Server to Azure SQL Database Managed Instance using DMS.