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 from Azure Blob Storage to Azure SQL Managed Instance. The quickstart restores the Wide World Importers sample database from a publicly available backup file using a SAS.

Prerequisites

This quickstart:

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

Use the restore wizard to restore from a backup file

In SSMS, take the steps in the following sections to restore the Wide World Importers database to SQL Managed Instance by using the restore wizard. The database backup file is stored in a preconfigured Blob Storage account.

Open the restore wizard

1. Open SSMS and connect to your SQL managed instance.

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

   

Select the backup source

1. In the restore wizard, select the ellipsis (...) to select the source of the backup set to restore.

   

2. In Select backup devices, select Add. In Backup media type, URL is the only option that's available because it's the only source type supported. Select OK.

   

3. In Select a Backup File Location, choose from one of three options to provide information about the location of your backup files:

   • Select a preregistered storage container from the Azure storage container list.
   • 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.

   

   If you select Add, proceed to the next section, Browse Azure subscription storage containers. If you use a different method to provide the location of the backup files, skip to Restore the database.

   To restore the Wide World Importers sample database from publicly accessible read-only storage, provide the value https://mitutorials.blob.core.windows.net/examples/ and for the Shared Access Signature field, provide any value, such as SAS.

   

Browse Azure subscription storage containers

1. Before connecting to a Subscription, you need to configure the cloud environment. Please navigate to Tools and select Options, then select AzureChinaCloud in the Azure Cloud option under Azure Services.

   

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

   

3. Sign in to your Azure Account to initiate the session in Azure.

   

4. Select the subscription of the storage account that contains the backup files.

   

5. Select the storage account that contains the backup files.

   

6. Select the blob container that contains the backup files.

   

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

   

Restore the database

Now that you've selected a storage container, you should see the Locate Backup File in Microsoft Azure dialog.

1. In the left pane, expand the folder structure to show the folder that contains the backup files. In the right pane, select all the backup files that are related to the backup set that you're restoring, and then select OK. For example, the following screenshot shows the publicly accessible read-only blob storage containing the WideWorldImporters sample database.

   

   SSMS validates the backup set. This process takes at most a few seconds. The duration depends on the size of the backup set.

2. If the backup is validated, you need to specify a name for the database that's being restored. By default, under Destination, the Database box contains the name of the backup set database. To change the name, enter a new name for Database. Select OK.

   

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

   

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

   

4. In Object Explorer, check for the restored database by selecting Refresh.

   

Use T-SQL to restore from a backup file

As an alternative to the restore wizard, you can use T-SQL statements to restore a database. In SSMS, follow these steps to restore the Wide World Importers database to SQL Managed Instance by using T-SQL. The database backup file is stored in a preconfigured Blob Storage account.

1. Open SSMS and connect to your SQL managed instance.

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

3. Run the following T-SQL statement, which uses publicly available preconfigured storage container and a shared access signature key to create a credential in your SQL 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 ?.
   • In this example, SECRET is omitted because the storage account is publicly available. If you use a storage account that isn't publicly available, you must provide a shared access signature token.

   CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/WideWorldImporters-Standard.bak]
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE';
   

   

   The credential path in the previous example provides access to a single file. You can also create a shared access signature token to a folder path, for example:

   CREATE CREDENTIAL [https://<your storage>.blob.core.chinacloudapi.cn/databases/backups/]
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
   , SECRET = '<your shared access signature>'; -- omit the leading ?
   

4. To check your credential is working properly, run the following statement, which uses a URL to get a backup file list.

   RESTORE FILELISTONLY FROM URL = 'https://mitutorials.blob.core.windows.net/examples/WideWorldImporters-Standard.bak';
   

   

5. Run the following statement to restore the example Wide World Importers database.

   RESTORE DATABASE [WideWorldImportersExample] FROM URL =
     'https://mitutorials.blob.core.windows.net/examples/WideWorldImporters-Standard.bak';
   

   

   Tip

   If you receive Error: 3201 or Operating system error 86(The specified network password is not correct.), it is likely because your SAS credential (created in previous steps) is incorrectly created. DROP CREDENTIAL and recreate, review the credential name, identity, and secret.

6. Run the following statement to track the status of your restore process.

   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 process finishes, view the database in Object Explorer. You can verify that the database is restored by using the sys.dm_operation_status view.

Related content

• For information about 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 by using your favorite tools or languages, see Quickstarts: Azure SQL Database connect and query.