SQL Server 備份至 URLSQL Server Backup to URL

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

本主題介紹使用 Microsoft Azure Blob 儲存體服務作為備份目的地所需的概念、需求及元件。This topic introduces the concepts, requirements and components necessary to use the Microsoft Azure Blob storage service as a backup destination. 使用磁碟或磁帶時,備份和還原功能相同或類似,只有些許的差異。The backup and restore functionality are same or similar to when using DISK or TAPE, with a few differences. 本主題包含這些差異和一些程式碼範例。These differences and a few code examples are included in this topic.

需求、元件和概念Requirements, Components, and Concepts

本節內容:In this section:

安全性Security

以下是備份至 Microsoft Azure Blob 儲存體服務或從中還原時的安全性考量和需求。The following are security considerations and requirements when backing up to or restoring from the Microsoft Azure Blob storage service.

  • 建立 Microsoft Azure Blob 儲存體服務的容器時,建議您將存取權設為 [私用] 。When creating a container for the Microsoft Azure Blob storage service, we recommend that you set the access to private. 將存取權設定為 [私用] 可限制只有能夠提供必要資訊向 Azure 帳戶驗證的使用者或帳戶,才有存取權。Setting the access to private restricts the access to users or accounts able to provide the necessary information to authenticate to the Azure account.

    重要

    SQL ServerSQL Server 需要在 SQL ServerSQL Server 認證中儲存 Azure 帳戶名稱和存取金鑰驗證或共用存取簽章和存取權杖。requires that either an Azure account name and access key authentication or a Shared Access Signature and access token be stored in a SQL ServerSQL Server Credential. 在執行備份或還原作業時,系統會使用此資訊向該 Azure 帳戶驗證。This information is used to authenticate to the Azure account when performing backup or restore operations.

  • 用來發出 BACKUP 或 RESTORE 命令的使用者帳戶應該位於擁有 改變任何認證 權限的 db_backup 運算子 資料庫角色中。The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.

重要元件和概念簡介Introduction to Key Components and Concepts

下列兩節將介紹 Microsoft Azure Blob 儲存體服務,以及備份至 Microsoft Azure Blob 儲存體服務或從中還原時使用的 SQL ServerSQL Server 元件。The following two sections introduce the Microsoft Azure Blob storage service, and the SQL ServerSQL Server components used when backing up to or restoring from the Microsoft Azure Blob storage service. 為了備份至 Microsoft Azure Blob 儲存體服務或從中還原,請務必了解這些元件以及它們之間的互動方式。It is important to understand the components and the interaction between them to do a backup to or restore from the Microsoft Azure Blob storage service.

在您的 Azure 訂用帳戶內建立 Azure 儲存體帳戶是這個程序的第一個步驟。Creating an Azure Storage account within your Azure subscription is the first step in this process. 這個儲存體帳戶是系統管理帳戶,有以儲存體帳戶建立之所有容器和物件的完整系統管理權限。This storage account is an administrative account that has full administrative permissions on all containers and objects created with the storage account. SQL ServerSQL Server 可以使用 Azure 儲存體帳戶名稱及其存取金鑰值來驗證和讀寫 Blob 至 Microsoft Azure Blob 儲存體服務,或使用針對特定容器產生的共用存取簽章 Token 授與它讀寫權限。can either use the Azure storage account name and its access key value to authenticate and write and read blobs to the Microsoft Azure Blob storage service or use a Shared Access Signature token generated on specific containers granting it read and write rights. 如需 Azure 儲存體帳戶的詳細資訊,請參閱關於 Azure 儲存體帳戶,如需共用存取簽章的詳細資訊,請參閱共用存取簽章,第 1 部分:了解 SAS 模型For more information on Azure Storage Accounts, see About Azure Storage Accounts and for more information about Shared Access Signatures, see Shared Access Signatures, Part 1: Understanding the SAS Model. SQL ServerSQL Server 認證會儲存這項驗證資訊,並且在備份或還原作業期間使用。The SQL ServerSQL Server Credential stores this authentication information and is used during the backup or restore operations.

備份至區塊 Blob 與分頁 BlobBackup to block blob vs. page blob

Microsoft Azure Blob 儲存體服務可以儲存的 Blob 類型有兩種:區塊和分頁 Blob。There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: block and page blobs. SQL Server 備份可以使用這兩種 Blob 類型,視所使用的 Transact-SQL 語法而定:如果認證中使用儲存體金鑰,則會使用分頁 Blob;如果使用共用存取簽章,就會使用區塊 Blob。SQL Server backup can use either blob type depending upon the Transact-SQL syntax used: If the storage key is used in the credential, page blob will be used; if the Shared Access Signature is used, block blob will be used.

備份至區塊 Blob 功能僅能在 SQL Server 2016 或更新版本中使用。Backup to block blob is only available in SQL Server 2016 or later version. 若您執行 SQL Server 2016 或更新版本,我們建議您改為備份至區塊 Blob,而非分頁 Blob。We recommend you to backup to block blob instead of page block if you are running SQL Server 2016 or later version. 主要的原因如下:The main reasons are:

  • 相較於儲存體金鑰,共用存取簽章是授權 Blob 存取更安全的方式。Shared Access Signature is a safer way to authorize blob access compared to storage key.
  • 您可以備份至多個區塊 Blob,以取得更佳的備份及還原效能,並支援更大的資料庫備份。You can backup to multiple block blobs to get better backup and restore performance, and support larger database backup.
  • 區塊 Blob分頁 Blob 更便宜。Block blob is cheaper than page blob.

將大型資料庫備份到 Blob 儲存體會受限於受控執行個體 T-SQL 差異、限制和已知問題中所列的限制。Backup of a large database to blob storage is subject to the limitations listed in Managed instance T-SQL differences, limitations, and known issues.

若資料庫太大,請執行以下其中一項操作:If the database is too large, either:

  • 使用備份壓縮或Use backup compression or
  • 備份至多個區塊 BlobBackup to multiple block blobs

Microsoft Azure Blob 儲存體服務Microsoft Azure Blob storage service

儲存體帳戶: 儲存體帳戶是所有儲存體服務的起點。Storage Account: The storage account is the starting point for all storage services. 若要存取 Microsoft Azure Blob 儲存體服務,請先建立 Azure 儲存體帳戶。To access the Microsoft Azure Blob storage service, first create an Azure storage account. 如需詳細資訊,請參閱 建立儲存體帳戶For more information, see Create a Storage Account

容器: 容器會提供一組 Blob 的群組,且可以儲存不限數目的 Blob。Container: A container provides a grouping of a set of blobs, and can store an unlimited number of blobs. 若要將 SQL ServerSQL Server 備份寫入 Microsoft Azure Blob 服務,您至少必須建立根容器。To write a SQL ServerSQL Server backup to the Microsoft Azure Blob storage service, you must have at least the root container created. 您可以針對容器產生共用存取簽章 Token,並僅對特定容器的物件授與存取權。You can generate a Shared Access Signature token on a container and grant access to objects on a specific container only.

Blob: 任何類型和大小的檔案。Blob: A file of any type and size. Microsoft Azure Blob 儲存體服務可以儲存的 Blob 類型有兩種:區塊和分頁 Blob。There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: block and page blobs. SQL ServerSQL Server 備份可以使用這兩種 Blob 類型,視所用的 Transact-SQL 語法而定。backup can use either blob type depending upon the Transact-SQL syntax used. 您可以使用下列 URL 格式來定址 Blob: https://<儲存體帳戶>.blob.core.windows.net/<容器>/<Blob>。Blobs are addressable using the following URL format: https://<storage account>.blob.core.windows.net/<container>/<blob>. 如需 Microsoft Azure Blob 儲存體服務的詳細資訊,請參閱 以 .NET 開始使用 Azure Blob 儲存體For more information about the Microsoft Azure Blob storage service, see How to use the Blob Storage from .NET. 如需分頁 Blob 的詳細資訊,請參閱 了解區塊 Blob、附加 Blob 和分頁 BlobFor more information about page and block blobs, see Understanding Block and Page Blobs.

Azure Blob 儲存體Azure Blob Storage

Azure 快照集: 在某個時點間取得的 Azure Blob 快照集。Azure Snapshot: A snapshot of an Azure blob taken at a point in time. 如需詳細資訊,請參閱 建立 Blob 的快照集For more information, see Creating a Snapshot of a Blob. SQL ServerSQL Server 備份現在支援儲存在 Microsoft Azure Blob 儲存體服務中的資料庫檔案 Azure 快照集備份。backup now supports Azure snapshot backups of database files stored in the Microsoft Azure Blob storage service. 如需詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information, see File-Snapshot Backups for Database Files in Azure.

SQL ServerSQL Server ComponentsSQL ServerSQL Server Components

URL: URL 會指定唯一備份檔案的統一資源識別項 (URI)。URL: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. 此 URL 是用來提供 SQL ServerSQL Server 備份檔案的位置和名稱。The URL is used to provide the location and name of the SQL ServerSQL Server backup file. 此 URL 必須指向實際的 Blob,而非只有容器。The URL must point to an actual blob, not just a container. 如果 Blob 不存在,就會建立 Blob。If the blob does not exist, it is created. 如果指定了現有的 Blob,除非同時指定 "WITH FORMAT" 選項覆寫 Blob 中現有的備份檔,否則 BACKUP 會失敗。If an existing blob is specified, BACKUP fails, unless the "WITH FORMAT" option is specified to overwrite the existing backup file in the blob.

以下是 URL 值範例:http[s]://ACCOUNTNAME.blob.core.windows.net/<容器>/<.bak>。Here is a sample URL value: http[s]://ACCOUNTNAME.blob.core.windows.net/<CONTAINER>/<FILENAME.bak>. HTTPS 不是必要項目,但是建議使用。HTTPS is not required, but is recommended.

認證: SQL ServerSQL Server 認證是用來儲存連線到 SQL Server 外部資源所需之驗證資訊的物件。Credential: A SQL ServerSQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. 此處, SQL ServerSQL Server 備份和還原程序會使用認證,向 Microsoft Azure Blob 儲存體服務及其容器和 Blob 物件驗證。Here, SQL ServerSQL Server backup and restore processes use credential to authenticate to the Microsoft Azure Blob storage service and its container and blob objects. 認證會儲存儲存體帳戶名稱和儲存體帳戶 存取金鑰 值,或容器 URL 及其共用存取簽章 Token。The Credential stores either the name of the storage account and the storage account access key values or container URL and its Shared Access Signature token. 一旦建立認證,BACKUP/RESTORE 陳述式的語法就會決定 Blob 類型和所需的認證。Once the credential is created, the syntax of the BACKUP/RESTORE statements determines the type of blob and the credential required.

如需如何建立共用存取簽章的範例,請參閱本主題稍後的 建立共用存取簽章 範例,若要建立 SQL ServerSQL Server 認證,請參閱本主題稍後的 建立認證 範例。For an example about how to create a Shared Access Signature, see Create a Shared Access Signature examples later in this topic and to create a SQL ServerSQL Server Credential, see Create a Credential examples later in this topic.

如需認證的一般資訊,請參閱 認證For general information about credentials, see Credentials

如需使用認證之其他範例的資訊,請參閱 建立 SQL Server Agent ProxyFor information on other examples where credentials are used, see Create a SQL Server Agent Proxy.

限制Limitations

  • 不支援備份至進階儲存體。Backup to premium storage is not supported.

  • SQL Server 會將使用分頁 Blob 支援的備份大小上限限制為 1 TB。SQL Server limits the maximum backup size supported using a page blob to 1 TB. 使用區塊 Blob 支援的備份大小上限則會限制為大約 200 GB (50,000 個區塊 * 4MB 的 MAXTRANSFERSIZE)。The maximum backup size supported using block blobs is limited to approximately 200 GB (50,000 blocks * 4MB MAXTRANSFERSIZE). 區塊 Blob 支援等量分割,以支援實質上較大的備份大小。Block blobs support striping to support substantially larger backup sizes.

  • 您可以使用 TSQL、SMO、PowerShell Cmdlet、SQL Server Management Studio [備份精靈] 或 [還原精靈] 發出 Backup 或 Restore 陳述式。You can issue backup or restore statements by using TSQL, SMO, PowerShell cmdlets, SQL Server Management Studio Backup or Restore wizard.

  • 不支援建立邏輯裝置名稱。Creating a logical device name is not supported. 因此,不支援使用 sp_dumpdevice 或透過 SQL Server Management Studio 加入 URL 做為備份裝置。So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.

  • 不支援附加至現有的備份 Blob。Appending to existing backup blobs is not supported. 現有 blob 的備份只能使用 WITH FORMAT 選項來覆寫。Backups to an existing blob can only be overwritten by using the WITH FORMAT option. 不過,當使用檔案快照集備份時 (使用 WITH FILE_SNAPSHOT 引數),不允許 WITH FORMAT 引數,以避免留下使用原始檔案快照集備份建立的孤立檔案快照集。However, when using file-snapshot backups (using the WITH FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to avoid leaving orphaned file-snapshots that were created with the original file-snapshot backup.

  • 只有使用區塊 Blob 和使用共用存取簽章 (SAS) Token 才支援備份至單一備份作業中的多個 Blob,而不是 SQL 認證的儲存體帳戶金鑰。Backup to multiple blobs in a single backup operation is only supported using block blobs and using a Shared Access Signature (SAS) token rather than the storage account key for the SQL Credential.

  • 針對分頁 Blob,不支援指定 BLOCKSIZESpecifying BLOCKSIZE is not supported for page blobs.

  • 針對分頁 Blob,不支援指定 MAXTRANSFERSIZESpecifying MAXTRANSFERSIZE is not supported page blobs.

  • 不支援指定備份組選項 - RETAINDAYSEXPIREDATESpecifying backupset options - RETAINDAYS and EXPIREDATE are not supported.

  • SQL ServerSQL Server 的備份裝置名稱大小上限為 259 個字元。has a maximum limit of 259 characters for a backup device name. BACKUP TO URL 會用 36 個字元的必要項目指定 URL - 'https://.blob.core.windows.net//.bak ',而保留 223 個字元供帳戶、容器和 Blob 名稱共用。The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together.

支援 Backup/Restore 陳述式Support for Backup/Restore Statements

Backup/Restore 陳述式Backup/Restore Statement 支援Supported 例外狀況Exceptions 註解Comments
BACKUPBACKUP YY 針對區塊 Blob,支援指定 BLOCKSIZE 和 MAXTRANSFERSIZE。BLOCKSIZE and MAXTRANSFERSIZE are supported for block blobs. 它們不支援用於分頁 Blob。They are not supported for page blobs. 備份至區塊 Blob 需要 SQL Server 認證中所儲存的共用存取簽章。BACKUP to a block blob requires a Shared Access Signature saved in a SQL Server credential. 備份至分頁 Blob 需要 SQL ServerSQL Server 認證中所儲存的儲存體帳戶金鑰,而且需要指定 WITH CREDENTIAL 引數。BACKUP to page blob requires the storage account key saved in a SQL ServerSQL Server credential, and requires the WITH CREDENTIAL argument to be specified.
RESTORERESTORE YY 如果 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰所定義,則需要定義 SQL ServerSQL Server 認證,而且需要指定 WITH CREDENTIAL 引數。Requires a SQL ServerSQL Server credential to be defined, and requires the WITH CREDENTIAL argument to be specified if the SQL ServerSQL Server credential is defined using the storage account key as the secret
RESTORE FILELISTONLYRESTORE FILELISTONLY YY 如果 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰所定義,則需要定義 SQL ServerSQL Server 認證,而且需要指定 WITH CREDENTIAL 引數。Requires a SQL ServerSQL Server credential to be defined, and requires the WITH CREDENTIAL argument to be specified if the SQL ServerSQL Server credential is defined using the storage account key as the secret
RESTORE HEADERONLYRESTORE HEADERONLY YY 如果 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰所定義,則需要定義 SQL ServerSQL Server 認證,而且需要指定 WITH CREDENTIAL 引數。Requires a SQL ServerSQL Server credential to be defined, and requires the WITH CREDENTIAL argument to be specified if the SQL ServerSQL Server credential is defined using the storage account key as the secret
RESTORE LABELONLYRESTORE LABELONLY YY 如果 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰所定義,則需要定義 SQL ServerSQL Server 認證,而且需要指定 WITH CREDENTIAL 引數。Requires a SQL ServerSQL Server credential to be defined, and requires the WITH CREDENTIAL argument to be specified if the SQL ServerSQL Server credential is defined using the storage account key as the secret
RESTORE VERIFYONLYRESTORE VERIFYONLY YY 如果 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰所定義,則需要定義 SQL ServerSQL Server 認證,而且需要指定 WITH CREDENTIAL 引數。Requires a SQL ServerSQL Server credential to be defined, and requires the WITH CREDENTIAL argument to be specified if the SQL ServerSQL Server credential is defined using the storage account key as the secret
RESTORE REWINDONLYRESTORE REWINDONLY -

如需 Backup 陳述式的語法和一般資訊,請參閱 BACKUP (Transact-SQL)For syntax and general information about backup statements, see BACKUP (Transact-SQL).

如需 Restore 陳述式的語法和一般資訊,請參閱 RESTORE (Transact-SQL)For syntax and general information about restore statements, see RESTORE (Transact-SQL).

支援 Backup 引數Support for Backup Arguments

引數Argument 支援Supported 例外狀況Exception 註解Comments
DATABASEDATABASE YY
LOGLOG YY
TO (URL)TO (URL) YY 與 DISK 和 TAPE 不同,URL 不支援指定或建立邏輯名稱。Unlike DISK and TAPE, URL does not support specifying or creating a logical name. 這個引數是用來指定備份檔案的 URL 路徑。This argument is used to specify the URL path for the backup file.
MIRROR TOMIRROR TO YY
WITH 選項:WITH OPTIONS:
CREDENTIALCREDENTIAL YY 使用 BACKUP TO URL 選項備份至 Microsoft Azure Blob 儲存體服務時,而且只有當 SQL ServerSQL Server 認證是使用作為密碼的儲存體帳戶金鑰定義時,才支援 WITH CREDENTIAL。WITH CREDENTIAL is only supported when using BACKUP TO URL option to back up to the Microsoft Azure Blob storage service and only if the SQL ServerSQL Server credential is defined using the storage account key as the secret
FILE_SNAPSHOTFILE_SNAPSHOT YY
ENCRYPTIONENCRYPTION YY 當指定 WITH ENCRYPTION 引數時, SQL ServerSQL Server 檔案快照集備份會確保整個資料庫先以 TDE 加密再備份;而且在這個情況下,會使用資料庫對 TDE 指定的演算法加密檔案快照集備份檔案本身。When the WITH ENCRYPTION argument is specified, SQL ServerSQL Server File-Snapshot Backup ensures that the entire database was TDE-encrypted before taking the backup and, if so, encrypts the file-snapshot backup file itself using the algorithm specified for TDE on the database. 如果整個資料庫中的資料庫所有資料都未加密,則備份會失敗 (例如,加密程序尚未完成)。If all data in the database in the entire database is not encrypted, the backup will fail (e.g. the encryption process is not yet complete).
DIFFERENTIALDIFFERENTIAL YY
COPY_ONLYCOPY_ONLY YY
COMPRESSION|NO_COMPRESSIONCOMPRESSION|NO_COMPRESSION YY 不支援檔案快照備份Not supported for file-snapshot backup
DESCRIPTIONDESCRIPTION YY
NAMENAME YY
EXPIREDATE | RETAINDAYSEXPIREDATE | RETAINDAYS -
NOINIT | INITNOINIT | INIT - 您無法附加至 Blob。Appending to blobs is not possible. 若要覆寫備份,請使用 WITH FORMAT 引數。To overwrite a backup use the WITH FORMAT argument. 不過,當使用檔案快照集備份時 (使用 WITH FILE_SNAPSHOT 引數),不允許 WITH FORMAT 引數,以避免留下使用原始備份建立的孤立檔案快照集。However, when using file-snapshot backups (using the WITH FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to avoid leaving orphaned file-snapshots that were created with the original backup.
NOSKIP | SKIPNOSKIP | SKIP -
NOFORMAT | FORMATNOFORMAT | FORMAT YY 除非指定了 WITH FORMAT ,否則將備份帶入現有的 Blob 會失敗。A backup taken to an existing blob fails unless WITH FORMAT is specified. 指定 WITH FORMAT 時就會覆寫現有的 Blob。The existing blob is overwritten when WITH FORMAT is specified. 不過,當使用檔案快照集備份時 (使用 WITH FILE_SNAPSHOT 引數),不允許 FORMAT 引數,以避免留下使用原始檔案快照集備份建立的孤立檔案快照集。However, when using file-snapshot backups (using the WITH FILE_SNAPSHOT argument), the FORMAT argument is not permitted to avoid leaving orphaned file-snapshots that were created with the original file-snapshot backup. 不過,當使用檔案快照集備份時 (使用 WITH FILE_SNAPSHOT 引數),不允許 WITH FORMAT 引數,以避免留下使用原始備份建立的孤立檔案快照集。However, when using file-snapshot backups (using the WITH FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to avoid leaving orphaned file-snapshots that were created with the original backup.
MEDIADESCRIPTIONMEDIADESCRIPTION YY
MEDIANAMEMEDIANAME YY
BLOCKSIZEBLOCKSIZE YY 不支援用於分頁 Blob。Not supported for page blob. 支援用於區塊 Blob。Supported for block blob. 建議使用 BLOCKSIZE=65536,以最佳化使用區塊 Blob 中所允許的 50,000 個區塊。Recommend BLOCKSIZE=65536 to optimize use of the 50,000 blocks allowed in a block blob.
BUFFERCOUNTBUFFERCOUNT YY
MAXTRANSFERSIZEMAXTRANSFERSIZE YY 不支援用於分頁 Blob。Not supported for page blob. 支援用於區塊 Blob。Supported for block blob. 預設值為 1048576。Default is 1048576. 此值的最大範圍為 4 MB,並以 65536 個位元組增量。The value can range up to 4 MB in increments of 65536 bytes.
建議使用 MAXTRANSFERSIZE=4194304,以最佳化使用區塊 Blob 中所允許的 50,000 個區塊。Recommend MAXTRANSFERSIZE=4194304 to optimize use of the 50,000 blocks allowed in a block blob.
NO_CHECKSUM | CHECKSUMNO_CHECKSUM | CHECKSUM YY
STOP_ON_ERROR | CONTINUE_AFTER_ERRORSTOP_ON_ERROR | CONTINUE_AFTER_ERROR YY
STATSSTATS YY
REWIND | NOREWINDREWIND | NOREWIND -
UNLOAD | NOUNLOADUNLOAD | NOUNLOAD -
NORECOVERY | STANDBYNORECOVERY | STANDBY YY
NO_TRUNCATENO_TRUNCATE YY

如需 Backup 引數的詳細資訊,請參閱 BACKUP (Transact-SQL)For more information about backup arguments, see BACKUP (Transact-SQL).

支援 Restore 引數Support for Restore Arguments

引數Argument 支援Supported 例外狀況Exceptions 註解Comments
DATABASEDATABASE YY
LOGLOG YY
FROM (URL)FROM (URL) YY FROM URL 引數是用來指定備份檔案的 URL 路徑。The FROM URL argument is used to specify the URL path for the backup file.
WITH Options:WITH Options:
CREDENTIALCREDENTIAL YY 當您使用 RESTORE FROM URL 選項,從 Microsoft Azure Blob 儲存體服務還原時,才支援 WITH CREDENTIAL。WITH CREDENTIAL is only supported when using RESTORE FROM URL option to restore from Microsoft Azure Blob storage service.
PARTIALPARTIAL YY
RECOVERY | NORECOVERY | STANDBYRECOVERY | NORECOVERY | STANDBY YY
LOADHISTORYLOADHISTORY YY
MOVEMOVE YY
REPLACEREPLACE YY
RESTARTRESTART YY
RESTRICTED_USERRESTRICTED_USER YY
FILEFILE -
PASSWORDPASSWORD YY
MEDIANAMEMEDIANAME YY
MEDIAPASSWORDMEDIAPASSWORD YY
BLOCKSIZEBLOCKSIZE YY
BUFFERCOUNTBUFFERCOUNT -
MAXTRANSFERSIZEMAXTRANSFERSIZE -
CHECKSUM | NO_CHECKSUMCHECKSUM | NO_CHECKSUM YY
STOP_ON_ERROR | CONTINUE_AFTER_ERRORSTOP_ON_ERROR | CONTINUE_AFTER_ERROR YY
FILESTREAMFILESTREAM YY 不支援快照備份Not supported for snapshot backup
STATSSTATS YY
REWIND | NOREWINDREWIND | NOREWIND -
UNLOAD | NOUNLOADUNLOAD | NOUNLOAD -
KEEP_REPLICATIONKEEP_REPLICATION YY
KEEP_CDCKEEP_CDC YY
ENABLE_BROKER | ERROR_BROKER_CONVERSATIONS | NEW_BROKERENABLE_BROKER | ERROR_BROKER_CONVERSATIONS | NEW_BROKER YY
STOPAT | STOPATMARK | STOPBEFOREMARKSTOPAT | STOPATMARK | STOPBEFOREMARK YY

如需 Restore 引數的詳細資訊,請參閱 RESTORE 引數 (Transact-SQL)For more information about Restore arguments, see RESTORE Arguments (Transact-SQL).

在 SQL Server Management Studio 中使用備份工作Using Back Up Task in SQL Server Management Studio

您可以在 SQL Server Management Studio 中使用 SQL Server 認證,透過備份工作將資料庫備份至 URL。You can back up a database to URL through the Back Up task in SQL Server Management Studio using a SQL Server Credential.

注意

若要建立 SQL ServerSQL Server 檔案快照集備份,或覆寫現有的媒體集,您必須在 SQL Server Management Studio 中使用 Transact-SQL、Powershell 或 C#,而不是使用備份工作。To create a SQL ServerSQL Server file-snapshot backup, or overwrite an existing media set, you must use Transact-SQL, Powershell or C# rather than the Back Up task in SQL Server Management Studio.

下列步驟說明如何在 SQL Server Management Studio 中對備份資料庫工作進行變更,以允許備份至 Azure 儲存體:The following steps describe the changes made to the Back Up Database task in SQL Server Management Studio to allow for backing up to Azure storage:

  1. 在物件總管 中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. 展開 [資料庫] ,以滑鼠右鍵按一下所需的資料庫,指向 [工作] ,然後按一下 [備份...] 。Expand Databases, right-click the desired database, point to Tasks, and then click Back Up....

  3. [一般] 頁面之 [目的地] 區段的 [備份至:] 下拉式清單中提供 [URL] 選項。On the General page in the Destination section the URL option is available in the Back up to: drop-down list. [URL] 選項可用以建立備份至 Microsoft Azure 儲存體。The URL option is used to create a backup to Microsoft Azure storage. 按一下 [加入] ,[選取備份目的地] 對話方塊隨即開啟:Click Add and the Select Backup Destination dialog box will open:

    1. Azure 儲存體容器: 儲存備份檔案的 Microsoft Azure 儲存體容器名稱。Azure storage container: The name of the Microsoft Azure storage container to store the backup files. 從下拉式清單中選取現有的容器,或手動輸入容器。Select an existing container from the drop-down list or manually enter the container.

    2. 共用的存取原則: 輸入手動輸入之容器的共用存取簽章。Shared Access Policy: Enter the shared access signature for a manually entered container. 如果已選擇現有的容器,則此欄位無法使用。This field is not available if an existing container was chosen.

    3. 備份檔案: 備份檔案的名稱。Backup File: Name of the backup file.

    4. 新增容器: 用來註冊您沒有共用存取簽章的現有容器。New Container: Used to register an existing container that you do not have a shared access signature for. 請參閱 連接到 Microsoft Azure 訂用帳戶See Connect to a Microsoft Azure Subscription.

注意

加入單一媒體集有多個備份檔案和儲存體容器的支援。Add supports multiple backup files and storage containers for a single media set.

當您選取 URL 作為目的地時,[媒體選項] 頁面中的某些選項會停用。When you select URL as the destination, certain options in the Media Options page are disabled. 下列主題包含有關備份資料庫對話方塊的詳細資訊:The following topics have more information on the Back Up Database dialog:

備份資料庫 (一般頁面)Back Up Database (General Page)

備份資料庫 (媒體選項頁面)Back Up Database (Media Options Page)

備份資料庫 (備份選項頁面)Back Up Database (Backup Options Page)

建立認證 - 向 Azure 儲存體驗證Create Credential - Authenticate to Azure Storage

使用 [維護計畫精靈] 將 SQL Server 備份至 URLSQL Server Backup to URL Using Maintenance Plan Wizard

與先前所述的備份工作類似,SQL Server Management Studio 中的 [維護計畫精靈] 包含 URL 作為其中一個目的地選項,也包含備份至 Microsoft Azure 儲存體所需的其他支援物件 (像是 SQL 認證)。Similar to the backup task described previously, the Maintenance Plan Wizard in SQL Server Management Studio includes URL as one of the destination options, and other supporting objects required to backup to Azure storage like the SQL Credential. 如需詳細資訊,請參閱 Using Maintenance Plan Wizard 中的 Define Backup Tasks一節。It has the same For more information, see the Define Backup Tasks section in Using Maintenance Plan Wizard.

注意

若要建立等量的備份組、 SQL ServerSQL Server 檔案快照集備份,或使用共用存取權杖的 SQL 認證,您必須在 [維護計畫精靈] 中使用 Transact-SQL、Powershell 或 C#,而不是使用備份工作。To create a striped backup set, a SQL ServerSQL Server file-snapshot backup, or a SQL credential using Shared Access token, you must use Transact-SQL, Powershell or C# rather than the Backup task in Maintenance Plan Wizard.

使用 SQL Server Management Studio 從 Microsoft Azure 儲存體還原Restoring from Microsoft Azure storage Using SQL Server Management Studio

還原資料庫工作會包含 URL 作為還原裝置的來源。The Restore Database task includes URL as a device to restore from. 下列步驟說明使用還原工作,從 Microsoft Azure Blob 儲存體服務還原︰The following steps describe using the Restore task to restore from the Microsoft Azure Blob storage service:

  1. 以滑鼠右鍵按一下 [資料庫] ,然後選取 [還原資料庫...] 。Right-click Databases and select Restore Database....

  2. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置] 。On the General page, select Device under the Source section.

  3. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box.

  4. 從 [備份媒體類型:] 下拉式清單中選取 [URL] 。Select URL from the Backup media type: drop-down list. 按一下 [加入] 開啟 [選取備份檔案位置] 對話方塊。Click Add to open the Select a Backup File Location dialog box.

    1. Azure 儲存體容器: 包含備份檔案之 Microsoft Azure 儲存體容器的完整名稱。Azure storage container: The fully qualified name of the Microsoft Azure storage container which contains the backup files. 從下拉式清單中選取現有的容器,或手動輸入完整容器名稱。Select an existing container from the drop-down list or manually enter the fully qualified container name.

    2. 共用存取簽章: 用來輸入指定容器的共用存取簽章。Shared Access Signature: Used to enter the shared access signature for the designated container.

    3. 新增: 用來註冊您沒有共用存取簽章的現有容器。Add: Used to register an existing container that you do not have a shared access signature for. 請參閱 連接到 Microsoft Azure 訂用帳戶See Connect to a Microsoft Azure Subscription.

    4. 確定: SQL Server 會使用您提供的 SQL 認證資訊連線到 Microsoft Azure 儲存體,並開啟 [在 Microsoft Azure 中尋找備份檔案] 對話方塊。OK: SQL Server connects to Microsoft Azure storage using the SQL Credential information you provided and opens the Locate Backup File in Microsoft Azure dialog. 位於儲存體容器中的備份檔案,會顯示在此頁面上。The backup files residing in the storage container are displayed on this page. 選取您要用以還原的檔案,並按一下 [確定]Select the file you want to use to restore and click OK. 如此會讓您回到 [選取備份裝置] 對話方塊,而按一下此對話方塊中的 [確定] ,會帶您回到您可完成還原的主要 [還原] 對話方塊。This takes you back to the Select Backup Devices dialog, and clicking OK on this dialog takes you back to the main Restore dialog where you will be able complete the restore.

    還原資料庫 (一般頁面)Restore Database (General Page)

    還原資料庫 (檔案頁面)Restore Database (Files Page)

    還原資料庫 (選項頁面)Restore Database (Options Page)

程式碼範例Code Examples

本節包含下列範例。This section contains the following examples.

注意

如需搭配使用 SQL Server 2016 和 Microsoft Azure Blob 儲存體服務的教學課程,請參閱教學課程:搭配使用 Microsoft Azure Blob 儲存體服務和 SQL Server 2016 資料庫For a tutorial on using SQL Server 2016 with the Microsoft Azure Blob storage service, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases

建立共用存取簽章Create a Shared Access Signature

下列範例會建立共用存取簽章,可用在新建立的容器上建立 SQL ServerSQL Server 認證。The following example creates Shared Access Signatures that can be used to create a SQL ServerSQL Server Credential on a newly created container. 此指令碼會建立關聯到預存存取原則的共用存取簽章。The script creates a Shared Access Signature that is associated with a Stored Access Policy. 如需詳細資訊,請參閱共用存取簽章,第 1 部分:了解 SAS 模型For more information, see Shared Access Signatures, Part 1: Understanding the SAS Model. 這個指令碼也會撰寫在 SQL Server 上建立認證所需的 T-SQL 命令。The script also writes the T-SQL command required to create the credential on SQL Server.

注意

此範例需要 Microsoft Azure Powershell。The example requires Microsoft Azure Powershell. 如需安裝及使用 Azure Powershell 的資訊,請參閱 如何安裝和設定 Azure PowerShellFor information about installing and using Azure Powershell, see How to install and configure Azure PowerShell.
這些指令碼是使用 Azure PowerShell 5.1.15063 所驗證。These scripts were verified using Azure PowerShell 5.1.15063.

與預存存取原則相關聯的共用存取簽章Shared Access Signature that is associated with a Stored Access Policy

# Define global variables for the script  
$prefixName = '<a prefix name>'  # used as the prefix for the name for various objects  
$subscriptionName='<your subscription name>'   # the name of subscription name you will use  
$locationName = '<a data center location>'  # the data center region you will use  
$storageAccountName= $prefixName + 'storage' # the storage account name you will create or use  
$containerName= $prefixName + 'container'  # the storage container name to which you will attach the SAS policy with its SAS token  
$policyName = $prefixName + 'policy' # the name of the SAS policy  


# Set a variable for the name of the resource group you will create or use  
$resourceGroupName=$prefixName + 'rg'   

# adds an authenticated Azure account for use in the session   
Connect-AzAccount

# set the tenant, subscription and environment for use in the rest of   
Set-AzContext -SubscriptionName $subscriptionName   

# create a new resource group - comment out this line to use an existing resource group  
New-AzResourceGroup -Name $resourceGroupName -Location $locationName   

# Create a new ARM storage account - comment out this line to use an existing ARM storage account  
New-AzStorageAccount -Name $storageAccountName -ResourceGroupName $resourceGroupName -Type Standard_RAGRS -Location $locationName   

# Get the access keys for the ARM storage account  
$accountKeys = Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName  

# Create a new storage account context using an ARM storage account  
$storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].value 

# Creates a new container in blob storage  
$container = New-AzStorageContainer -Context $storageContext -Name $containerName  
$cbc = $container.CloudBlobContainer  

# Sets up a Stored Access Policy and a Shared Access Signature for the new container  
$policy = New-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $policyName -Context $storageContext -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission "rwld"
$sas = New-AzStorageContainerSASToken -Policy $policyName -Context $storageContext -Container $containerName
Write-Host 'Shared Access Signature= '$($sas.Substring(1))''  

# Outputs the Transact SQL to the clipboard and to the screen to create the credential using the Shared Access Signature  
Write-Host 'Credential T-SQL'  
$tSql = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sas.Substring(1)   
$tSql | clip  
Write-Host $tSql  

成功執行指令碼之後,請將 CREATE CREDENTIAL 命令複製到查詢工具,再連線到 SQL Server 執行個體,然後執行命令以建立含共用存取簽章的認證。After successfully running the script, copy the CREATE CREDENTIAL command to a query tool, connect to an instancance of SQL Server and run the command to create the credential with the Shared Access Signature.

建立認證Create a Credential

下列範例會建立 SQL ServerSQL Server 認證,以向 Microsoft Azure Blob 儲存體服務驗證。The following examples create SQL ServerSQL Server credentials for authentication to the Microsoft Azure Blob storage service. 執行下列其中一項動作。Do one of the following.

  1. 使用共用存取簽章Using Shared Access Signature

如果您已執行上述建立共用存取簽章的指令碼,請將 CREATE CREDENTIAL 複製到查詢編輯器,再連線到您的 SQL Server 執行個體,然後執行命令。If you ran the script to create the Shared Access Signature above, copy the CREATE CREDENTIAL to a query editor connected to your instance of SQL Server and run the command.

下列為建立認證以使用共用存取簽章的 T-SQL 範例。The following T-SQL is an example that creates the credential to use a Shared Access Signature.

IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>')  
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
   SECRET = '<SAS_TOKEN>';  
  1. 使用儲存體帳戶的身分識別和存取金鑰Using storage account identity and access key
IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = '<mycredentialname>')  
CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<mystorageaccountaccesskey>';  

執行完整的資料庫備份Perform a full database backup

下列範例會將 AdventureWorks2016 資料庫完整備份至 Microsoft Azure Blob 儲存體服務。The following examples perform a full database backup of the AdventureWorks2016 database to the Microsoft Azure Blob storage service. 執行下列其中之一:Do one of the following:

  1. 至 URL,使用共用存取簽章To URL using Shared Access Signature
BACKUP DATABASE AdventureWorks2016   
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak';  
GO   
  1. 至 URL,使用儲存體帳戶的身分識別和存取金鑰To URL using storage account identity and access key
BACKUP DATABASE AdventureWorks2016  
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak'   
      WITH CREDENTIAL = '<mycredentialname>'   
     ,COMPRESSION  
     ,STATS = 5;  
GO   

使用 STOPAT 還原至時間點Restoring to a point-in-time using STOPAT

下列範例會將 AdventureWorks2016 範例資料庫還原至某個時點狀態,並且顯示還原作業。The following example restores the AdventureWorks2016 sample database to its state at a point in time, and shows a restore operation.

  1. 從 URL,使用共用存取簽章From URL using Shared Access Signature
RESTORE DATABASE AdventureWorks2016 FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_2015_05_18_16_00_00.bak'   
WITH MOVE 'AdventureWorks2016_data' to 'C:\Program Files\Microsoft SQL Server\<myinstancename>\MSSQL\DATA\AdventureWorks2016.mdf'  
,MOVE 'AdventureWorks2016_log' to 'C:\Program Files\Microsoft SQL Server\<myinstancename>\MSSQL\DATA\AdventureWorks2016.ldf'  
,NORECOVERY  
,REPLACE  
,STATS = 5;  
GO   

RESTORE LOG AdventureWorks2016 FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016_2015_05_18_18_00_00.trn'   
WITH   
RECOVERY   
,STOPAT = 'May 18, 2015 5:35 PM'   
GO  

另請參閱See Also

SQL Server 備份至 URL 的最佳作法和疑難排解 SQL Server Backup to URL Best Practices and Troubleshooting
系統資料庫的備份與還原 (SQL Server) Back Up and Restore of System Databases (SQL Server)
教學課程:搭配使用 Microsoft Azure Blob 儲存體服務和 SQL Server 2016 資料庫Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases