Quickstart: Restore a database to Azure SQL Managed Instance with SSMS

APPLIES TO: Azure SQL 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 to Azure SQL Managed Instance.

Prerequisites

This quickstart:

• Uses resources from the Create a managed instance quickstart.
• Requires the latest version of SSMS installed.
• Requires using SSMS to connect to SQL Managed Instance. See these quickstarts on how to connect:
  • Enable a public endpoint on SQL Managed Instance - this is the recommended approach for this tutorial.
  • Connect to SQL Managed Instance from an Azure VM.
  • Configure a point-to-site connection to SQL Managed Instance from on-premises.

Restore from a backup file using the restore wizard

In SSMS, follow these steps to restore the Wide World Importers database to SQL Managed Instance by using the restore wizard. The database backup file is stored in a pre-configured Azure Blob Storage account.

1. Open SSMS and connect to your managed instance.

2. In Object Explorer, right-click the databases of your managed instance and select Restore Database to open the restore wizard.

   

3. In the new restore wizard, select the ellipsis (...) to select the source of the backup file to use.

   

4. In Select backup devices, select Add. In Backup media type, URL is the only option because it is the only source type supported. Select OK.

   

5. In Select a Backup File Location, you can choose from three options to provide information about backup files are located:

   • Select a pre-registered storage container from the dropdown.
   • Enter a new storage container and a shared access signature. (A new SQL credential will be registered for you.)
   • Select Add to browse more storage containers from your Azure subscription.

   

   Complete the next steps if you select the Add button. If you use a different method to provide the backup file location, go to step 12.

6. In Connect to a Microsoft Subscription, select Sign in to sign in to your Azure subscription:

   

7. Sign in to your Microsoft Account to initiate the session in Azure:

   

8. Select the subscription where the storage account with the backup files is located:

   

9. Select the storage account where the backup files are located:

   

10. Select the blob container where the backup files are located:

    

11. Specify the expiration date of the shared access policy and select Create Credential. A shared access signature with the correct permissions is created. Select OK.

    

12. In the left pane, expand the folder structure to show the folder where the backup files are located. Select all the backup files that are related to the backup set to be restored, and then select OK:

    

    SSMS validates the backup set. The process takes up to a few seconds depending on the size of the backup set.

13. If the backup is validated, specify the destination database name or leave the database name of the backup set, and then select OK:

    

    The restore starts. The duration depends on the size of the backup set.

    

14. When the restore finishes, a dialog shows that it was successful. Select OK.

    

15. Check the restored database in Object Explorer:

    

Restore from a backup file using T-SQL

In SQL Server Management Studio, follow these steps to restore the Wide World Importers database to SQL Managed Instance. The database backup file is stored in a pre-configured Azure Blob storage account.

1. Open SSMS and connect to your managed instance.

2. In Object Explorer, right-click your managed instance and select New Query to open a new query window.

3. Run the following SQL script, which uses a pre-configured storage account and SAS key to create a credential in your managed instance.

   Important

   CREDENTIAL must match the container path, begin with https, and can't contain a trailing forward slash. IDENTITY must be SHARED ACCESS SIGNATURE. SECRET must be the Shared Access Signature token and can't contain a leading ?.

   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'
   

   

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

   

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

   

6. Run the following script to track the status of your restore.

   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 the database in Object Explorer. You can verify that database restore is completed using the sys.dm_operation_status view.

Next steps

• If, at step 5, a database restore is terminated with the message ID 22003, create a new backup file containing backup checksums and perform the restore again. See Enable or disable backup checksums during backup or restore.
• 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 SQL Managed Instance.
• To query using your favorite tools or languages, see Quickstarts: Azure SQL Database connect and query.