SQL Server 備份至 URL 的最佳作法和疑難排解SQL Server Backup to URL Best Practices and Troubleshooting

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

本主題包含從 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.

  • 如果 SQL ServerSQL Server 資料庫位於在 Azure 虛擬機器中執行的 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. 如需詳細資訊,請參閱刪除擁有使用中租用的備份 Blob 檔案For 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 reccomended at SQL Server Backup to URL.

處理大型檔案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 具有最大的 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 Backup section, it is very important when backing up large files.

疑難排解備份至 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.

驗證錯誤: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. 與認證有關的失敗可能包括:Failures related to credential could be the following:

    BACKUPRESTORE 命令中指定的認證不存在。The credential specified in the BACKUP or RESTORE command does not exist. 若要避免此問題,您可以在 Backup 陳述式中加入 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> ;  
    
  • 認證存在,但是用來執行 Backup 命令的登入帳戶沒有存取認證的權限。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.

備份錯誤/失敗:Backup Errors/Failures:

  • 相同 Blob 的平行備份會導致其中一個備份失敗並出現 [初始化失敗] 錯誤。Parallel backups to the same blob cause one of the backups to fail with an Initialization failed error.

  • 使用下列錯誤記錄來協助疑難排解備份錯誤: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.

    • 備份大型資料庫時,請考慮 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

      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.
      
      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;  
      
  • 從壓縮備份還原時,您可能會看見下列錯誤: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.

      若要解決此錯誤,請重新發出 RESTORE 陳述式並搭配指定 BLOCKSIZE = 65536To solve this error, reissue the RESTORE statement with BLOCKSIZE = 65536 specified.

  • 含有使用中租用的 Blob 導致備份期間發生錯誤:失敗的備份活動可能會產生含有使用中租用的 Blob。Error during backup due to blobs that have active lease on them: Failed backup activity can result in blobs with active leases.

    如果重新嘗試執行 Backup 陳述式,備份作業可能會失敗並出現類似以下的錯誤: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 檔案執行 Restore 陳述式,還原作業會失敗並出現類似以下的錯誤: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. 如需有關此案例以及如何更正這個問題的詳細資訊,請參閱 刪除擁有使用中租用的備份 Blob 檔案For more information on this scenario and how to correct this problem, see Deleting Backup Blob Files with Active Leases

Proxy 錯誤Proxy Errors

如果您使用 Proxy 伺服器存取網際網路,可能會看見下列問題:If you are using Proxy Servers to access the internet, you may see the following issues:

Proxy 伺服器連線節流:Connection throttling by Proxy Servers:

Proxy 伺服器可能有限制每分鐘連接數目的設定。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. 如果發生這種情況,Proxy 伺服器會清除該連接。If this happens, the proxy server kills the connection. 若要解決這個問題,請變更 Proxy 設定,讓 SQL Server 不使用 Proxy。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.)

預設 Proxy 設定未收取:Default Proxy Settings not picked up:

有時預設設定未收取會導致 Proxy 驗證錯誤,如下所示: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 處理序可以使用預設 Proxy 設定: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.configCreate 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)