快速入门：使用 SSMS 将数据库还原到 Azure SQL 托管实例Quickstart: Restore a database to Azure SQL Managed Instance with SSMS

• 01/04/2021
• • W

适用于： Azure SQL 托管实例

本快速入门介绍如何使用 SQL Server Management Studio (SSMS) 将某个数据库（Wide World Importers - Standard 备份文件）从 Azure Blob 存储还原到 Azure SQL 托管实例。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.
• 需要安装最新版本的 SSMS。Requires the latest version of SSMS installed.
• 要求使用 SSMS 连接到 SQL 托管实例。Requires using SSMS to connect to SQL Managed Instance. 有关连接方法，请参阅以下快速入门：See these quickstarts on how to connect:
  • 在 SQL 托管实例上启用公共终结点，这是本教程推荐的方法。Enable a public endpoint on SQL Managed Instance - this is the recommended approach for this tutorial.
  • 从 Azure VM 连接到 SQL 托管实例。Connect to SQL Managed Instance from an Azure VM.
  • 配置从本地到 SQL 托管实例的点到站点连接。Configure a point-to-site connection to SQL Managed Instance from on-premises.

从备份文件还原Restore from a backup file

在 SQL Server Management Studio 中，请按照以下步骤将 Wide World Importers 数据库还原到 SQL 托管实例。In SQL Server Management Studio, follow these steps to restore the Wide World Importers database to SQL Managed Instance. 数据库备份文件存储在预配置的 Azure Blob 存储帐户中。The database backup file is stored in a pre-configured Azure Blob storage account.

1. 打开 SSMS 并连接到托管实例。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. 运行以下 SQL 脚本，该脚本使用预配置的存储帐户和 SAS 密钥在托管实例中创建凭据。Run the following SQL script, which uses a pre-configured storage account and SAS key to create a credential in your managed instance.

   重要

   CREDENTIAL 必须与容器路径匹配，以 https 开头，结尾不能包含正斜杠。CREDENTIAL must match the container path, begin with https, and can't contain a trailing forward slash. IDENTITY 必须为 SHARED ACCESS SIGNATURE。IDENTITY must be SHARED ACCESS SIGNATURE. SECRET 必须是共享访问签名令牌，且不能包含前导 ?。SECRET must be the Shared Access Signature token and can't contain a leading ?.

   CREATE CREDENTIAL [https://mitutorials.blob.core.chinacloudapi.cn/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. 若要检查凭据，请运行以下脚本。该脚本使用容器 URL 获取备份文件列表。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.chinacloudapi.cn/databases/WideWorldImporters-Standard.bak'
   

   

5. 运行以下脚本，还原 Wide World Importers 数据库。Run the following script to restore the Wide World Importers database.

   RESTORE DATABASE [Wide World Importers] FROM URL =
     'https://mitutorials.blob.core.chinacloudapi.cn/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. 可以使用 sys.dm_operation_status 视图验证是否已完成数据库还原。You can verify that database restore is completed using the sys.dm_operation_status view.

后续步骤Next steps

• 如果在步骤 5 中，数据库还原终止并显示消息 ID 22003，请创建包含备份校验和的新备份文件，然后再次执行还原。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.
• 若要排查备份到 URL 时出现的问题，请参阅将 SQL Server 备份到 URL 的最佳做法和故障排除。For troubleshooting a backup to a URL, see SQL Server Backup to URL best practices and troubleshooting.
• 有关应用连接选项的概述，请参阅将应用程序连接到 SQL 托管实例。For an overview of app connection options, see Connect your applications to SQL Managed Instance.
• 若要使用偏好的工具或语言进行查询，请参阅快速入门：Azure SQL 数据库连接和查询。To query using your favorite tools or languages, see Quickstarts: Azure SQL Database connect and query.