从 Microsoft Azure 中存储的备份还原Restoring From Backups Stored in Microsoft Azure

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

本主题概述在使用存储在 Azure Blob 存储服务中的备份还原数据库时的注意事项。This topic outlines the considerations when restoring a database using a backup stored in the Azure Blob storage service. 这适用于使用“SQL Server 备份到 URL”备份或由 Microsoft Azure 的 SQL Server 托管备份SQL Server Managed Backup to Microsoft Azure创建的备份。This applies to backups created either by using SQL Server Backup to URL backup or by Microsoft Azure 的 SQL Server 托管备份SQL Server Managed Backup to Microsoft Azure.

如果在 Azure Blob 存储服务中存储了要还原的备份,则我们建议查看本主题,然后查看介绍关于如何还原数据库的步骤的主题,本地和 Azure 备份的还原步骤相同。We recommend reviewing this topic if you have backups stored in the Azure Blob storage service that you plan to restore, and then review the topics that describe the steps on how to restore a database which is the same for both on-premises and azure backups.

概述Overview

用于从本地备份还原数据库的工具和方法也适用于从云备份还原数据库。The tools and methods that are used to restore a database from an on-premises backup apply to restoring a database from a cloud backup. 以下各节介绍了这些注意事项,以及在使用 Azure Blob 存储服务存储的备份时应当了解的所有差异。The following sections describe these considerations and any differences you should know about when you use backups stored in the Azure Blob storage service.

“使用 Transact-SQL”Using Transact-SQL

  • 因为 SQL Server 必须连接外部源才能检索备份文件,所以会使用 SQL 凭据来对存储帐户进行身份验证。Since SQL Server must connect to an external source to retrieve the backup files, SQL Credential is used to authenticate to the storage account. 因此,RESTORE 语句需要有 WITH CREDENTIAL 选项。Consequently, the RESTORE statement requires WITH CREDENTIAL option. 有关详细信息,请参阅使用 Microsoft Azure Blob 存储服务执行 SQL Server 备份和还原For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

  • 如果你正在使用 Microsoft Azure 的 SQL Server 托管备份SQL Server Managed Backup to Microsoft Azure 管理备份到云中的备份,则可以通过使用 smart_admin.fn_available_backups 系统函数,查看存储中的所有可用备份。If you are using the Microsoft Azure 的 SQL Server 托管备份SQL Server Managed Backup to Microsoft Azure to manage your backups to the cloud, you can review all the available backups in the storage, by using the smart_admin.fn_available_backups system function. 此系统函数会在一个表中返回数据库的所有可用备份。This system function returns all the available backups for a database in a table. 因为结果以表的形式返回,所以您可以对结果进行筛选或排序。As the results are returned in a table, you can filter or sort the results. 有关详细信息,请参阅 managed_backup.fn_available_backups (Transact-SQL)For more information, see managed_backup.fn_available_backups (Transact-SQL).

使用 SQL Server Management StudioUsing SQL Server Management Studio

  • 还原任务用于使用 SQL Server Management Studio 还原数据库。The restore task is used to restore a database using the SQL Server Management Studio. 备份介质页现在包含“URL”选项,可以显示存储在 Azure Blob 存储服务中的备份文件。The backup media page now includes the URL option to show backup files stored in the Azure Blob storage service. 您还必须提供用于对存储帐户进行身份验证的 SQL 凭据。You also must provide the SQL Credential that is used to authenticate to the storage account. 随后,使用 Azure Blob 存储中的可用备份填充“要还原的备份集”网格。The Backup sets to restore grid is then populated with the available backups in the Azure Blob storage. 有关详细信息,请参阅使用 SQL Server Management Studio 从 Azure 存储还原For more information, see Restoring from Azure storage Using SQL Server Management Studio.

优化还原Optimizing Restores

要减小还原写入时间,请将 “执行卷维护任务” 用户权限添加到 SQL Server 用户帐户。To reduce restore write time, Add perform volume maintenance tasks user right to the SQL Server user account. 有关详细信息,请参阅 数据库文件初始化For more information, see Database File Initialization. 如果在开启即时文件初始化后还原操作仍很慢,请查看执行数据库备份的实例的日志文件的大小。If restore is still slow with instant file initialization turned on, look at the size of the log file on the instance where the database was backed up. 如果日志非常大(好几 GB),则还原操作慢是正常的。If the log is very large in size (multiple GBs), it would be expected that restore would be slow. 在还原过程中,必须清空日志文件,这需要花费相当多的时间。During restore the log file must be zeroed which takes a significant amount of time.

要减少还原次数,建议使用压缩的备份。To reduce restore times it is recommended that you use compressed backups. 对于大小超过 25 GB 的备份,请使用 AzCopy 实用工具 下载到本地驱动器,然后执行还原。For backup sizes exceeding 25 GB, use AzCopy utility to download to the local drive and then perform the restore. 有关其他备份最佳实践和建议,请参阅 SQL Server Backup to URL Best Practices and TroubleshootingFor other backup best practices and recommendations, see SQL Server Backup to URL Best Practices and Troubleshooting.

在执行还原操作时,也可开启跟踪标志 3051,以生成详细日志。You can also turn on Trace Flag 3051 when doing the restore to generate a detailed log. 此日志文件放置在日志目录中,以下面的格式命名:BackupToUrl-<instancename>-<dbname>-action-<PID>.log。This log file is placed in the log directory, and is named using the format: BackupToUrl-<instancename>-<dbname>-action-<PID>.log. 日志文件包含每次往返 Azure 存储的相关信息(包括计时),这在诊断问题时可能非常有用。The log file includes information about each round trip to Azure Storage including timing that can be helpful in diagnosing the issue.

关于执行还原操作的主题Topics on Performing Restore Operations