BACKUP (Transact-SQL)BACKUP (Transact-SQL)

备份 SQL 数据库。Backs up a SQL database.

单击以下选项卡之一,了解所使用的特定 SQL 版本的语法、参数、注解、权限和示例。Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular SQL version with which you are working.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

单击一个产品!Click a product!

在下一行中,单击你感兴趣的产品名称。In the following row, click whichever product name you are interested in. 单击时此处会显示适合你单击的任何产品的不同内容:The click displays different content here, appropriate for whichever product you click:

* SQL Server *  * SQL Server *   SQL 数据库
托管实例
SQL Database
managed instance
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

SQL ServerSQL Server

备份完整的 SQL ServerSQL Server 数据库以创建数据库备份,或者备份数据库的一个或多个文件或文件组以创建文件备份 (BACKUP DATABASE)。Backs up a complete SQL ServerSQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). 另外,在完整恢复模式或大容量日志恢复模式下备份数据库事务日志以创建日志备份 (BACKUP LOG)。Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG).

语法Syntax

--Backing Up a Whole Database
BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL
           | <general_WITH_options> [ ,...n ] } ]
[;]

--Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name | @database_name_var }
 <file_or_filegroup> [ ,...n ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Creating a Partial Backup
BACKUP DATABASE { database_name | @database_name_var }
 READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

--Backing Up the Transaction Log (full and bulk-logged recovery models)
BACKUP LOG
  { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { <general_WITH_options> | \<log-specific_optionspec> } [ ,...n ] ]
[;]

<backup_device>::=
 {
  { logical_device_name | @logical_device_name_var }
 | {   DISK
     | TAPE
     | URL } =
     { 'physical_device_name' | @physical_device_name_var | 'NUL' }
 }

<MIRROR TO clause>::=
 MIRROR TO <backup_device> [ ,...n ]

<file_or_filegroup>::=
 {
   FILE = { logical_file_name | @logical_file_name_var }
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
 }

<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

<general_WITH_options> [ ,...n ]::=
--Backup Set Options
   COPY_ONLY
 | { COMPRESSION | NO_COMPRESSION }
 | DESCRIPTION = { 'text' | @text_variable }
 | NAME = { backup_set_name | @backup_set_name_var }
 | CREDENTIAL
 | ENCRYPTION
 | FILE_SNAPSHOT
 | { EXPIREDATE = { 'date' | @date_var }
        | RETAINDAYS = { days | @days_var } }

--Media Set Options
   { NOINIT | INIT }
 | { NOSKIP | SKIP }
 | { NOFORMAT | FORMAT }
 | MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Tape Options
   { REWIND | NOREWIND }
 | { UNLOAD | NOUNLOAD }

--Log-specific Options
   { NORECOVERY | STANDBY = undo_file_name }
 | NO_TRUNCATE

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   `SERVER CERTIFICATE` = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

参数Arguments

DATABASE 指定一个完整数据库备份。DATABASE Specifies a complete database backup. 如果指定了一个文件和文件组的列表,则仅备份该列表中的文件和文件组。If a list of files and filegroups is specified, only those files and filegroups are backed up. 在进行完整数据库备份或差异数据库备份的过程中,SQL ServerSQL Server 会备份足够多的事务日志,以便在还原备份时生成一个一致的数据库。During a full or differential database backup, SQL ServerSQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.

还原由 BACKUP DATABASE(“数据备份” )创建的备份时,将还原整个备份。When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. 只有日志备份才能还原到备份中的特定时间或事务。Only a log backup can be restored to a specific time or transaction within the backup.

备注

对 master 数据库,只能执行完整数据库备份。Only a full database backup can be performed on the master database.

LOGLOG

指定仅备份事务日志。Specifies a backup of the transaction log only. 该日志是从上一次成功执行的日志备份到当前日志的末尾。The log is backed up from the last successfully executed log backup to the current end of the log. 必须创建完整备份,才能创建第一个日志备份。Before you can create the first log backup, you must create a full backup.

通过在 RESTORE LOG 语句中指定 WITH STOPATSTOPATMARKSTOPBEFOREMARK,可以将日志备份还原到备份中的特定时间或事务。You can restore a log backup to a specific time or transaction within the backup by specifying WITH STOPAT, STOPATMARK, or STOPBEFOREMARK in your RESTORE LOG statement.

备注

执行典型日志备份后,如果没有指定 WITH NO_TRUNCATECOPY_ONLY,某些事务日志记录将变为不活动状态。After a typical log backup, some transaction log records become inactive, unless you specify WITH NO_TRUNCATE or COPY_ONLY. 一个或多个虚拟日志文件中的所有记录变为不活动状态后,日志将被截断。The log is truncated after all the records within one or more virtual log files become inactive. 如果日志在常规日志备份后未被截断,则可能是某些操作延迟了日志截断。If the log is not being truncated after routine log backups, something might be delaying log truncation. 有关详细信息,请参阅可能延迟日志截断的因素For more information, see Factors that can delay log truncation.

{ database_name | @ database_name_var } 是备份事务日志、部分数据库或完整的数据库时所用的源数据库。{ database_name | @database_name_var } Is the database from which the transaction log, partial database, or complete database is backed up. 如果作为变量 (@ database_name_var ) 提供,则可以将此名称指定为字符串常量 (@ database_name_var =database name ) 或指定为字符串数据类型(ntext 或 text 数据类型除外)的变量。If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.

备注

不能备份数据库镜像伙伴关系中的镜像数据库。The mirror database in a database mirroring partnership cannot be backed up.

<file_or_filegroup> [ , ...n ] 只能与 BACKUP DATABASE 一起使用,用于指定某个数据库文件或文件组包含在文件备份中,或指定某个只读文件或文件组包含在部分备份中。<file_or_filegroup> [ ,...n ] Used only with BACKUP DATABASE, specifies a database file or filegroup to include in a file backup, or specifies a read-only file or filegroup to include in a partial backup.

FILE = { logical_file_name | @ logical_file_name_var } 文件或变量的逻辑名称,其值等于要包含在备份中的文件的逻辑名称。FILE = { logical_file_name | @logical_file_name_var } Is the logical name of a file or a variable whose value equates to the logical name of a file that is to be included in the backup.

FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } 文件组或变量的逻辑名称,其值等于要包含在备份中的文件组的逻辑名称。FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } Is the logical name of a filegroup or a variable whose value equates to the logical name of a filegroup that is to be included in the backup. 在简单恢复模式下,只允许对只读文件组执行文件组备份。Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.

备注

如果数据库的大小和性能要求使得进行数据库备份不切实际,则应考虑使用文件备份。Consider using file backups when the database size and performance requirements make a database backup impractical. NUL 设备可用于测试备份的性能,但不应在生产环境中使用。The NUL device can be used to test the performance of backups, but should not be used in production environments.

n 一个占位符,表示可以在逗号分隔的列表中指定多个文件和文件组。n Is a placeholder that indicates that multiple files and filegroups can be specified in a comma-separated list. 数量不受限制。The number is unlimited.

有关详细信息,请参阅完整文件备份备份文件和文件组For more information, see Full File Backups and Back Up Files and Filegroups.

READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } [ , ...n ] ] 指定部分备份。READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ] Specifies a partial backup. 部分备份包括数据库中的所有读/写文件:主文件组和任何读/写辅助文件组,以及任何指定的只读文件或文件组。A partial backup includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups.

READ_WRITE_FILEGROUPS 指定在部分备份中备份所有读/写文件组。READ_WRITE_FILEGROUPS Specifies that all read/write filegroups be backed up in the partial backup. 如果数据库是只读的,则 READ_WRITE_FILEGROUPS 仅包括主文件组。If the database is read-only, READ_WRITE_FILEGROUPS includes only the primary filegroup.

重要

使用 FILEGROUP 而不是 READ_WRITE_FILEGROUPS 显式列出读/写文件组将会创建文件备份。Explicitly listing the read/write filegroups by using FILEGROUP instead of READ_WRITE_FILEGROUPS creates a file backup.

FILEGROUP = { logical_filegroup_name | @ logical_filegroup_name_var } 只读文件组或变量的逻辑名称,其值等于要包含在部分备份中的只读文件组的逻辑名称。FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } Is the logical name of a read-only filegroup or a variable whose value equates to the logical name of a read-only filegroup that is to be included in the partial backup. 有关详细信息,请参阅本主题前面的“<file_or_filegroup>”。For more information, see "<file_or_filegroup>," earlier in this topic.

n 一个占位符,表示可以在逗号分隔的列表中指定多个只读文件组。n Is a placeholder that indicates that multiple read-only filegroups can be specified in a comma-separated list.

有关部分备份的详细信息,请参阅部分备份For more information about partial backups, see Partial Backups.

TO <backup_device> [ ,...n ] 指示附带的备份设备集是一个未镜像的媒体集,或者是镜像媒体集中的第一批镜像(为其声明了一个或多个 MIRROR TO 子句) 。TO <backup_device> [ ,...n ] Indicates that the accompanying set of backup devices is either an unmirrored media set or the first of the mirrors within a mirrored media set (for which one or more MIRROR TO clauses are declared).

<backup_device><backup_device>

指定用于备份操作的逻辑备份设备或物理备份设备。Specifies a logical or physical backup device to use for the backup operation.

{ logical_device_name | @logical\_device\_name\_var } 适用范围: SQL Server 要将数据库备份到的备份设备的逻辑名称。{ logical_device_name | @logical_device_name_var } Applies to: SQL Server Is the logical name of the backup device to which the database is backed up. 逻辑名称必须遵守标识符规则。The logical name must follow the rules for identifiers. 如果作为变量 (@logical_device_name_var ) 提供,则可以将该备份设备名称指定为字符串常量(@logical\_device\_name\_var = 逻辑备份设备名称)或任何字符串数据类型(ntext 或 text 数据类型除外)的变量。If supplied as a variable (@logical_device_name_var), the backup device name can be specified either as a string constant (@logical_device_name_var= logical backup device name) or as a variable of any character string data type except for the ntext or text data types.

{ DISK | TAPE | URL} = { 'physical_device_name' | @physical\_device\_name\_var | 'NUL' } 适用范围: 磁盘、磁带和用于 SQL Server 的 URL。{ DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' } Applies to: DISK, TAPE, and URL apply to SQL Server. 指定磁盘文件或磁带设备,或者 Microsoft Azure 存储服务。Specifies a disk file or tape device, or a Microsoft Azure Blob storage service. 此 URL 格式用于创建到 Microsoft Azure 存储服务的备份。The URL format is used for creating backups to the Microsoft Azure storage service. 有关详细信息和示例,请参阅使用 Microsoft Azure Blob 存储服务进行 SQL Server 备份和还原For more information and examples, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 如需教程,请参阅教程:将 SQL Server 备份和还原到 Microsoft Azure Blob 存储服务For a tutorial, see Tutorial: SQL Server Backup and Restore to Microsoft Azure Blob Storage Service.

备注

NUL 磁盘设备将弃用发送给它的所有信息,且仅应用于测试。The NUL disk device will discard all information sent to it and should only be used for testing. 这不适用于生产用途。This is not for production use.

重要

SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2014 (12.x)SQL Server 2014 (12.x),备份到 URL 时只能备份到单个设备。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2014 (12.x)SQL Server 2014 (12.x), you can only backup to a single device when backing up to URL. 备份到 URL 时,若要备份到多个设备,必须使用 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 以及共享访问签名 (SAS) 令牌。In order to backup to multiple devices when backing up to URL, you must use SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and you must use Shared Access Signature (SAS) tokens. 有关创建共享访问签名的示例,请参阅 SQL Server 备份到 URL使用 Powershell 简化在 Azure 存储空间中使用共享访问签名 (SAS) 令牌创建 SQL 凭据的过程For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.

URL 适用范围SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2017SQL Server 2017)。URL applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017SQL Server 2017).

如果某一磁盘设备不存在,也可以在 BACKUP 语句中指定它。A disk device does not have to exist before it is specified in a BACKUP statement. 如果存在物理设备且 BACKUP 语句中未指定 INIT 选项,则备份将追加到该设备。If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

备注

NUL 设备将弃用发送到此文件的所有输入,但备份仍将所有页面标记为备份。The NUL device will discard all input sent to this file, however the backup will still mark all pages as backed up.

有关详细信息,请参阅 备份设备For more information, see Backup Devices.

备注

SQL ServerSQL Server 的未来版本中将删除 TAPE 选项。The TAPE option will be removed in a future version of SQL ServerSQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

n 一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.

MIRROR TO <backup_device> [ ,...n ] 指定一组辅助备份设备(最多三个),其中每个设备都镜像 TO 子句中指定的备份设备 。MIRROR TO <backup_device> [ ,...n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. MIRROR TO 子句和 TO 子句必须指定相同类型和数量的备份设备。The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. 最多可以使用三个 MIRROR TO 子句。The maximum number of MIRROR TO clauses is three.

此选项仅在 SQL ServerSQL Server 的 Enterprise 版中可用。This option is available only in the Enterprise edition of SQL ServerSQL Server.

备注

对于 MIRROR TO = DISK,BACKUP 自动基于磁盘的扇区大小来决定磁盘设备合适的块大小。For MIRROR TO = DISK, BACKUP automatically determines the appropriate block size for disk devices based on the sector size of the disk. 如果使用与指定为主备份设备的磁盘不同的扇区大小格式化 MIRROR TO 磁盘,则备份命令将失败。If the MIRROR TO disk is formatted with a different sector size than the disk specified as the primary backup device, the backup command will fail. 为了将备份镜像到具有不同扇区大小的设备,必须指定 BLOCKSIZE 参数,并且应将其设置为所有目标设备中的最大扇区大小。In order to mirror backups to devices that have different sector sizes, the BLOCKSIZE parameter must be specified, and should be set to the highest sector size amongst all the target devices. 有关块大小的详细信息,请参阅此主题后面的 "BLOCKSIZE"。For more information about block size, see "BLOCKSIZE" later in this topic.

有关 <backup_device> 的信息,请参阅本部分前面的“<backup_device>”。<backup_device> See "<backup_device>," earlier in this section.

n 一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list. MIRROR TO 子句中的设备数必须等于 TO 子句中的设备数。The number of devices in the MIRROR TO clause must equal the number of devices in the TO clause.

有关详细信息,请参阅本主题后面备注部分中的“镜像媒体集中的媒体簇”。For more information, see "Media Families in Mirrored Media Sets" in the Remarks section, later in this topic.

[ next-mirror-to ] 一个占位符,表示一个 BACKUP 语句除了包含一个 TO 子句外,最多还可包含三个 MIRROR TO 子句。[ next-mirror-to ] Is a placeholder that indicates that a single BACKUP statement can contain up to three MIRROR TO clauses, in addition to the single TO clause.

WITH 选项WITH Options

指定要用于备份操作的选项。Specifies options to be used with a backup operation.

CREDENTIAL 适用范围 :SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2017SQL Server 2017)。CREDENTIAL Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 through SQL Server 2017SQL Server 2017). 仅在创建到 Microsoft Azure Blob 存储服务的备份时使用。Used only when creating a backup to the Microsoft Azure Blob storage service.

FILE_SNAPSHOT 适用范围:SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017) 。FILE_SNAPSHOT Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).

用于在使用 Azure Blob 存储服务存储所有 SQL Server 数据库文件时,创建数据库文件的 Azure 快照。Used to create an Azure snapshot of the database files when all of the SQL Server database files are stored using the Azure Blob storage service. 有关详细信息,请参阅 Microsoft Azure 中的 SQL Server 数据文件For more information, see SQL Server Data Files in Microsoft Azure. SQL ServerSQL Server 快照备份将数据库文件(数据和日志文件)的 Azure 快照保持为一致的状态。Snapshot Backup takes Azure snapshots of the database files (data and log files) at a consistent state. 一组一致的 Azure 快照构成备份并记录在备份文件中。A consistent set of Azure snapshots make up a backup and are recorded in the backup file. BACKUP DATABASE TO URL WITH FILE_SNAPSHOTBACKUP LOG TO URL WITH FILE_SNAPSHOT 之间的唯一区别是后者会截断事务日志,而前者不会。The only difference between BACKUP DATABASE TO URL WITH FILE_SNAPSHOT and BACKUP LOG TO URL WITH FILE_SNAPSHOT is that the latter also truncates the transaction log while the former does not. 借助 SQL ServerSQL Server 快照备份,在完成 SQL ServerSQL Server 建立备份链时所需的初始完整备份之后,只需单个事务日志备份即可将数据库还原到事务日志备份的时间点。With SQL ServerSQL Server Snapshot Backup, after the initial full backup that is required by SQL ServerSQL Server to establish the backup chain, only a single transaction log backup is required to restore a database to the point in time of the transaction log backup. 此外,只需两次事务日志备份即可将数据库还原到两次事务日志备份之间的时间点。Furthermore, only two transaction log backups are required to restore a database to a point in time between the time of the two transaction log backups.

DIFFERENTIALDIFFERENTIAL

只能与 BACKUP DATABASE 一起使用,指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup. 差异备份一般会比完整备份占用更少的空间。A differential backup usually takes up less space than a full backup. 对于上一次完整备份后执行的所有单个日志备份,使用该选项可以不必再进行备份。Use this option so that all individual log backups performed since the last full backup do not have to be applied.

备注

默认情况下,BACKUP DATABASE 创建完整备份。By default, BACKUP DATABASE creates a full backup.

有关详细信息,请参阅 差异备份For more information, see Differential Backups.

ENCRYPTION 用于指定将备份加密。ENCRYPTION Used to specify encryption for a backup. 可指定加密备份所用的加密算法,或指定 NO_ENCRYPTION 以不加密备份。You can specify an encryption algorithm to encrypt the backup with or specify NO_ENCRYPTION to not have the backup encrypted. 建议进行加密以帮助保护备份文件的安全。Encryption is recommended practice to help secure backup files. 可指定的算法的列表如下:The list of algorithms you can specify are:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果决定加密,则还必须使用加密程序选项指定加密程序:If you choose to encrypt you will also have to specify the encryptor using the encryptor options:

  • SERVER CERTIFICATE = Encryptor_NameSERVER CERTIFICATE = Encryptor_Name
  • SERVER ASYMMETRIC KEY = Encryptor_NameSERVER ASYMMETRIC KEY = Encryptor_Name

SERVER CERTIFICATESERVER ASYMMETRIC KEY 是在 master 数据库中创建的证书和非对称密钥。The SERVER CERTIFICATE and SERVER ASYMMETRIC KEY are a certificate and an asymmetric key created in master database. 有关详细信息,请分别参阅 CREATE CERTIFICATECREATE ASYMMETRIC KEYFor more information see CREATE CERTIFICATE and CREATE ASYMMETRIC KEY respectively.

警告

将加密与 FILE_SNAPSHOT 参数一起使用时,使用指定的加密算法对元数据文件本身进行加密,并且系统会验证是否已完成对数据库的透明数据加密 (TDE)When encryption is used in conjunction with the FILE_SNAPSHOT argument, the metadata file itself is encrypted using the specified encryption algorithm and the system verifies that Transparent Data Encryption (TDE) was completed for the database. 不会再对数据本身进行其他加密。No additional encryption happens for the data itself. 如果未加密数据库或发出备份语句之前未完成加密,则备份失败。The backup fails if the database was not encrypted or if the encryption was not completed before the backup statement was issued.

备份集选项Backup Set Options

这些选项对此备份操作创建的备份集进行操作。These options operate on the backup set that is created by this backup operation.

备注

若要为还原操作指定备份集,请使用 FILE = <backup_set_file_number> 选项。To specify a backup set for a restore operation, use the FILE = <backup_set_file_number> option. 有关如何指定备份集的详细信息,请参阅 RESTORE 参数 中的“指定备份集”。For more information about how to specify a backup set, see "Specifying a Backup Set" in RESTORE Arguments.

COPY_ONLY 指定备份为“仅复制备份” ,该备份不影响正常的备份顺序。COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. 仅复制备份是独立于定期计划的常规备份而创建的。A copy-only backup is created independently of your regularly scheduled, conventional backups. 仅复制备份不会影响数据库的总体备份和还原过程。A copy-only backup does not affect your overall backup and restore procedures for the database.

应在出于特殊目的而进行备份的情况下使用仅复制备份,例如在进行联机文件还原前备份日志。Copy-only backups should be used in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. 通常,仅复制日志备份仅使用一次即被删除。Typically, a copy-only log backup is used once and then deleted.

  • BACKUP DATABASE 一起使用时,COPY_ONLY 选项创建的完整备份不能用作差异基准。When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. 差异位图不会被更新,因此差异备份的表现就像仅复制备份不存在一样。The differential bitmap is not updated, and differential backups behave as if the copy-only backup does not exist. 后续差异备份将最新的常规完整备份用作它们的基准。Subsequent differential backups use the most recent conventional full backup as their base.

    重要

    如果将 DIFFERENTIALCOPY_ONLY 一起使用,则忽略 COPY_ONLY 并创建差异备份。If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created.

  • BACKUP LOG 一起使用时,COPY_ONLY 选项将创建“仅复制日志备份” ,该备份不会截断事务日志。When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. 仅复制日志备份对日志链没有任何影响,因此其他日志备份的表现就像仅复制备份不存在一样。The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.

有关详细信息,请参阅仅复制备份For more information, see Copy-Only Backups.

{ COMPRESSION | NO_COMPRESSION } 仅适用于 SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 和更高版本;指定是否对此备份执行备份压缩,覆盖服务器级默认设置。{ COMPRESSION | NO_COMPRESSION } In SQL Server 2008 EnterpriseSQL Server 2008 Enterprise and later versions only, specifies whether backup compression is performed on this backup, overriding the server-level default.

安装时,默认行为是不进行备份压缩。At installation, the default behavior is no backup compression. 但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。But this default can be changed by setting the backup compression default server configuration option. 有关查看此选项的当前值的信息,请参阅查看或更改服务器属性面板For information about viewing the current value of this option, see View or Change Server Properties.

有关对支持透明数据加密 (TDE) 的数据库使用备份压缩的信息,请参阅备注部分。For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.

COMPRESSION 显式启用备份压缩。COMPRESSION Explicitly enables backup compression.

NO_COMPRESSION 显式禁用备份压缩。NO_COMPRESSION Explicitly disables backup compression.

DESCRIPTION = { ' text ' | @ text_variable } 指定说明备份集的自由格式文本。DESCRIPTION = { 'text' | @text_variable } Specifies the free-form text describing the backup set. 该字符串最长可达 255 个字符。The string can have a maximum of 255 characters.

NAME = { backup_set_name | @ backup_set_var } 指定备份集的名称。NAME = { backup_set_name | @backup_set_var } Specifies the name of the backup set. 名称最长可达 128 个字符。Names can have a maximum of 128 characters. 如果未指定 NAME,它将为空。If NAME is not specified, it is blank.

{ EXPIREDATE =' date ' | RETAINDAYS = days } 指定允许覆盖该备份的备份集的日期。{ EXPIREDATE ='date' | RETAINDAYS = days } Specifies when the backup set for this backup can be overwritten. 如果同时使用这两个选项,RETAINDAYS 的优先级别将高于 EXPIREDATE。If these options are both used, RETAINDAYS takes precedence over EXPIREDATE.

如果这两个选项均未指定,则过期日期由 mediaretention 配置设置确定。If neither option is specified, the expiration date is determined by the mediaretention configuration setting. 有关详细信息,请参阅服务器配置选项For more information, see Server Configuration Options.

重要

这些选项仅仅阻止 SQL ServerSQL Server 覆盖文件。These options only prevent SQL ServerSQL Server from overwriting a file. 用其他方法仍可擦除磁带,而通过操作系统也可以删除磁盘文件。Tapes can be erased using other methods, and disk files can be deleted through the operating system. 有关过期验证的详细信息,请参阅本主题中的 SKIP 和 FORMAT。For more information about expiration verification, see SKIP and FORMAT in this topic.

EXPIREDATE = { ' date ' | @ date_var } 指定备份集到期和允许被覆盖的日期。EXPIREDATE = { 'date' | @date_var } Specifies when the backup set expires and can be overwritten. 如果作为变量 (@date\_var ) 提供,则该日期必须采用已配置系统日期/时间 的格式,并指定为下列类型之一:If supplied as a variable (@date_var), this date must follow the configured system datetime format and be specified as one of the following:

  • 字符串常量 (@date\_var = date)A string constant (@date_var = date)
  • 字符串数据类型(ntext 或 text 数据类型除外)的变量A variable of character string data type (except for the ntext or text data types)
  • smalldatetime A smalldatetime
  • datetime 变量A datetime variable

例如:For example:

  • 'Dec 31, 2020 11:59 PM'
  • '1/1/2021'

有关如何指定 datetime 值的信息,请参阅日期和时间类型For information about how to specify datetime values, see Date and Time Types.

备注

若要忽略过期日期,请使用 SKIP 选项。To ignore the expiration date, use the SKIP option.

RETAINDAYS = { days | @ days_var } 指定必须经过多少天才可以覆盖该备份媒体集。RETAINDAYS = { days | @days_var } Specifies the number of days that must elapse before this backup media set can be overwritten. 如果作为变量 (@ days_var ) 提供,则必须指定为整数。If supplied as a variable (@days_var), it must be specified as an integer.

媒体集选项Media Set Options

这些选项作为一个整体对介质集进行操作。These options operate on the media set as a whole.

{ NOINIT | INIT } 控制备份操作是追加到还是覆盖备份媒体中的现有备份集。{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. 默认为追加到介质中最新的备份集 (NOINIT)。The default is to append to the most recent backup set on the media (NOINIT).

备注

有关 { NOINIT | INIT } and { NOSKIP | SKIP } 之间交互的信息,请参阅本主题后面的备注For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see Remarks later in this topic.

NOINIT 表示备份集将追加到指定的媒体集上,以保留现有的备份集。NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. 如果为介质集定义了介质密码,则必须提供密码。If a media password is defined for the media set, the password must be supplied. NOINIT 是默认设置。NOINIT is the default.

有关详细信息,请参阅 媒体集、媒体簇和备份集For more information, see Media Sets, Media Families, and Backup Sets.

INIT 指定应覆盖所有备份集,但是保留媒体标头。INIT Specifies that all backup sets should be overwritten, but preserves the media header. 如果指定了 INIT,将覆盖该设备上所有现有的备份集(如果条件允许)。If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. 默认情况下,BACKUP 将检查下列条件,如果其中的任一条件存在,都不会覆盖备份介质:By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

  • 所有备份集都未过期。Any backup set has not yet expired. 有关详细信息,请参阅 EXPIREDATERETAINDAYS 选项。For more information, see the EXPIREDATE and RETAINDAYS options.
  • 如果 BACKUP 语句给出了备份集名,则该备份集名与备份介质上的名称不匹配。The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. 有关详细信息,请参阅本部分前面介绍的 NAME 选项。For more information, see the NAME option, earlier in this section.

若要替代这些检查,请使用 SKIP 选项。To override these checks, use the SKIP option.

有关详细信息,请参阅 媒体集、媒体簇和备份集For more information, see Media Sets, Media Families, and Backup Sets.

{ NOSKIP | SKIP } 控制备份操作是否在覆盖媒体中的备份集之前检查它们的过期日期和时间。{ NOSKIP | SKIP } Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

备注

有关 { NOINIT | INIT } and { NOSKIP | SKIP } 之间交互的信息,请参阅本主题后面的“备注”。For information about the interactions between { NOINIT | INIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

NOSKIP 指示 BACKUP 语句在可以覆盖媒体上的所有备份集之前先检查它们的过期日期。NOSKIP Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. 这是默认行为。This is the default behavior.

SKIP 禁用备份集的过期和名称检查,这些检查一般由 BACKUP 语句执行以防覆盖备份集。SKIP Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. 有关 { NOINIT | INIT } 和 { NOSKIP | SKIP } 之间交互的信息,请参阅本主题后面的“备注”。For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic. 若要查看备份集的过期日期,请查询 backupset 历史记录表的 expiration_date 列。To view the expiration dates of backup sets, query the expiration_date column of the backupset history table.

{ NOFORMAT | FORMAT } 指定是否应该在用于此备份操作的卷上写入媒体标头,以覆盖任何现有的媒体标头和备份集。{ NOFORMAT | FORMAT } Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.

NOFORMAT 指定备份操作在用于此备份操作的媒体卷上保留现的有媒体标头和备份集。NOFORMAT Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. 这是默认行为。This is the default behavior.

FORMAT 指定创建新的媒体集。FORMAT Specifies that a new media set be created. FORMAT 将使备份操作在用于备份操作的所有介质卷上写入新的介质标头。FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. 卷的现有内容将变为无效,因为覆盖了任何现有的介质标头和备份集。The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

重要

请谨慎使用 FORMATUse FORMAT carefully. 格式化介质集的任何一个卷都将使整个介质集不可用。Formatting any volume of a media set renders the entire media set unusable. 例如,如果初始化现有条带介质集中的单个磁带,则整个介质集都将变得不可用。For example, if you initialize a single tape belonging to an existing striped media set, the entire media set is rendered useless.

指定 FORMAT 即表示 SKIPSKIP 无需显式声明。Specifying FORMAT implies SKIP; SKIP does not need to be explicitly stated.

MEDIADESCRIPTION = { text | @ text_variable } 指定媒体集的自由格式文本说明,最多为 255 个字符。MEDIADESCRIPTION = { text | @text_variable } Specifies the free-form text description, maximum of 255 characters, of the media set.

MEDIANAME = { media_name | @ media_name_variable } 指定整个备份媒体集的媒体名称。MEDIANAME = { media_name | @media_name_variable } Specifies the media name for the entire backup media set. 介质名称的长度不能多于 128 个字符,如果指定了 MEDIANAME,则该名称必须匹配备份卷上已存在的先前指定的介质名称。The media name must be no longer than 128 characters, If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volumes. 如果未指定该选项或指定了 SKIP 选项,将不会对介质名称进行验证检查。If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.

BLOCKSIZE = { blocksize | @ blocksize_variable } 指定物理块大小(以字节为单位)。BLOCKSIZE = { blocksize | @blocksize_variable } Specifies the physical block size, in bytes. 支持的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字节。The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. 对于磁带设备默认为 65536,其他情况为 512。The default is 65536 for tape devices and 512 otherwise. 通常,由于 BACKUP 自动选择适合于设备的块大小,因此不需要此选项。Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. 显式声明块大小将覆盖自动选择块大小。Explicitly stating a block size overrides the automatic selection of block size.

如果要建立一个计划在 CD-ROM 上进行复制和还原的备份,请指定 BLOCKSIZE=2048。If you are taking a backup that you plan to copy onto and restore from a CD-ROM, specify BLOCKSIZE=2048.

备注

通常,只有写入磁带设备时,此选项才会影响性能。This option typically affects performance only when writing to tape devices.

数据传输选项Data Transfer Options

BUFFERCOUNT = { buffercount | @ buffercount_variable } 指定用于备份操作的 I/O 缓冲区总数。BUFFERCOUNT = { buffercount | @buffercount_variable } Specifies the total number of I/O buffers to be used for the backup operation. 可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

缓冲区使用的总空间是由以下公式确定:BUFFERCOUNT * MAXTRANSFERSIZEThe total space used by the buffers is determined by: BUFFERCOUNT * MAXTRANSFERSIZE.

备注

有关使用 BUFFERCOUNT 选项的重要信息,请参阅不正确的 BufferCount 数据传输选项可导致 OOM 情况博客。For important information about using the BUFFERCOUNT option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } 指定要在 SQL ServerSQL Server 和备份介质之间使用的最大传输单元(字节)。MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } Specifies the largest unit of transfer in bytes to be used between SQL ServerSQL Server and the backup media. 可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

备注

使用 SQL 编写器服务创建备份时,如果数据库已配置 FILESTREAM(文件流)或包含内存优化文件组,则恢复时的 MAXTRANSFERSIZE 应大于或等于创建备份时使用的 MAXTRANSFERSIZEWhen creating backups by using the SQL Writer Service, if the database has configured FILESTREAM, or includes memory optimized filegroups, then the MAXTRANSFERSIZE at the time of a restore should be greater than or equal to the MAXTRANSFERSIZE that was used when the backup was created.

备注

对于具有单个数据文件且支持透明数据加密 (TDE) 的数据库,默认 MAXTRANSFERSIZE 为 65536 (64 KB)。For Transparent Data Encryption (TDE) enabled databases with a single data file, the default MAXTRANSFERSIZE is 65536 (64 KB). 对于非 TDE 加密数据库,使用备份到 DISK 时,默认 MAXTRANSFERSIZE 为 1048576 (1 MB),使用 VDI 或 TAPE.时为 65536 (64 KB)。For non-TDE encrypted databases the default MAXTRANSFERSIZE is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE. 有关对 TDE 加密数据库使用备份压缩的详细信息,请参阅备注部分。For more information about using backup compression with TDE encrypted databases, see the Remarks section.

错误管理选项Error Management Options

使用这些选项可以确定是否为备份操作启用了备份校验和,以及备份操作是否在遇到错误时停止。These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.

{ NO_CHECKSUM | CHECKSUM } 控制是否启用备份校验和。{ NO_CHECKSUM | CHECKSUM } Controls whether backup checksums are enabled.

NO_CHECKSUM 显式禁用备份校验和的生成(以及页校验和的验证)。NO_CHECKSUM Explicitly disables the generation of backup checksums (and the validation of page checksums). 这是默认行为。This is the default behavior.

CHECKSUM 如果此选项已启用并且可用,则指定备份操作将验证每页的校验和及页残缺,并生成整个备份的校验和。CHECKSUM Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

使用备份校验和可能会影响工作负荷以及备份吞吐量。Using backup checksums may affect workload and backup throughput.

有关详细信息,请参阅在备份和还原期间可能的媒体错误For more information, see Possible Media Errors During Backup and Restore.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 控制备份操作在遇到页校验和错误后是停止还是继续。{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } Controls whether a backup operation stops or continues after encountering a page checksum error.

STOP_ON_ERROR 如果未验证页校验和,则指示 BACKUP 失败。STOP_ON_ERROR Instructs BACKUP to fail if a page checksum does not verify. 这是默认行为。This is the default behavior.

CONTINUE_AFTER_ERROR 指示 BACKUP 继续执行,不管是否遇到无效校验和或页撕裂之类的错误。CONTINUE_AFTER_ERROR Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

数据库损坏时,如果无法使用 NO_TRUNCATE 选项备份日志尾部,则可以通过指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

有关详细信息,请参阅在备份和还原期间可能的媒体错误For more information, see Possible Media Errors During Backup and Restore.

兼容性选项Compatibility Options

RESTART 从 SQL Server 2008SQL Server 2008 开始不起作用。RESTART Beginning with SQL Server 2008SQL Server 2008, has no effect. 此版本接受该选项,以便与旧版本的 SQL ServerSQL Server 保持兼容。This option is accepted by the version for compatibility with previous versions of SQL ServerSQL Server.

监视选项Monitoring Options

STATS [ = percentage ] 每当另一个百分比 完成时显示一条消息,并用于测量进度。STATS [ = percentage ] Displays a message each time another percentage completes, and is used to gauge progress. 如果省略百分比 ,则 SQL ServerSQL Server 在每完成 10% 就显示一条消息。If percentage is omitted, SQL ServerSQL Server displays a message after each 10 percent is completed.

STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。The STATS option reports the percentage complete as of the threshold for reporting the next interval. 这是指定百分比的近似值;例如,当 STATS=10 时,如果完成进度为 40%,则该选项可能显示 43%。This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. 对于较大的备份集,这不是问题,因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

磁带选项Tape Options

这些选项只用于 TAPE 设备。These options are used only for TAPE devices. 如果使用的是非磁带设备,则会忽略这些选项。If a nontape device is being used, these options are ignored.

{ REWIND | NOREWIND } REWIND{ REWIND | NOREWIND } REWIND

指定 SQL ServerSQL Server 释放和倒带磁带。Specifies that SQL ServerSQL Server releases and rewinds the tape. REWIND 是默认设置。REWIND is the default.

NOREWINDNOREWIND

指定在备份操作之后 SQL ServerSQL Server 让磁带一直处于打开状态。Specifies that SQL ServerSQL Server will keep the tape open after the backup operation. 在对磁带执行多个备份操作时,可以使用此选项来帮助改进性能。You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND 包含 NOUNLOAD,并且这些选项在单个 BACKUP 语句中不兼容。NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

备注

如果使用 NOREWIND,则 SQL ServerSQL Server 实例将一直保留磁带机的所有权,直到在同一进程中运行的 BACKUP 或 RESTORE 语句使用 REWINDUNLOAD 选项或服务器实例关闭为止。If you use NOREWIND, the instance of SQL ServerSQL Server retains ownership of the tape drive until a BACKUP or RESTORE statement that is running in the same process uses either the REWIND or UNLOAD option, or the server instance is shut down. 磁带保持打开将防止其他进程访问磁带。Keeping the tape open prevents other processes from accessing the tape. 有关如何显示打开的磁带列表和如何将打开的磁带关闭的信息,请参阅备份设备For information about how to display a list of open tapes and to close an open tape, see Backup Devices.

{ UNLOAD | NOUNLOAD }{ UNLOAD | NOUNLOAD }

备注

UNLOADNOUNLOAD 会话设置可在整个会话期间存在,或者在通过指定其他设置而进行重置之前一直存在。UNLOAD and NOUNLOAD are session settings that persist for the life of the session or until it is reset by specifying the alternative.

UNLOADUNLOAD

指定在备份完成后自动重绕并卸载磁带。Specifies that the tape is automatically rewound and unloaded when the backup is finished. 会话开始时 UNLOAD 是默认值。UNLOAD is the default when a session begins.

NOUNLOADNOUNLOAD

指定在 BACKUP 操作之后磁带继续在磁带机中加载。Specifies that after the BACKUP operation the tape remains loaded on the tape drive.

备注

备份到磁带备份设备时,BLOCKSIZE 选项会影响备份操作的性能。For a backup to a tape backup device, the BLOCKSIZE option to affect the performance of the backup operation. 通常,只有写入磁带设备时,此选项才会影响性能。This option typically affects performance only when writing to tape devices.

特定于日志的选项Log-specific options

这些选项仅与 BACKUP LOG 一起使用。These options are only used with BACKUP LOG.

备注

如果不想进行日志备份,则请使用简单恢复模式。If you do not want to take log backups, use the simple recovery model. 有关详细信息,请参阅恢复模式For more information, see Recovery Models.

{ NORECOVERY | STANDBY = undo_file_name }{ NORECOVERY | STANDBY = undo_file_name }

NORECOVERYNORECOVERY

备份日志的尾部并使数据库处于 RESTORING 状态。Backs up the tail of the log and leaves the database in the RESTORING state. 当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时,NORECOVERY 很有用。NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.

若要执行最大程度的日志备份(跳过日志截断)并自动将数据库置于 RESTORING 状态,请同时使用 NO_TRUNCATENORECOVERY 选项。To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

STANDBY = standby_file_nameSTANDBY = standby_file_name

备份日志的尾部并使数据库处于只读和 STANDBY 状态。Backs up the tail of the log and leaves the database in a read-only and STANDBY state. 将 STANDBY 子句写入备用数据(执行回滚,但需带进一步还原选项)。The STANDBY clause writes standby data (performing rollback, but with the option of further restores). 使用 STANDBY 选项等同于 BACKUP LOG WITH NORECOVERY 后跟 RESTORE WITH STANDBY。Using the STANDBY option is equivalent to BACKUP LOG WITH NORECOVERY followed by a RESTORE WITH STANDBY.

使用备用模式需要一个备用文件,该文件由 standby_file_name 指定,其位置存储于数据库的日志中。Using standby mode requires a standby file, specified by standby_file_name, whose location is stored in the log of the database. 如果指定的文件已经存在,则数据库引擎Database Engine会覆盖该文件;如果指定的文件不存在,则数据库引擎Database Engine将创建它。If the specified file already exists, the 数据库引擎Database Engine overwrites it; if the file does not exist, the 数据库引擎Database Engine creates it. 备用文件将成为数据库的一部分。The standby file becomes part of the database.

该文件将保存对回滚所做的更改,如果要在以后应用 RESTORE LOG 操作,则必须反转这些更改。This file holds the rolled back changes, which must be reversed if RESTORE LOG operations are to be subsequently applied. 必须有足够的磁盘空间供备用文件增长,以使备用文件能够包含数据库中由回滚的未提交事务修改的所有不重复的页。There must be enough disk space for the standby file to grow so that it can contain all the distinct pages from the database that were modified by rolling back uncommitted transactions.

NO_TRUNCATENO_TRUNCATE

指定不截断日志,并使 数据库引擎Database Engine 尝试执行备份,而不考虑数据库的状态。Specifies that the is log not truncated and causes the 数据库引擎Database Engine to attempt the backup regardless of the state of the database. 因此,使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据。Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. 该选项允许在数据库损坏时备份日志。This option allows backing up the log in situations where the database is damaged.

BACKUP LOG 的 NO_TRUNCATE 选项相当于同时指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.

如果不使用 NO_TRUNCATE 选项,则数据库必须处于 ONLINE 状态。Without the NO_TRUNCATE option, the database must be in the ONLINE state. 如果数据库处于 SUSPENDED 状态,则可以通过指定 NO_TRUNCATE 来创建备份。If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. 但是,如果数据库处于 OFFLINE 或 EMERGENCY 状态,即便使用了 NO_TRUNCATE,也不允许执行 BACKUP。But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. 有关数据库状态的详细信息,请参阅数据库状态For information about database states, see Database States.

关于使用 SQL Server 备份About working with SQL Server backups

本节简要说明了以下基本备份概念:This section introduces the following essential backup concepts:

备份类型 事务日志截断 格式化备份媒体 使用备份设备和媒体集 SQL Server 备份Backup Types Transaction Log Truncation Formatting Backup Media Working with Backup Devices and Media Sets Restoring SQL Server Backups

备注

有关 SQL ServerSQL Server 中备份的说明,请参阅备份概述For an introduction to backup in SQL ServerSQL Server, see Backup Overview.

备份类型Backup types

支持的备份类型取决于数据库的恢复模式,如下所示:The supported backup types depend on the recovery model of the database, as follows

  • 所有恢复模式都支持数据的完整备份和差异备份。All recovery models support full and differential backups of data.

    备份范围Scope of backup 备份类型Backup types
    整个数据库Whole database 数据库备份涵盖整个数据库。Database backups cover the whole database.

    或者,每个数据库备份都可以充当由一个或多个差异数据库备份构成的系列的基础。Optionally, each database backup can serve as the base of a series of one or more differential database backups.
    部分数据库Partial database 部分备份涵盖读/写文件组,也可能涵盖一个或多个只读文件或文件组。Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.

    或者,每个部分备份都可以充当由一个或多个差异部分备份构成的系列的基础。Optionally, each partial backup can serve as the base of a series of one or more differential partial backups.
    文件或文件组File or filegroup 文件备份涵盖一个或多个文件或文件组,仅与包含多个文件组的数据库相关。File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. 在简单恢复模式下,文件备份实质上仅限于只读辅助文件组。Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.
    或者,每个文件备份都可以充当由一个或多个差异文件备份构成的系列的基础。Optionally, each file backup can serve as the base of a series of one or more differential file backups.
  • 在完整恢复模式或大容量日志恢复模式下,常规备份还包括顺序“事务日志备份” (或称“日志备份” ),这是必需的备份。Under the full recovery model or bulk-logged recovery model, conventional backups also include sequential transaction log backups (or log backups), which are required. 每个日志备份均涵盖创建备份时处于活动状态的事务日志部分,并包括在上次日志备份中没有备份的所有日志记录。Each log backup covers the portion of the transaction log that was active when the backup was created, and it includes all log records not backed up in a previous log backup.

    若要以增加管理开销为代价最大限度地降低工作丢失的风险,您应该安排对日志进行频繁的备份。To minimize work-loss exposure, at the cost of administrative overhead, you should schedule frequent log backups. 在完整备份之间安排差异备份可减少数据还原后需要还原的日志备份数,从而缩短还原时间。Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

    建议您将日志备份和数据库备份分别放在不同的卷上。We recommend that you put log backups on a separate volume than the database backups.

    备注

    必须创建完整备份,才能创建第一个日志备份。Before you can create the first log backup, you must create a full backup.

  • “仅复制备份” 是特殊用途的完整备份或日志备份,它独立于正常的常规备份顺序。A copy-only backup is a special-purpose full backup or log backup that is independent of the normal sequence of conventional backups. 若要创建仅复制备份,请在 BACKUP 语句中指定 COPY_ONLY 选项。To create a copy-only backup, specify the COPY_ONLY option in your BACKUP statement. 有关详细信息,请参阅仅复制备份For more information, see Copy-Only Backups.

事务日志截断Transaction Log Truncation

若要避免填满数据库的事务日志,例行备份至关重要。To avoid filling up the transaction log of a database, routine backups are essential. 在简单恢复模式下,备份了数据库后会自动截断日志,而在完整恢复模式下,只有备份了事务日志后方才截断日志。Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log. 但是,截断过程有时也可能发生延迟。However, sometimes the truncation process can be delayed. 有关延迟日志截断的因素的信息,请参阅事务日志For information about factors that can delay log truncation, see The Transaction Log.

备注

已停用 BACKUP LOG WITH NO_LOGWITH TRUNCATE_ONLY 选项。The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. 使用完整恢复模式或大容量日志恢复模式时,如果必须删除数据库中的日志备份链,请切换至简单恢复模式。If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. 有关详细信息,请参阅查看或更改数据库的恢复模式For more information, see View or Change the Recovery Model of a Database.

格式化备份介质Formatting Backup Media

当且仅当满足任何以下条件时,BACKUP 语句才会格式化备份介质:Backup media is formatted by a BACKUP statement if and only if any of the following is true:

  • 未指定 FORMAT 选项。The FORMAT option is specified.
  • 介质为空。The media is empty.
  • 操作正在写入延续磁带。The operation is writing a continuation tape.

使用备份设备和媒体集Working with backup devices and media sets

条带媒体集(条带集)中的备份设备Backup devices in a striped media set (a stripe set)

“条带集” 是一组磁盘文件,其中的数据划分为若干块并按固定顺序分发。A stripe set is a set of disk files on which data is divided into blocks and distributed in a fixed order. 条带集中使用的备份设备数目必须保持不变(除非以 FORMAT 命令重新初始化介质)。The number of backup devices used in a stripe set must stay the same (unless the media is reinitialized with FORMAT).

下面的示例将 AdventureWorks2012AdventureWorks2012 数据库的备份写入使用三个磁盘文件的新条带介质集。The following example writes a backup of the AdventureWorks2012AdventureWorks2012 database to a new striped media set that uses three disk files.

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
  MEDIANAME = 'AdventureWorksStripedSet0',
  MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database;
GO

在备份设备已定义为条带集的组成部分后,将不能用于单个设备备份,除非指定了 FORMAT。After a backup device is defined as part of a stripe set, it cannot be used for a single-device backup unless FORMAT is specified. 同样,一个含有非条带备份的备份设备不能用于条带集,除非指定了 FORMAT。Similarly, a backup device that contains nonstriped backups cannot be used in a stripe set unless FORMAT is specified. 若要拆分条带备份集,请使用 FORMAT。To split a striped backup set, use FORMAT.

如果写入介质标头时未指定 MEDIANAME 或 MEDIADESCRIPTION,则与空项对应的介质标头字段将为空。If neither MEDIANAME or MEDIADESCRIPTION is specified when a media header is written, the media header field corresponding to the blank item is empty.

使用镜像媒体集Working with a mirrored media set

通常,备份是非镜像的,而且 BACKUP 语句仅包含一个 TO 子句。Typically, backups are unmirrored, and BACKUP statements simply include a TO clause. 但是,每个介质集可能总共包含四个镜像。However, a total of four mirrors is possible per media set. 对于镜像介质集,备份操作写入到多组备份设备。For a mirrored media set, the backup operation writes to multiple groups of backup devices. 每组备份设备均包含镜像介质集中的一个镜像。Each group of backup devices comprises a single mirror within the mirrored media set. 每个镜像都必须使用相同数量和类型的物理备份设备,而且这些设备必须都具有相同的属性。Every mirror must use the same quantity and type of physical backup devices, which must all have the same properties.

若要备份到镜像介质集,则所有的镜像服务器必须存在。To back up to a mirrored media set, all of the mirrors must be present. 若要备份到镜像媒体集,请指定 TO 子句来指定第一个镜像,并为其他每个镜像指定 MIRROR TO 子句。To back up to a mirrored media set, specify the TO clause to specify the first mirror, and specify a MIRROR TO clause for each additional mirror.

对于镜像媒体集,每个 MIRROR TO 子句列出的设备数量和类型都必须与 TO 子句列出的相同。For a mirrored media set, each MIRROR TO clause must list the same number and type of devices as the TO clause. 下面的示例写入到包含两个镜像并在每个镜像中使用三个设备的镜像介质集:The following example writes to a mirrored media set that contains two mirrors and uses three devices per mirror:

BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1a.bak',
  DISK='Y:\SQLServerBackups\AdventureWorks2a.bak',
  DISK='Z:\SQLServerBackups\AdventureWorks3a.bak'
MIRROR TO DISK='X:\SQLServerBackups\AdventureWorks1b.bak',
  DISK='Y:\SQLServerBackups\AdventureWorks2b.bak',
  DISK='Z:\SQLServerBackups\AdventureWorks3b.bak';
GO

重要

此示例旨在允许您在本地系统上对其进行测试。This example is designed to allow you to test it on your local system. 实际上,备份到同一驱动器中的多个设备会降低性能并不存在冗余,而镜像介质集正是为冗余而设计的。In practice, backing up to multiple devices on the same drive would hurt performance and would eliminate the redundancy for which mirrored media sets are designed.

镜像媒体集中的媒体簇Media families in mirrored media sets

BACKUP 语句的 TO 子句中指定的每个备份设备均对应于一个媒体簇。Each backup device specified in the TO clause of a BACKUP statement corresponds to a media family. 例如,如果 TO 子句列出三个设备,则 BACKUP 将数据写入三个媒体簇。For example, if the TO clauses lists three devices, BACKUP writes data to three media families. 在镜像介质集中,每个镜像都必须包含各个介质簇的副本。In a mirrored media set, every mirror must contain a copy of every media family. 这正是各个镜像中的设备数量必须相同的原因。This is why the number of devices must be identical in every mirror.

当对每个镜像列出多个设备时,这些设备的顺序将决定将哪个介质簇写入特定的设备。When multiple devices are listed for each mirror, the order of the devices determines which media family is written to a particular device. 例如,在每个设备列表中,第二个设备都对应于第二个介质簇。For example, in each of the device lists, the second device corresponds to the second media family. 对于上例中的设备,设备与介质簇间的对应关系如下表所示。For the devices in the above example, the correspondence between devices and media families is shown in the following table.

镜像Mirror 媒体簇 1Media family 1 媒体簇 2Media family 2 介质簇 3Media family 3
00 Z:\AdventureWorks1a.bak Z:\AdventureWorks2a.bak Z:\AdventureWorks3a.bak
11 Z:\AdventureWorks1b.bak Z:\AdventureWorks2b.bak Z:\AdventureWorks3b.bak

介质簇必须总是备份到特定镜像中的同一个设备。A media family must always be backed up onto the same device within a specific mirror. 因此,每次使用现有介质集时,请按照创建介质集时指定的相同顺序列出各个镜像的设备。Therefore, each time you use an existing media set, list the devices of each mirror in the same order as they were specified when the media set was created.

有关镜像媒体集的详细信息,请参阅镜像备份媒体集For more information about mirrored media sets, see Mirrored Backup Media Sets. 有关媒体集和媒体簇的常规信息,请参阅媒体集、媒体簇和备份集For more information about media sets and media families in general, see Media Sets, Media Families, and Backup Sets.

还原 SQL Server 备份Restoring SQL Server backups

若要还原数据库,选择联机恢复数据库或使其还原文件或文件组,请使用 Transact-SQLTransact-SQL RESTORE 语句或 SQL Server Management StudioSQL Server Management Studio Restore 任务。To restore a database and, optionally, recover it to bring it online, or to restore a file or filegroup, use either the Transact-SQLTransact-SQL RESTORE statement or the SQL Server Management StudioSQL Server Management Studio Restore tasks. 有关详细信息,请参阅还原和恢复For more information see Restore and Recovery Overview.

有关 BACKUP 选项的其他注意事项Additional considerations about BACKUP options

SKIP、NOSKIP、INIT 和 NOINIT 之间的交互Interaction of SKIP, NOSKIP, INIT, and NOINIT

下表说明了 { NOINIT | INIT } and { NOSKIP | SKIP } 选项之间的交互。This table describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.

备注

如果磁带介质为空或磁盘备份文件不存在,则所有这些交互将写入介质标头并继续进行。If the tape media is empty or the disk backup file does not exist, all these interactions write a media header and proceed. 如果介质不为空但缺少有效的介质标头,则这些操作将反馈相关信息,指出这是无效的 MTF 介质,然后终止备份操作。If the media is not empty and lacks a valid media header, these operations give feedback stating that this is not valid MTF media, and they terminate the backup operation.

NOINITNOINIT INITINIT
NOSKIPNOSKIP 如果卷中包含有效的介质标头,则验证介质名称是否匹配给定的 MEDIANAME(如果有)。If the volume contains a valid media header, verifies that the media name matches the given MEDIANAME, if any. 如果匹配,则追加备份集,同时保留所有现有的备份集。If it matches, appends the backup set, preserving all existing backup sets.
如果卷中不含有效的介质标头,则会发生错误。If the volume does not contain a valid media header, an error occurs.
如果卷中包含有效的介质标头,将执行以下检查:If the volume contains a valid media header, performs the following checks:
  • 如果指定了 MEDIANAME,则验证给定的介质名称是否匹配介质标头的介质名称。1If MEDIANAME was specified, verifies that the given media name matches the media header's media name.1
  • 确保介质上没有未过期的备份集。Verifies that there are no unexpired backup sets already on the media. 如果有,则终止备份。If there are, terminates the backup.

如果这些检查都通过了,则覆盖该介质上的所有备份集,只保留介质标头。If these checks pass, overwrites any backup sets on the media, preserving only the media header.
如果卷中不含有效的介质标头,则使用指定的 MEDIANAMEMEDIADESCRIPTION(如果有)生成一个介质标头。If the volume does not contain a valid media header, generates one with using specified MEDIANAME and MEDIADESCRIPTION, if any.
SKIPSKIP 如果卷中包含有效的介质标头,则追加备份集,并保留所有现有备份集。If the volume contains a valid media header, appends the backup set, preserving all existing backup sets. 如果卷中包含有效的2 介质标头,则覆盖介质上的所有备份集,仅保留介质标头。If the volume contains a valid2 media header, overwrites any backup sets on the media, preserving only the media header.
如果介质为空,则使用指定的 MEDIANAMEMEDIADESCRIPTION(如果有)生成一个介质标头。If the media is empty, generates a media header using the specified MEDIANAME and MEDIADESCRIPTION, if any.

1 用户必须属于相应的固定数据库或服务器角色才能执行备份操作。1 The user must belong to the appropriate fixed database or server roles to perform a backup operation.

2 有效性包括 MTF 版本号和其他标头信息。2 Validity includes the MTF version number and other header information. 如果不支持指定的版本或指定的版本不是期望值,将会发生错误。If the version specified is unsupported or an unexpected value, an error occurs.

兼容性Compatibility

注意

无法在早期版本的 SQL ServerSQL Server 中还原较新版本的 SQL ServerSQL Server创建的备份。Backups that are created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

BACKUP 支持 RESTART 选项以提供与 SQL ServerSQL Server 早期版本的向后兼容性。BACKUP supports the RESTART option to provide backward compatibility with earlier versions of SQL ServerSQL Server. 但是,RESTART 不起作用。But RESTART has no effect.

一般备注General remarks

可以将数据库或日志备份追加到任何磁盘或磁带设备上,从而将数据库及其事务日志保存在一个物理位置中。Database or log backups can be appended to any disk or tape device, allowing a database and its transaction logs to be kept within one physical location.

不允许在显式或隐式事务中使用 BACKUP 语句。The BACKUP statement is not allowed in an explicit or implicit transaction.

只要操作系统支持数据库的排序规则,就可以在不同的平台之间执行备份操作,即使这些平台使用不同的处理器类型。Cross-platform backup operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system.

对具有单个数据文件且支持透明数据加密 (TDE) 的数据库使用备份压缩时,建议使用大于 65536 (64 KB) 的 MAXTRANSFERSIZE 设置。When using backup compression with Transparent Data Encryption (TDE) enabled databases with a single data file, it is recommended to use a MAXTRANSFERSIZE setting larger than 65536 (64 KB). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,这为 TDE 加密数据库启用优化的压缩算法,该算法先解密页面,然后将其压缩并再次对其进行加密。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again. 如果使用 MAXTRANSFERSIZE = 65536 (64 KB),对使用 TDE 加密的数据库执行备份压缩时会直接压缩加密的页面,且可能不会得到良好的压缩比率。If using MAXTRANSFERSIZE = 65536 (64 KB), backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios. 有关详细信息,请参阅支持 TDE 的数据库的备份压缩For more information, see Backup Compression for TDE-enabled Databases.

备注

某些情况下,默认的 MAXTRANSFERSIZE 大于 64K:There are some cases where the default MAXTRANSFERSIZE is greater than 64K:

  • 数据库创建了多个数据文件时,它使用 MAXTRANSFERSIZE > 64KWhen the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K
  • 执行 URL 备份时,默认 MAXTRANSFERSIZE = 1048576 (1MB)When performing backup to URL, the default MAXTRANSFERSIZE = 1048576 (1MB)

即使其中一个条件适用,也必须在备份命令中显式设置 MAXTRANSFERSIZE 大于 64K,才能获得新的备份压缩算法。Even if one of these conditions applies, you must explicitly set MAXTRANSFERSIZE greater than 64K in your backup command in order to get the new backup compression algorithm.

默认情况下,每个成功的备份操作都会在 SQL ServerSQL Server 错误日志和系统事件日志中添加一个条目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 如果非常频繁地备份日志,这些成功消息会迅速累积,从而产生一个巨大的错误日志,这样会使查找其他消息变得非常困难。If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. 在这些情况下,如果任何脚本均不依赖于这些日志条目,则可以使用跟踪标志 3226 取消这些条目。In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. 有关更多信息,请参见跟踪标记For more information, see Trace Flags.

互操作性Interoperability

在数据库仍在使用时,SQL ServerSQL Server 使用联机备份过程对数据库进行备份。SQL ServerSQL Server uses an online backup process to allow a database backup while the database is still in use. 在备份过程中,可以进行多个操作;例如:在执行备份操作期间允许使用 INSERT、UPDATE 或 DELETE 语句。During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.

在数据库或事务日志备份的过程中无法运行的操作包括:Operations that cannot run during a database or transaction log backup include:

  • 文件管理操作,例如带有 ADD FILEREMOVE FILE 选项的 ALTER DATABASE 语句。File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

  • 收缩数据库或文件操作。Shrink database or shrink file operations. 这包括自动收缩操作。This includes auto-shrink operations.

如果备份操作与文件管理操作或收缩操作重叠,则产生冲突。If a backup operation overlaps with a file-management or shrink operation, a conflict arises. 无论哪个冲突操作先行开始,第二个操作总会等待第一个操作设置的锁超时(超时期限由会话超时设置控制)。Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out (the time-out period is controlled by a session timeout setting). 如果在超时期限内释放锁,第二个操作将继续执行。If the lock is released during the time-out period, the second operation continues. 如果锁超时,则第二个操作失败。If the lock times out, the second operation fails.

元数据Metadata

SQL ServerSQL Server 包含以下备份历史记录表以跟踪备份活动:includes the following backup history tables that track backup activity:

在执行还原操作时,如果尚未在 msdb 数据库中记录备份集,则可以修改备份历史记录表。When a restore is performed, if the backup set was not already recorded in the msdb database, the backup history tables might be modified.

SecuritySecurity

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,PASSWORDMEDIAPASSWORD 选项不可再用于创建备份。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. 仍可以还原使用密码创建的备份。It is still possible to restore backups created with passwords.

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

示例Examples

本部分包含以下示例:This section contains the following examples:

备注

备份操作指南主题还包含其他示例。The backup how-to topics contain additional examples. 有关详细信息,请参阅备份概述For more information, see Backup Overview.

A.A. 备份整个数据库Backing up a complete database

以下示例将 AdventureWorks2012AdventureWorks2012 数据库备份到磁盘文件。The following example backs up the AdventureWorks2012AdventureWorks2012 database to a disk file.

BACKUP DATABASE AdventureWorks2012
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
    WITH FORMAT;
GO

B.B. 备份数据库和日志Backing up the database and log

下面的示例备份 AdventureWorks2012AdventureWorks2012 示例数据库,默认情况下,该数据库使用简单恢复模式。The following example backups up the AdventureWorks2012AdventureWorks2012 sample database, which uses the simple recovery model by default. 若要支持日志备份,请将 AdventureWorks2012AdventureWorks2012 数据库改为使用完整恢复模式。To support log backups, the AdventureWorks2012AdventureWorks2012 database is modified to use the full recovery model.

接下来,该示例使用 sp_addumpdevice 创建一个逻辑备份设备以备份数据 (AdvWorksData),并创建另一个逻辑备份设备以备份日志 (AdvWorksLog)。Next, the example uses sp_addumpdevice to create a logical backup device for backing up data, AdvWorksData, and creates another logical backup device for backing up the log, AdvWorksLog.

然后,该示例对 AdvWorksData 创建完整数据库备份,并在一段更新活动过后将日志备份到 AdvWorksLogThe example then creates a full database backup to AdvWorksData, and after a period of update activity, backs up the log to AdvWorksLog.

-- To permit log backups, before the full database backup, modify the database
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2012
    SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices.
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog',
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012 TO AdvWorksData;
GO
-- Back up the AdventureWorks2012 log.
BACKUP LOG AdventureWorks2012
    TO AdvWorksLog;
GO

备注

对于生产数据库,需要定期备份日志。For a production database, back up the log regularly. 应当经常进行日志备份,以提供足够的保护来防止数据丢失。Log backups should be frequent enough to provide sufficient protection against data loss.

C.C. 创建辅助文件组的完整文件备份Creating a full file backup of the secondary filegroups

下面的示例将对两个辅助文件组中的各个文件创建完整文件备份。The following example creates a full file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck';
GO

D.D. 创建辅助文件组的差异文件备份Creating a differential file backup of the secondary filegroups

下面的示例将对两个辅助文件组中的各个文件创建差异文件备份。The following example creates a differential file backup of every file in both of the secondary filegroups.

--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
    FILEGROUP = 'SalesGroup1',
    FILEGROUP = 'SalesGroup2'
    TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
    WITH
      DIFFERENTIAL;
GO

E.E. 创建和备份到单簇镜像介质集Creating and backing up to a single-family mirrored media set

下面的示例将创建包含一个介质簇和四个镜像的镜像介质集,并将 AdventureWorks2012AdventureWorks2012 数据库备份到其中。The following example creates a mirrored media set containing a single media family and four mirrors and backs up the AdventureWorks2012AdventureWorks2012 database to them.

BACKUP DATABASE AdventureWorks2012
TO TAPE = '\\.\tape0'
MIRROR TO TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2'
MIRROR TO TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet0';

F.F. 创建和备份到多簇镜像介质集Creating and backing up to a multifamily mirrored media set

下面的示例将创建镜像介质集,其中每个镜像包含两个介质簇。The following example creates a mirrored media set in which each mirror consists of two media families. 然后将 AdventureWorks2012AdventureWorks2012 数据库备份到这两个镜像中。The example then backs up the AdventureWorks2012AdventureWorks2012 database to both mirrors.

BACKUP DATABASE AdventureWorks2012
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    FORMAT,
    MEDIANAME = 'AdventureWorksSet1';

G.G. 备份到现有镜像介质集Backing up to an existing mirrored media set

下面的示例将备份集追加到在前面的示例中创建的介质集上。The following example appends a backup set to the media set created in the preceding example.

BACKUP LOG AdventureWorks2012
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
    NOINIT,
    MEDIANAME = 'AdventureWorksSet1';

备注

为清楚起见,此处显示默认的 NOINIT。NOINIT, which is the default, is shown here for clarity.

H.H. 在新的介质集中创建压缩备份Creating a compressed backup in a new media set

下面的示例格式化介质,并创建新的介质集,然后对 AdventureWorks2012AdventureWorks2012 数据库执行压缩完整备份。The following example formats the media, creating a new media set, and perform a compressed full backup of the AdventureWorks2012AdventureWorks2012 database.

BACKUP DATABASE AdventureWorks2012 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'
WITH
    FORMAT,
    COMPRESSION;

I.I. 备份到 Microsoft Azure Blob 存储服务Backing up to the Microsoft Azure Blob storage service

以下示例向 Microsoft Azure Blob 存储服务执行完整的 Sales 数据库备份。The example performs a full database backup of Sales to the Microsoft Azure Blob storage service. 存储帐户名称为 mystorageaccountThe storage Account name is mystorageaccount. 容器名称为 myfirstcontainerThe container is called myfirstcontainer. 已经创建具有读取、写入、删除和列表权限的存储访问策略。A stored access policy has been created with read, write, delete, and list rights. 已使用与存储访问策略相关联的共享访问签名创建 SQL ServerSQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL ServerSQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. 有关 SQL ServerSQL Server 备份到 Microsoft Azure Blob 存储服务的详细信息,请参阅使用 Microsoft Azure Blob 存储服务进行 SQL Server 备份和还原SQL Server 备份到 URLFor information on SQL ServerSQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_20160726.bak'
WITH STATS = 5;

J.J. 跟踪备份语句的进度Track the progress of backup statement

以下查询返回有关当前正在运行的备份语句的信息:The following query returns information about the currently running backup statements:

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

另请参阅See Also

SQL ServerSQL Server * SQL 数据库
托管实例*
 
* SQL Database
managed instance *
 
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL 数据库托管实例Azure SQL Database managed instance

备份 Azure SQL 数据库托管实例中放置/托管的 SQL 数据库。Backs up a SQL database placed/hosted in an Azure SQL Databae managed instance. SQL 数据库托管实例提供自动备份,可让用户创建完整的数据库 COPY_ONLY 备份。SQL Database managed instance has automatic backups, and enables users to create full database COPY_ONLY backups. 不支持差异、日志和文件快照备份。Differential, log, and file snapshot backups are not supported.

语法Syntax

BACKUP DATABASE { database_name | @database_name_var }
  TO URL = { 'physical_device_name' | @physical_device_name_var }[ ,...n ]
  WITH COPY_ONLY [, { <general_WITH_options> } ]
[;]

<general_WITH_options> [ ,...n ]::=

--Media Set Options
   MEDIADESCRIPTION = { 'text' | @text_variable }
 | MEDIANAME = { media_name | @media_name_variable }
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART

--Monitoring Options
   STATS [ = percentage ]

--Encryption Options
 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) <encryptor_options> ::=
   SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

参数Arguments

DATABASE 指定一个完整数据库备份。DATABASE Specifies a complete database backup. 在数据库备份过程中,托管实例会备份足够多的事务日志,以便在还原备份时生成一致的数据库。During a database backup, the managed instance backs up enough of the transaction log to produce a consistent database when the backup is restored.

重要

在托管实例上创建的数据库备份只能在另一个托管实例上还原。A database backup created on a managed instance can only be restored on another managed instance. 无法将其还原到 SQL Server 本地实例(类似于无法将 SQL Server 2016 数据库的备份还原到 SQL Server 2012 实例)。It cannot be restored to a SQL Server on-premises instance (similar to the way that a backup of a SQL Server 2016 database cannot be restored to a SQL Server 2012 instance).

还原由 BACKUP DATABASE(“数据备份” )创建的备份时,将还原整个备份。When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. 若要从 Azure SQL 数据库托管实例自动备份还原,请参阅将数据库还原为托管实例To restore from Azure SQL Database managed instance automatic backups, see Restore a database to a Managed Instance.

{ database_name | @ database_name_var } 是备份完整的数据库时所用的源数据库。{ database_name | @database_name_var } Is the database from which the complete database is backed up. 如果作为变量 (@ database_name_var ) 提供,则可以将此名称指定为字符串常量 (@ database_name_var =database name ) 或指定为字符串数据类型(ntext 或 text 数据类型除外)的变量。If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.

有关详细信息,请参阅完整文件备份备份文件和文件组For more information, see Full File Backups and Back Up Files and Filegroups.

TO URLTO URL

指定要用于备份操作的 URL。Specifies the URL to use for the backup operation. 此 URL 格式用于创建到 Microsoft Azure 存储服务的备份。The URL format is used for creating backups to the Microsoft Azure storage service.

重要

备份到 URL 时,若要备份到多个设备,必须使用共享访问签名 (SAS) 令牌。In order to backup to multiple devices when backing up to URL, you must use Shared Access Signature (SAS) tokens. 有关创建共享访问签名的示例,请参阅 SQL Server 备份到 URL使用 Powershell 简化在 Azure 存储空间中使用共享访问签名 (SAS) 令牌创建 SQL 凭据的过程For examples creating a Shared Access Signature, see SQL Server Backup to URL and Simplifying creation of SQL Credentials with Shared Access Signature (SAS) tokens on Azure Storage with Powershell.

n 一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。n Is a placeholder that indicates that up to 64 backup devices may be specified in a comma-separated list.

WITH 选项 指定要用于备份操作的选项WITH OptionsSpecifies options to be used with a backup operation

CREDENTIAL 仅在创建到 Microsoft Azure Blob 存储服务的备份时使用。CREDENTIAL Used only when creating a backup to the Microsoft Azure Blob storage service.

ENCRYPTION 用于指定将备份加密。ENCRYPTION Used to specify encryption for a backup. 可指定加密备份所用的加密算法,或指定 NO_ENCRYPTION 以不加密备份。You can specify an encryption algorithm to encrypt the backup with or specify NO_ENCRYPTION to not have the backup encrypted. 建议进行加密以帮助保护备份文件的安全。Encryption is recommended practice to help secure backup files. 可指定的算法的列表如下:The list of algorithms you can specify are:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果决定加密,则还必须使用加密程序选项指定加密程序:If you choose to encrypt you will also have to specify the encryptor using the encryptor options:

  • SERVER CERTIFICATE = <Encryptor_Name>
  • SERVER ASYMMETRIC KEY = <Encryptor_Name>

备份集选项Backup Set Options

COPY_ONLY 指定备份为“仅复制备份” ,该备份不影响正常的备份顺序。COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. 仅复制备份是独立于 Azure SQL 数据库自动备份创建的。A copy-only backup is created independently of the Azure SQL Database automatic backups. 有关详细信息,请参阅仅复制备份For more information, see Copy-Only Backups.

{ COMPRESSION | NO_COMPRESSION } 指定是否为此备份执行备份压缩,该设置将替代服务器级默认设置。{ COMPRESSION | NO_COMPRESSION } Specifies whether backup compression is performed on this backup, overriding the server-level default.

默认行为是不进行备份压缩。The default behavior is no backup compression. 但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。But this default can be changed by setting the backup compression default server configuration option. 有关查看此选项的当前值的信息,请参阅查看或更改服务器属性面板For information about viewing the current value of this option, see View or Change Server Properties.

有关对支持透明数据加密 (TDE) 的数据库使用备份压缩的信息,请参阅备注部分。For information about using backup compression with Transparent Data Encryption (TDE) enabled databases, see the Remarks section.

COMPRESSION 显式启用备份压缩。COMPRESSION Explicitly enables backup compression.

NO_COMPRESSION 显式禁用备份压缩。NO_COMPRESSION Explicitly disables backup compression.

DESCRIPTION = { ' text ' | @ text_variable } 指定说明备份集的自由格式文本。DESCRIPTION = { 'text' | @text_variable } Specifies the free-form text describing the backup set. 该字符串最长可达 255 个字符。The string can have a maximum of 255 characters.

NAME = { backup_set_name | @ backup_set_var } 指定备份集的名称。NAME = { backup_set_name | @backup_set_var } Specifies the name of the backup set. 名称最长可达 128 个字符。Names can have a maximum of 128 characters. 如果未指定 NAME,它将为空。If NAME is not specified, it is blank.

MEDIADESCRIPTION = { text | @ text_variable } 指定媒体集的自由格式文本说明,最多为 255 个字符。MEDIADESCRIPTION = { text | @text_variable } Specifies the free-form text description, maximum of 255 characters, of the media set.

MEDIANAME = { media_name | @ media_name_variable } 指定整个备份媒体集的媒体名称。MEDIANAME = { media_name | @media_name_variable } Specifies the media name for the entire backup media set. 介质名称的长度不能多于 128 个字符,如果指定了 MEDIANAME,则该名称必须匹配备份卷上已存在的先前指定的介质名称。The media name must be no longer than 128 characters, If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volumes. 如果未指定该选项或指定了 SKIP 选项,将不会对介质名称进行验证检查。If it is not specified, or if the SKIP option is specified, there is no verification check of the media name.

BLOCKSIZE = { blocksize | @ blocksize_variable } 指定物理块大小(以字节为单位)。BLOCKSIZE = { blocksize | @blocksize_variable } Specifies the physical block size, in bytes. 支持的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字节。The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. 对于磁带设备默认为 65536,其他情况为 512。The default is 65536 for tape devices and 512 otherwise. 通常,由于 BACKUP 自动选择适合于设备的块大小,因此不需要此选项。Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. 显式声明块大小将覆盖自动选择块大小。Explicitly stating a block size overrides the automatic selection of block size.

数据传输选项Data Transfer Options

BUFFERCOUNT = { buffercount | @ buffercount_variable } 指定用于备份操作的 I/O 缓冲区总数。BUFFERCOUNT = { buffercount | @buffercount_variable } Specifies the total number of I/O buffers to be used for the backup operation. 可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.

缓冲区使用的总空间是由以下公式确定:BUFFERCOUNT * MAXTRANSFERSIZEThe total space used by the buffers is determined by: BUFFERCOUNT * MAXTRANSFERSIZE.

备注

有关使用 BUFFERCOUNT 选项的重要信息,请参阅不正确的 BufferCount 数据传输选项可导致 OOM 情况博客。For important information about using the BUFFERCOUNT option, see the Incorrect BufferCount data transfer option can lead to OOM condition blog.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } 指定要在 SQL ServerSQL Server 和备份介质之间使用的最大传输单元(字节)。MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } Specifies the largest unit of transfer in bytes to be used between SQL ServerSQL Server and the backup media. 可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

备注

对于具有单个数据文件且支持透明数据加密 (TDE) 的数据库,默认 MAXTRANSFERSIZE 为 65536 (64 KB)。For Transparent Data Encryption (TDE) enabled databases with a single data file, the default MAXTRANSFERSIZE is 65536 (64 KB). 对于非 TDE 加密数据库,使用备份到 DISK 时,默认 MAXTRANSFERSIZE 为 1048576 (1 MB),使用 VDI 或 TAPE.时为 65536 (64 KB)。For non-TDE encrypted databases the default MAXTRANSFERSIZE is 1048576 (1 MB) when using backup to DISK, and 65536 (64 KB) when using VDI or TAPE. 有关对 TDE 加密数据库使用备份压缩的详细信息,请参阅备注部分。For more information about using backup compression with TDE encrypted databases, see the Remarks section.

错误管理选项Error Management Options

使用这些选项可以确定是否为备份操作启用了备份校验和,以及备份操作是否在遇到错误时停止。These options allow you to determine whether backup checksums are enabled for the backup operation and whether the operation stops on encountering an error.

{ NO_CHECKSUM | CHECKSUM } 控制是否启用备份校验和。{ NO_CHECKSUM | CHECKSUM } Controls whether backup checksums are enabled.

NO_CHECKSUM 显式禁用备份校验和的生成(以及页校验和的验证)。NO_CHECKSUM Explicitly disables the generation of backup checksums (and the validation of page checksums). 这是默认行为。This is the default behavior.

CHECKSUM 如果此选项已启用并且可用,则指定备份操作将验证每页的校验和及页残缺,并生成整个备份的校验和。CHECKSUM Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.

使用备份校验和可能会影响工作负荷以及备份吞吐量。Using backup checksums may affect workload and backup throughput.

有关详细信息,请参阅在备份和还原期间可能的媒体错误For more information, see Possible Media Errors During Backup and Restore.

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 控制备份操作在遇到页校验和错误后是停止还是继续。{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } Controls whether a backup operation stops or continues after encountering a page checksum error.

STOP_ON_ERROR 如果未验证页校验和,则指示 BACKUP 失败。STOP_ON_ERROR Instructs BACKUP to fail if a page checksum does not verify. 这是默认行为。This is the default behavior.

CONTINUE_AFTER_ERROR 指示 BACKUP 继续执行,不管是否遇到无效校验和或页撕裂之类的错误。CONTINUE_AFTER_ERROR Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.

数据库损坏时,如果无法使用 NO_TRUNCATE 选项备份日志尾部,则可以通过指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

有关详细信息,请参阅在备份和还原期间可能的媒体错误For more information, see Possible Media Errors During Backup and Restore.

兼容性选项Compatibility Options

RESTART 不起作用。RESTART Has no effect. 此版本接受该选项,以便与以前版本的 SQL Server 保持兼容。This option is accepted by the version for compatibility with previous versions of SQL Server.

监视选项Monitoring Options

STATS [ = percentage ] 每当另一个百分比 完成时显示一条消息,并用于测量进度。STATS [ = percentage ] Displays a message each time another percentage completes, and is used to gauge progress. 如果省略百分比 ,则 SQL ServerSQL Server 在每完成 10% 就显示一条消息。If percentage is omitted, SQL ServerSQL Server displays a message after each 10 percent is completed.

STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。The STATS option reports the percentage complete as of the threshold for reporting the next interval. 这是指定百分比的近似值;例如,当 STATS=10 时,如果完成进度为 40%,则该选项可能显示 43%。This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. 对于较大的备份集,这不是问题,因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

SQL 数据库托管实例的限制Limitations for SQL Database managed instance

最大备份带状线大小为 195 GB(最大 blob 大小)。Max backup stripe size is 195 GB (maximum blob size). 增加备份命令中的带状线数量以缩小单个带状线大小,将其保持在限制范围内。Increase the number of stripes in the backup command to reduce individual stripe size and stay within this limit.

SecuritySecurity

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 权限 。BACKUP DATABASE permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

URL 的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the URL can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions.

示例Examples

以下示例向 Microsoft Azure Blob 存储服务执行 Sales 的 COPY_ONLY 备份。The example performs a COPY_ONLY backup of Sales to the Microsoft Azure Blob storage service. 存储帐户名称为 mystorageaccountThe storage Account name is mystorageaccount. 容器名称为 myfirstcontainerThe container is called myfirstcontainer. 已经创建具有读取、写入、删除和列表权限的存储访问策略。A stored access policy has been created with read, write, delete, and list rights. 已使用与存储访问策略相关联的共享访问签名创建 SQL ServerSQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe SQL ServerSQL Server credential, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. 有关 SQL ServerSQL Server 备份到 Microsoft Azure Blob 存储服务的详细信息,请参阅使用 Microsoft Azure Blob 存储服务进行 SQL Server 备份和还原SQL Server 备份到 URLFor information on SQL ServerSQL Server backup to the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service and SQL Server Backup to URL.

BACKUP DATABASE Sales
TO URL = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_20160726.bak'
WITH STATS = 5, COPY_ONLY;

另请参阅See Also

还原数据库Restore database

SQL ServerSQL Server SQL 数据库
托管实例
SQL Database
managed instance
* Analytics
Platform System (PDW) *
 
* Analytics
Platform System (PDW) *
 

 

分析平台系统Analytics Platform System

创建 并行数据仓库Parallel Data Warehouse 数据库的备份并将该备份存储在设备以外的用户指定网络位置。Creates a backup of a 并行数据仓库Parallel Data Warehouse database and stores the backup off the appliance in a user-specified network location. 将此语句与 RESTORE DATABASE - Analytics Platform System 配合使用,用于灾难恢复或用于将数据库从一台设备复制到另一台设备。Use this statement with RESTORE DATABASE - Analytics Platform System for disaster recovery, or to copy a database from one appliance to another.

开始之前,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“获取和配置备份服务器”。Before you begin, see "Acquire and Configure a Backup Server" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

并行数据仓库Parallel Data Warehouse 中有两种类型的备份。There are two types of backups in 并行数据仓库Parallel Data Warehouse. 完整数据库备份是指备份整个 并行数据仓库Parallel Data Warehouse 数据库。A full database backup is a backup of an entire 并行数据仓库Parallel Data Warehouse database. 差异数据库备份只包含自上次完整备份后所做的更改。A differential database backup only includes changes made since the last full backup. 用户数据库备份包含数据库用户和数据库角色。A backup of a user database includes database users, and database roles. Master 数据库备份包含登录信息。A backup of the master database includes logins.

有关并行数据仓库Parallel Data Warehouse数据库备份的详细信息,请参阅并行数据仓库产品文档Parallel Data Warehouse product documentation中的“备份和还原”。For more information about 并行数据仓库Parallel Data Warehouse database backups, see "Backup and Restore" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

语法Syntax

--Create a full backup of a user database or the master database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    [ WITH [ ( ]<with_options> [ ,...n ][ ) ] ]
[;]

--Create a differential backup of a user database.
BACKUP DATABASE database_name
    TO DISK = '\\UNC_path\backup_directory'
    WITH [ ( ] DIFFERENTIAL
    [ , <with_options> [ ,...n ] [ ) ]
[;]

<with_options> ::=
    DESCRIPTION = 'text'
    | NAME = 'backup_name'

参数Arguments

database_name 要在其中创建备份的数据库的名称。database_name The name of the database on which to create a backup. 数据库既可以是 master 数据库,也可以是用户数据库。The database can be the master database or a user database.

TO DISK = '\\UNC_path \backup_directory ' 并行数据仓库Parallel Data Warehouse 将写入备份文件的网络路径和目录。TO DISK = '\\UNC_path\backup_directory' The network path and directory to which 并行数据仓库Parallel Data Warehouse will write the backup files. 例如,“\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup”。For example, '\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup'.

  • 备份目录名称的路径必须已存在,并且必须指定为完全限定的通用命名约定 (UNC) 路径。The path to the backup directory name must already exist and must be specified as a fully qualified universal naming convention (UNC) path.
  • 备份目录 backup_directory 在运行备份命令前不得存在。The backup directory, backup_directory, must not exist before running the backup command. 并行数据仓库Parallel Data Warehouse 将创建备份目录。will create the backup directory.
  • 备份目录的路径不能是本地路径,并且不能是任何并行数据仓库Parallel Data Warehouse设备节点上的位置。The path to the backup directory cannot be a local path and it cannot be a location on any of the 并行数据仓库Parallel Data Warehouse appliance nodes.
  • UNC 路径和备份目录名称的最大长度为 200 个字符。The maximum length of the UNC path and backup directory name is 200 characters.
  • 必须以 IP 地址的形式指定服务器或主机。The server or host must be specified as an IP address. 不能以主机名称或服务器名称的形式指定。You cannot specify it as the host or server name.

DESCRIPTION = ' text ' 定备份的文字描述。DESCRIPTION = 'text' Specifies a textual description of the backup. 文本的最大长度为 255 个字符。The maximum length of the text is 255 characters.

描述存储在元数据中,在使用 RESTORE HEADERONLY 还原备份标头时显示。The description is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.

NAME = ' backup _name ' 指定备份集的名称。NAME = 'backup _name' Specifies the name of the backup. 备份名称可以与数据库名称不同。The backup name can be different from the database name.

  • 名称最长可达 128 个字符。Names can have a maximum of 128 characters.
  • 不能包含路径。Cannot include a path.
  • 必须以字母或数字字符或下划线 () 开头。Must begin with a letter or number character or an underscore (). 允许使用的特殊字符包括下划线 (_)、连字符 (-) 或空格 ( )。Special characters permitted are the underscore (_), hyphen (-), or space ( ). 备份名称不能以空格字符结尾。Backup names cannot end with a space character.
  • 如果指定位置已存在 backup_name,该语句会失败。The statement will fail if backup_name already exists in the specified location.

此名称存储在元数据中,在使用 RESTORE HEADERONLY 还原备份标头时显示。This name is stored in the metadata, and will be displayed when the backup header is restored with RESTORE HEADERONLY.

DIFFERENTIAL 指定执行用户数据库的差异备份。DIFFERENTIAL Specifies to perform a differential backup of a user database. 如果省略,默认执行完整数据库备份。If omitted, the default is a full database backup. 差异备份的名称不必与完整备份的名称匹配。The name of the differential backup does not need to match the name of the full backup. 如需跟踪差异备份及其对应的完整备份,请考虑使用相同的名称并追加“full”或“diff”。For keeping track of the differential and its corresponding full backup, consider using the same name with 'full' or 'diff' appended.

例如:For example:

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerFull';

BACKUP DATABASE Customer TO DISK = '\\xxx.xxx.xxx.xxx\backups\CustomerDiff' WITH DIFFERENTIAL;

权限Permissions

要求具有 BACKUP DATABASE 权限,或者在 db_backupoperator 固定数据库角色中具有成员身份。Requires the BACKUP DATABASE permission or membership in the db_backupoperator fixed database role. 添加到 db_backupoperator 固定数据库角色的普通用户无法备份 master 数据库。The master database cannot be backed up but by a regular user that was added to the db_backupoperator fixed database role. sa、构造管理员或 sysadmin 固定服务器角色的成员可备份 master 数据库。The master database can only be backed up by sa, the fabric administrator, or members of the sysadmin fixed server role.

需要有权访问、创建和写入备份目录的 Windows 帐户。Requires a Windows account that has permission to access, create, and write to the backup directory. 还必须将 Windows 帐户名称和密码存储在并行数据仓库Parallel Data Warehouse中。You must also store the Windows account name and password in 并行数据仓库Parallel Data Warehouse. 若要将这些网络凭据添加到 并行数据仓库Parallel Data Warehouse,可使用 sp_pdw_add_network_credentials - SQL 数据仓库存储过程。To add these network credentials to 并行数据仓库Parallel Data Warehouse, use the sp_pdw_add_network_credentials - SQL Data Warehous stored procedure.

有关如何管理 并行数据仓库Parallel Data Warehouse 中凭据的详细信息,请参阅安全部分。For more information about managing credentials in 并行数据仓库Parallel Data Warehouse, see the Security section.

错误处理Error Handling

BACKUP DATABASE 错误会在以下情况中发生:BACKUP DATABASE errors under the following conditions:

  • 用户权限不足以执行备份。User permissions are not sufficient to perform a backup.
  • 并行数据仓库Parallel Data Warehouse 对存储备份所在的网络位置没有正确的权限。does not have the correct permissions to the network location where the backup will be stored.
  • 数据库不存在。The database does not exist.
  • 网络共享上已存在目标目录。The target directory already exists on the network share.
  • 目标网络共享不可用。The target network share is not available.
  • 目标网络共享没有用于备份的足够空间。The target network share does not have enough space for the backup. BACKUP DATABASE 命令在启动备份前不会确认是否存在足够的磁盘空间,因而可能在运行 BACKUP DATABASE 时生成磁盘空间不足错误。The BACKUP DATABASE command does not confirm that sufficient disk space exists prior to initiating the backup, making it possible to generate an out-of-disk-space error while running BACKUP DATABASE. 磁盘空间不足时,并行数据仓库Parallel Data Warehouse 会回滚 BACKUP DATABASE 命令。When insufficient disk space occurs, 并行数据仓库Parallel Data Warehouse rolls back the BACKUP DATABASE command. 若要缩小数据库的大小,可运行 DBCC SHRINKLOG (Azure SQL Data Warehouse)To decrease the size of your database, run DBCC SHRINKLOG (Azure SQL Data Warehouse)
  • 尝试在事务中启动备份。Attempt to start a backup within a transaction.

一般备注General Remarks

执行数据库备份前,请使用 DBCC SHRINKLOG (Azure SQL Data Warehouse) 缩小数据库的大小。Before you perform a database backup, use DBCC SHRINKLOG (Azure SQL Data Warehouse) to decrease the size of your database.

并行数据仓库Parallel Data Warehouse 备份会以多个文件的集合的形式存储在相同目录中。A 并行数据仓库Parallel Data Warehouse backup is stored as a set of multiple files within the same directory.

差异备份所需的时间通常比完整备份所需的时间少,并且可以更频繁地执行。A differential backup usually takes less time than a full backup and can be performed more frequently. 如果多个差异备份基于同一完整备份,每个差异备份会包含之前差异备份中的所有更改。When multiple differential backups are based on the same full backup, each differential includes all of the changes in the previous differential backup.

如果取消 BACKUP 命令,并行数据仓库Parallel Data Warehouse 将删除目标目录以及为备份创建的任何文件。If you cancel a BACKUP command, 并行数据仓库Parallel Data Warehouse will remove the target directory and any files created for the backup. 如果 并行数据仓库Parallel Data Warehouse 失去到共享的网络连接,则无法完成回滚。If 并行数据仓库Parallel Data Warehouse loses network connectivity to the share, the rollback cannot complete.

完整备份和差异备份存储在不同的目录中。Full backups and differential backups are stored in separate directories. 指定完整备份和差异备份拥有共同来源时,不强制使用命名约定。Naming conventions are not enforced for specifying that a full backup and differential backup belong together. 可通过自己的命名约定进行跟踪。You can track this through your own naming conventions. 或者,可使用 WITH DESCRIPTION 选项添加描述,然后使用 RESTORE HEADERONLY 语句检索该描述来进行跟踪。Alternatively, you can track this by using the WITH DESCRIPTION option to add a description, and then by using the RESTORE HEADERONLY statement to retrieve the description.

限制和局限Limitations and Restrictions

不能对 master 数据库执行差异备份。You cannot perform a differential backup of the master database. 仅支持对 master 数据库执行完整备份。Only full backups of the master database are supported.

备份文件的存储格式仅适合使用 RESTORE DATABASE - Analytics Platform System 语句将备份还原到 并行数据仓库Parallel Data Warehouse 设备。The backup files are stored in a format suitable only for restoring the backup to a 并行数据仓库Parallel Data Warehouse appliance by using the RESTORE DATABASE - Analytics Platform System statement.

使用 BACKUP DATABASE 语句的备份不能用于将数据或用户信息传输到 SMP SQL ServerSQL Server 数据库。The backup with the BACKUP DATABASE statement cannot be used to transfer data or user information to SMP SQL ServerSQL Server databases. 如需获取该功能,可使用远程表复制功能。For that functionality, you can use the remote table copy feature. 有关详细信息,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中的“远程表复制”。For more information, see "Remote Table Copy" in the 并行数据仓库产品文档Parallel Data Warehouse product documentation.

并行数据仓库Parallel Data Warehouse 使用 SQL ServerSQL Server 备份技术备份和还原数据库。uses SQL ServerSQL Server backup technology to backup and restore databases. SQL ServerSQL Server 备份选项已预先配置为使用备份压缩。backup options are preconfigured to use backup compression. 不能设置压缩、校验和、块大小和缓冲区计数等备份选项。You cannot set backup options such as compression, checksum, block size, and buffer count.

任何给定时间都只能在设备上运行一个数据库备份或还原。Only one database backup or restore can run on the appliance at any given time. 并行数据仓库Parallel Data Warehouse 会让备份或还原命令排队,直到当前备份或还原命令完成。will queue backup or restore commands until the current backup or restore command has completed.

用于还原备份的目标设备使用的计算节点数量至少应与源设备使用的计算节点数量相等。The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. 目标设备拥有的计算节点数量可多于源设备,但不可少于源设备。The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.

并行数据仓库Parallel Data Warehouse 不跟踪备份的位置和名称,因为备份存储在设备以外的位置。does not track the location and names of backups since the backups are stored off the appliance.

并行数据仓库Parallel Data Warehouse 会跟踪数据库备份是成功还是失败。does track the success or failure of database backups.

仅当上次完整备份成功完成时才允许执行差异备份。A differential backup is only allowed if the last full backup completed successfully. 例如,假设在星期一创建 Sales 数据库的完整备份且备份成功完成。For example, suppose that on Monday you create a full backup of the Sales database and the backup finishes successfully. 随后在星期二创建 Sales 数据库的完整备份并失败。Then on Tuesday you create a full backup of the Sales database and it fails. 此次失败后,不能基于星期一的完整备份创建差异备份。After this failure, you cannot then create a differential backup based on Monday's full backup. 在创建差异备份前,必须先创建成功的完整备份。You must first create a successful full backup before creating a differential backup.

元数据Metadata

这些动态管理视图包含关于所有备份、还原和加载操作的信息。These dynamic management views contain information about all backup, restore, and load operations. 信息在两次系统重启之间仍会保留。The information persists across system restarts.

“性能”Performance

若要执行备份,并行数据仓库Parallel Data Warehouse 首先会备份元数据,然后对存储在计算节点上的数据库数据执行并行备份。To perform a backup, 并行数据仓库Parallel Data Warehouse first backs up the metadata, and then it performs a parallel backup of the database data stored on the Compute nodes. 数据会直接从每个计算节点复制到备份目录。Data is copied directly from each Compute nodes to the backup directory. 若要在将数据从计算节点移至备份目录的过程中获得最佳性能,并行数据仓库Parallel Data Warehouse 可控制要并发复制数据的计算节点的数量。To achieve the best performance for moving data from the Compute nodes to the backup directory, 并行数据仓库Parallel Data Warehouse controls the number of Compute nodes that are copying data concurrently.

锁定Locking

在 DATABASE 对象上使用 ExclusiveUpdate 锁。Takes an ExclusiveUpdate lock on the DATABASE object.

SecuritySecurity

并行数据仓库Parallel Data Warehouse 备份不存储在设备上。backups are not stored on the appliance. 因此,IT 团队负责管理备份安全的所有方面。Therefore, your IT team is responsible for managing all aspects of the backup security. 例如,这包括管理备份数据的安全、用于存储备份的服务器的安全和将备份服务器连接到 并行数据仓库Parallel Data Warehouse 设备的网络基础结构的安全。For example, this includes managing the security of the backup data, the security of the server used to store backups, and the security of the networking infrastructure that connects the backup server to the 并行数据仓库Parallel Data Warehouse appliance.

管理网络凭据Manage Network Credentials

对备份目录的网络访问权限基于标准 Windows 文件共享安全。Network access to the backup directory is based on standard Windows file sharing security. 在执行备份前,需要创建或指定用于向备份目录验证 并行数据仓库Parallel Data Warehouse 身份的 Windows 帐户。Before performing a backup, you need to create or designate a Windows account that will be used for authenticating 并行数据仓库Parallel Data Warehouse to the backup directory. 此 Windows 帐户必须有权访问、创建和写入备份目录。This windows account must have permission to access, create, and write to the backup directory.

重要

若要降低数据的安全风险,建议指定一个 Windows 帐户专门用于执行备份和还原操作。To reduce security risks with your data, we advise that you designate one Windows account solely for the purpose of performing backup and restore operations. 仅允许此帐户访问备份位置,不要授予对其他位置的访问权限。Allow this account to have permissions to the backup location and nowhere else.

需要通过运行 sp_pdw_add_network_credentials - SQL 数据仓库存储过程,在 并行数据仓库Parallel Data Warehouse 中存储用户名和密码。You need to store the user name and password in 并行数据仓库Parallel Data Warehouse by running the sp_pdw_add_network_credentials - SQL Data Warehouse stored procedure. 并行数据仓库Parallel Data Warehouse 使用 Windows 凭据管理器在控制节点和计算节点上存储及加密用户名和密码。uses Windows Credential Manager to store and encrypt user names and passwords on the Control node and Compute nodes. 不使用 BACKUP DATABASE 命令备份凭据。The credentials are not backed up with the BACKUP DATABASE command.

若要从 并行数据仓库Parallel Data Warehouse 删除网络凭据,可使用 sp_pdw_remove_network_credentials - SQL 数据仓库To remove network credentials from 并行数据仓库Parallel Data Warehouse, see sp_pdw_remove_network_credentials - SQL Data Warehouse.

若要列出 并行数据仓库Parallel Data Warehouse 中存储的所有网络凭据,可使用 sys.dm_pdw_network_credentials 动态管理视图。To list all of the network credentials stored in 并行数据仓库Parallel Data Warehouse, use the sys.dm_pdw_network_credentials dynamic management view.

示例Examples

A.A. 添加备份位置的网络凭据Add network credentials for the backup location

若要创建备份,并行数据仓库Parallel Data Warehouse 必须具有备份目录的读/写权限。To create a backup, 并行数据仓库Parallel Data Warehouse must have read/write permission to the backup directory. 以下示例显示如何添加用户凭据。The following example shows how to add the credentials for a user. 并行数据仓库Parallel Data Warehouse 会存储这些凭据并将其用于备份和还原操作。will store these credentials and use them to for backup and restore operations.

重要

出于安全原因,建议创建一个域帐户专门用于执行备份。For security reasons, we recommend creating one domain account solely for the purpose of performing backups.

EXEC sp_pdw_add_network_credentials 'xxx.xxx.xxx.xxx', 'domain1\backupuser', '*****';

B.B. 删除备份位置的网络凭据Remove network credentials for the backup location

以下示例显示如何从 并行数据仓库Parallel Data Warehouse 删除域用户凭据。The following example shows how to remove the credentials for a domain user from 并行数据仓库Parallel Data Warehouse.

EXEC sp_pdw_remove_network_credentials 'xxx.xxx.xxx.xxx';

C.C. 创建用户数据库的完整备份Create a full backup of a user database

以下示例创建 Invoices 用户数据库的完整备份。The following example creates a full backup of the Invoices user database. 并行数据仓库Parallel Data Warehouse 会创建 Invoices2013 目录并将备份文件保存到 \\10.192.63.147\backups\yearly\Invoices2013Full 目录。will create the Invoices2013 directory and will save the backup files to the \\10.192.63.147\backups\yearly\Invoices2013Full directory.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full';

D.D. 创建用户数据库的差异备份Create a differential backup of a user database

以下示例创建差异备份,其中包含自 Invoices 数据库上次完整备份以来所做的全部更改。The following example creates a differential backup, which includes all changes made since the last full backup of the Invoices database. 并行数据仓库Parallel Data Warehouse 会创建 \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff 目录用于存储文件。will create the \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff directory to which it will store the files. 描述“Invoices 2013 differential backup”会随备份的标头信息一起存储。The description 'Invoices 2013 differential backup' will be stored with the header information for the backup.

仅当 Invoices 的上次完整备份成功完成时才会运行差异备份。The differential backup will only run successfully if the last full backup of Invoices completed successfully.

BACKUP DATABASE Invoices TO DISK = '\\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff'
    WITH DIFFERENTIAL,
    DESCRIPTION = 'Invoices 2013 differential backup';

E.E. 创建 master 数据库的完整备份Create a full backup of the master database

以下示例创建 master 数据库的完整备份,并将其存储在“\\10.192.63.147\backups\2013\daily\20130722\master”目录中。The following example creates a full backup of the master database and stores it in the directory '\\10.192.63.147\backups\2013\daily\20130722\master'.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master';

F.F. 创建设备登录信息的备份Create a backup of appliance login information

Master 数据库存储设备登录信息。The master database stores the appliance login information. 若要备份设备登录信息,需要备份 master 数据库。To backup the appliance login information you need to backup master.

以下示例创建 master 数据库的完整备份。The following example creates a full backup of the master database.

BACKUP DATABASE master TO DISK = '\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master'
WITH (
    DESCRIPTION = 'Master Backup 20130722',
    NAME = 'login-backup'
)
;

另请参阅See Also

RESTORE DATABASE - 并行数据仓库RESTORE DATABASE - Parallel Data Warehous