从 SQL Server 备份到 URL 的最佳做法和故障排除SQL Server back up to URL best practices and troubleshooting

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

本主题介绍 SQL Server 备份和还原到 Azure Blob 服务的最佳做法和故障排除提示。This topic includes best practices and troubleshooting tips for SQL Server backup and restores to the Azure Blob service.

有关将 Azure Blob 存储服务用于 SQL Server 备份或还原操作的详细信息,请参阅:For more information about using Azure Blob storage service for SQL Server backup or restore operations, see:

管理备份Managing Backups

下表列出了管理备份的一般建议:The following list includes general recommendations to manage backups:

  • 建议为每个备份使用唯一文件名以防止意外覆盖 blob。Unique file name for every backup is recommended to prevent accidentally overwriting the blobs.

  • 创建容器时,建议将访问级别设置为 “私有” ,这样只有可以提供所需的身份验证信息的用户或帐户可以在容器中读取或写入 blob。When creating a container, it is recommended that you set the access level to private, so only users or accounts that can provide the required authentication information can read or write the blobs in the container.

  • 对于在 Azure 虚拟机中运行的 SQL ServerSQL Server 实例上的 SQL ServerSQL Server 数据库,请使用与虚拟机相同区域中的存储帐户,以免产生区域之间的数据传输成本。For SQL ServerSQL Server databases on an instance of SQL ServerSQL Server running in an Azure Virtual Machine, use a storage account in the same region as the virtual machine to avoid data transfer costs between regions. 使用同一区域还可以确保备份和还原操作具有最佳性能。Using the same region also ensures optimal performance for backup and restore operations.

  • 失败的备份活动可能导致无效的备份文件。Failed backup activity can result in an invalid backup file. 我们建议定期标识失败的备份和删除 blob 文件。We recommend periodic identification of failed backups and deleting the blob files. 有关详细信息,请参阅 Deleting Backup Blob Files with Active LeasesFor more information, see Deleting Backup Blob Files with Active Leases

  • 在备份期间使用 WITH COMPRESSION 选项可以最大程度降低存储成本和存储事务成本。Using the WITH COMPRESSION option during backup can minimize your storage costs and storage transaction costs. 它也会减少完成备份过程所需的时间。It can also decrease the time taken to complete the backup process.

  • 按照从 SQL Server 备份到 URL 中的建议,设置 MAXTRANSFERSIZEBLOCKSIZE 参数。Set MAXTRANSFERSIZE and BLOCKSIZE arguments as recommended at SQL Server Backup to URL.

  • SQL Server 对于使用的存储冗余类型不可知。SQL Server is agnostic to the type of storage redundancy used. 每种存储冗余(LRS、ZRS、GRS、RA-GRS、RA-GZRS 等)都支持备份到页 blob 和块 blob。Backup to Page blobs and block blobs is supported for every storage redundancy (LRS\ZRS\GRS\RA-GRS\RA-GZRS\etc.).

处理大型文件Handling Large Files

  • SQL ServerSQL Server 备份操作使用多个线程来优化与 Azure Blob 存储服务的数据传输。The SQL ServerSQL Server backup operation uses multiple threads to optimize data transfer to Azure Blob storage services. 但是性能取决于各种因素,如 ISV 带宽和数据库的大小。However the performance depends on various factors, such as ISV bandwidth and size of the database. 如果您计划从内部 SQL Server 数据库备份大型数据库或文件组,建议您首先执行某些吞吐量测试。If you plan to back up large databases or filegroups from an on-premise SQL Server database, it is recommended that you do some throughput testing first. Azure SLA for Storage 限制了 blob 的最大处理时间,你需要考虑这个限制。Azure SLA for Storage has maximum processing times for blobs that you can take into consideration.

  • 按照管理备份部分的建议使用 WITH COMPRESSION 选项,在备份大型文件时这一点非常重要。Using the WITH COMPRESSION option as recommended in the Managing Backups section, it is very important when backing up large files.

备份到 URL 或从 URL 还原故障排除Troubleshooting Backup To or Restore from URL

以下内容提供了在备份到 Azure Blob 存储服务或从中还原时出现问题的一些快速解决方法。Following are some quick ways to troubleshoot errors when backing up to or restoring from the Azure Blob storage service.

要避免由于不支持的选项或限制导致的错误,请参阅 使用 Microsoft Azure Blob 存储服务进行 SQL Server 备份和还原 一文,查看限制列表以及 BACKUP 和 RESTORE 命令的支持信息。To avoid errors due to unsupported options or limitations, review the list of limitations, and support for BACKUP and RESTORE commands information in the SQL Server Backup and Restore with Microsoft Azure Blob Storage Service article.

初始化失败Initialization failed

并行备份到同一 blob 导致一个备份失败,发生 “初始化失败” 错误。Parallel backups to the same blob cause one of the backups to fail with an Initialization failed error.

如果使用的是页 Blob(例如 BACKUP... TO URL... WITH CREDENTIAL),请使用以下错误日志帮助解决备份错误:If you're using page blobs, for example, BACKUP... TO URL... WITH CREDENTIAL, use the following error logs to help with troubleshooting backup errors:

设置跟踪标志 3051 以启用记录到具有以下格式的特定错误日志:Set trace flag 3051 to turn on logging to a specific error log with the following format in:

BackupToUrl-\<instname>-\<dbname>-action-\<PID>.log 其中,\<action> 为以下值之一:BackupToUrl-\<instname>-\<dbname>-action-\<PID>.log Where \<action> is one of the following:

  • DBDB
  • FILELISTONLYFILELISTONLY
  • LABELONLYLABELONLY
  • HEADERONLYHEADERONLY
  • VERIFYONLYVERIFYONLY

还可以查看 Windows 事件日志(位于应用程序日志之下,名为“SQLBackupToUrl”),查找相关信息。You can also find information by reviewing the Windows Event Log - Under Application logs with the name SQLBackupToUrl.

由于出现 I/O 设备错误,因此无法执行该请求。The request could not be performed because of an I/O device error.

在备份大型数据库时,请考虑 COMPRESSION、MAXTRANSFERSIZE、BLOCKSIZE 和多个 URL 参数。Consider COMPRESSION, MAXTRANSFERSIZE, BLOCKSIZE, and multiple URL arguments when backing up large databases. 请参阅将 VLDB 备份到 Azure Blob 存储See Backing up a VLDB to Azure Blob Storage

错误:The error:

Msg 3202, Level 16, State 1, Line 1
Write on "https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_0.bak" failed: 
1117(The request could not be performed because of an I/O device error.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

示例解析:An example resolution:

BACKUP DATABASE TestDb
TO URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_0.bak',
URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_1.bak',
URL = 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_2.bak'
WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536;  

设备上的消息文件标记未对齐。Message Filemark on device is not aligned.

从压缩备份中还原时,您可能看到以下错误:When restoring from a compressed backup, you might see the following error:

SqlException 3284 occurred. Severity: 16 State: 5  
Message Filemark on device 'https://mystorage.blob.core.windows.net/mycontainer/TestDbBackupSetNumber2_0.bak' is not aligned.
Reissue the Restore statement with the same block size used to create the backupset: '65536' looks like a possible value.  

要解决此错误,请重新发布指定了 BLOCKSIZE = 65536 的 RESTORE 语句 。To solve this error, reissue the RESTORE statement with BLOCKSIZE = 65536 specified.

失败的备份活动可能导致 blob 产生活动租约。Failed backup activity can result in blobs with active leases.

由于 blob 具有活动租约,备份期间出错:Failed backup activity can result in blobs with active leases.Error during backup due to blobs that have active lease on them: Failed backup activity can result in blobs with active leases.

如果重新尝试执行备份语句,备份操作可能失败,出现类似于以下的错误:If a backup statement is reattempted, backup operation might fail with an error similar to the following:

Backup to URL received an exception from the remote endpoint. Exception Message: 
The remote server returned an error: (412) There is currently a lease on the blob and no lease ID was specified in the request. 

如果尝试对具有活动租约的备份 blob 文件执行还原语句,则还原操作失败,出现类似于以下的错误:If a restore statement is attempted on a backup blob file that has an active lease, the restore operation fails with an error similar to the following:

Exception Message: The remote server returned an error: (409) Conflict..

发生这种错误时,需要删除 blob 文件。When such error occurs, the blob files need to be deleted. 有关此情形和如何解决此问题的详细信息,请参阅 Deleting Backup Blob Files with Active LeasesFor more information on this scenario and how to correct this problem, see Deleting Backup Blob Files with Active Leases

操作系统错误 50:不支持该请求OS error 50: The request is not supported

备份数据库时,可能会由于以下原因而出现错误 Operating system error 50(The request is not supported)When backing up a database, you may see error Operating system error 50(The request is not supported) for the following reasons:

  • 指定的存储帐户不是常规用途 V1/V2。The specified storage account is not General Purpose V1/V2.
  • SAS 令牌超过 128 个字符。The SAS token is more than 128 characters.
  • 创建凭据时,SAS 令牌的令牌开头有一个 ? 符号。The SAS token had a ? symbol at the beginning of the token when the credential was created. 如果是,请删除它。If yes, then remove it.
  • 当前连接无法使用存储资源管理器或 SQL Server Management Studio (SSMS) 从当前计算机连接到存储帐户。The current connection is unable to connect to the storage account from the current machine using Storage Explorer or SQL Server Management Studio (SSMS).
  • 分配给 SAS 令牌的策略已过期。The policy assigned to the SAS token is expired. 使用 Azure 存储资源管理器创建新策略,并使用该策略创建新的 SAS 令牌,或者更改凭据并再次尝试备份。Create a new policy using Azure Storage Explorer and either create a new SAS token using the policy or alter the credential and try backing up again.

身份验证错误Authentication errors

WITH CREDENTIAL 是一个新选项,在备份到 Azure Blob 存储服务或从中还原时需要该选项。The WITH CREDENTIAL is a new option and required to back up to or restore from the Azure Blob storage service.

与凭据有关的失败可能包括:The credential specified in the **BACKUP** or **RESTORE** command does not exist. Failures related to credential could be the following: The credential specified in the **BACKUP** or **RESTORE** command does not exist.

要避免此问题,如果备份语句中没有指定凭据,可以使用 T-SQL 语句来创建凭据。To avoid this issue, you can include T-SQL statements to create the credential if one does not exist in the backup statement. 以下是您可以使用的一个示例:The following is an example you can use:

IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE credential_identity = 'mycredential')  
CREATE CREDENTIAL <credential name> WITH IDENTITY = 'mystorageaccount'  
, SECRET = '<storage access key>' ;  

凭据存在但是用于运行备份命令的登录帐户没有访问凭据的权限。The credential exists but the login account that is used to run the backup command does not have permissions to access the credentials. 使用角色为 db_backupoperator 且拥有“更改任意凭据”权限的登录帐户 。Use a login account in the db_backupoperator role with Alter any credential permissions.

验证存储帐户名称和密钥值。Verify the storage account name and key values. 在凭据中存储的信息必须与你在备份和还原操作中使用的 Azure 存储帐户的属性值匹配。The information stored in the credential must match the property values of the Azure storage account you are using in the backup and restore operations.

400(错误的请求)错误400 (Bad Request) errors

使用 SQL Server 2012,执行类似于如下的备份时可能会遇到错误:Using SQL Server 2012 you may encounter an error performing a backup similar to the following:

Backup to URL received an exception from the remote endpoint. Exception Message: 
The remote server returned an error: (400) Bad Request..

这是由 Azure 存储帐户支持的 TLS 版本引起的。This is caused by the TLS version supported by the Azure Storage Account. 更改支持的 TLS 版本或使用 KB4017023 中列出的解决方法。Changing the supported TLS version or using the workaround listed in KB4017023.

代理错误Proxy Errors

如果您使用代理服务器访问 Internet,可能会发现以下问题:If you are using Proxy Servers to access the internet, you may see the following issues:

代理服务器连接限制Connection throttling by Proxy Servers

代理服务器可能具有限制每分钟连接次数的设置。Proxy Servers can have settings that limit the number of connections per minute. “备份到 URL”进程是一个多线程进程,因此可能超过此限制。The Backup to URL process is a multi-threaded process and hence can go over this limit. 如果出现此情况,代理服务器将终止连接。If this happens, the proxy server kills the connection. 若要解决此问题,请更改代理设置,使 SQL Server 不使用该代理。To resolve this issue, change the proxy settings so SQL Server is not using the proxy. 下面是一些您可能在错误日志中看到的类型或错误消息的示例:Following are some examples of the types or error messages you may see in the error log:

Write on "https://storageaccount.blob.core.windows.net/container/BackupAzurefile.bak" failed: Backup to URL received an exception from the remote endpoint. Exception Message: Unable to read data from the transport connection: The connection was closed.
A nonrecoverable I/O error occurred on file "https://storageaccount.blob.core.windows.net/container/BackupAzurefile.bak:" Error could not be gathered from Remote Endpoint.  
  
Msg 3013, Level 16, State 1, Line 2  
  
BACKUP DATABASE is terminating abnormally.  
BackupIoRequest::ReportIoError: write failure on backup device https://storageaccount.blob.core.windows.net/container/BackupAzurefile.bak'. Operating system error Backup to URL received an exception from the remote endpoint. Exception Message: Unable to read data from the transport connection: The connection was closed.

如果使用跟踪标志 3051 打开详细日志记录,您还可能在日志中看到以下消息:If you turn on the verbose logging using the trace flag 3051 you may also see the following message in the logs:

HTTP status code 502, HTTP Status Message Proxy Error (The number of HTTP requests per minute exceeded the configured limit. Contact your ISA Server administrator.)

未选择默认代理设置:Default Proxy Settings not picked up:

有时候,若不选用默认设置,会导致代理身份验证错误,如下所示:Sometimes the default settings are not picked up causing proxy authentication errors such as the one shown below:

A nonrecoverable I/O error occurred on file "https://storageaccount.blob.core.windows.net/container/BackupAzurefile.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (407)* **Proxy Authentication Required.

若要解决此问题,请使用以下步骤创建一个配置文件,以允许“备份到 URL”进程使用默认代理设置:To resolve this issue, create a configuration file that allows the Backup to URL process to use the default proxy settings using the following steps:

  1. 使用以下 xml 内容创建一个名为 BackuptoURL.exe.config 的配置文件:Create a configuration file named BackuptoURL.exe.config with the following xml content:

    <?xml version ="1.0"?>  
    <configuration>   
                    <system.net>   
                                    <defaultProxy enabled="true" useDefaultCredentials="true">   
                                                    <proxy usesystemdefault="true" />   
                                    </defaultProxy>   
                    </system.net>  
    </configuration>  
    
  2. 将该配置文件置于 SQL ServerSQL Server 实例的 Binn 文件夹中。Place the configuration file in the Binn folder of the SQL ServerSQL Server Instance. 例如,如果我的 SQL ServerSQL Server 安装在计算机的 C 驱动器上,可将该配置文件置在 C:\Program Files\Microsoft SQL Server\MSSQL13.\<InstanceName>\MSSQL\Binn 中。For example, if my SQL ServerSQL Server is installed on the C drive of the machine, place the configuration file in C:\Program Files\Microsoft SQL Server\MSSQL13.\<InstanceName>\MSSQL\Binn.

另请参阅See Also

从 Microsoft Azure 中存储的备份还原Restoring From Backups Stored in Microsoft Azure
BACKUP (Transact-SQL)BACKUP (Transact-SQL)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)