SQL Server 备份到 URLSQL Server Backup to URL

适用于: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否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:

SecuritySecurity

以下是备份到 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 要求将 Azure 帐户名称和访问密钥身份验证或共享访问签名和访问令牌存储在 SQL ServerSQL Server 凭据中。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 存储服务和从中读取 blob,也可以使用特定容器上生成的共享访问签名令牌授予它读取和写入权限。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 和页 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. 你可以在容器上生成共享访问签名令牌,并只授予其对特定容器上的对象的访问权限。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 和页 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. Blob 可使用以下 URL 格式寻址: https://<storage account>.blob.core.windows.net/<container>/<blob>。Blobs are addressable using the following URL format: https://<storage account>.blob.core.windows.net/<container>/<blob>. 有关 Microsoft Azure Blob 存储服务的详细信息,请参阅 如何通过 .NET 使用 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 组件SQL 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 不存在,则创建它。If the blob does not exist, it is created. 如果指定了现有 blob,则除非指定了“WITH FORMAT”选项以覆盖 blob 中的现有备份文件,否则备份将失败。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/<CONTAINER>/<FILENAME.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 及其共享访问签名令牌。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 代理的代理For 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 块 * 4 MB 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 备份或还原向导发出备份或还原语句。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. 只能使用 WITH FORMAT 选项覆盖到现有 blob 的备份。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) 令牌(而不是存储帐户密钥)作为 SQL 凭据时才支持通过单个备份操作备份到多个 blob。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. 对于用于指定 URL“https://.blob.core.windows.net//.bak”所需的元素,BACKUP TO URL 占用 36 个字符,其余 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.

对备份/还原语句的支持Support for Backup/Restore Statements

备份/还原语句Backup/Restore Statement 支持Supported 例外Exceptions 注释Comments
备份BACKUP YY 支持将 BLOCKSIZE 和 MAXTRANSFERSIZE 用于块 blob。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 (Transact-SQL)For syntax and general information about backup statements, see BACKUP (Transact-SQL).

有关还原语句的语法和一般信息,请参阅 RESTORE (Transact-SQL)For syntax and general information about restore statements, see RESTORE (Transact-SQL).

对备份参数的支持Support for Backup Arguments

参数Argument 支持Supported 异常Exception 注释Comments
DATABASEDATABASE YY
日志LOG 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 CREDENTIALWITH 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
名称NAME 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
统计信息STATS YY
REWIND | NOREWINDREWIND | NOREWIND -
UNLOAD | NOUNLOADUNLOAD | NOUNLOAD -
NORECOVERY | STANDBYNORECOVERY | STANDBY YY
NO_TRUNCATENO_TRUNCATE YY

有关备份参数的详细信息,请参阅 BACKUP (Transact-SQL)For more information about backup arguments, see BACKUP (Transact-SQL).

对还原参数的支持Support for Restore Arguments

参数Argument 支持Supported 例外Exceptions 注释Comments
DATABASEDATABASE YY
日志LOG 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
统计信息STATS 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 参数 (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 凭据通过 SQL Server Management Studio 中的备份任务将数据库备份到 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.

以下步骤介绍为了能够备份到 Azure 存储而对 SQL Server Management Studio 中的“备份数据库”任务做出的更改: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 数据库引擎实例,然后展开该实例。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 作为一个目标选项,以及 SQL 凭据等备份到 Azure 存储所需的其他支持对象。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 中的“定义备份任务”部分 。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. 从“备份媒体类型:” 下拉列表中选择 URLSelect 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.

备注

有关将 Microsoft Azure Blob 存储服务和 SQL Server 2016 配合使用的教程,请参见教程:将 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

下面的示例创建用于向 Microsoft Azure Blob 存储服务进行身份验证的 SQL ServerSQL Server 凭据。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. To URL 使用共享访问签名To URL using Shared Access Signature
BACKUP DATABASE AdventureWorks2016   
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak';  
GO   
  1. To 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. From 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