BACKUP (Transact-SQL)

备份 SQL 数据库。

选择一个产品

在下面的行中,选择你感兴趣的产品名称,系统将只显示该产品的信息。

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定

* SQL Server *  

 

SQL Server

备份完整的 SQL Server 数据库以创建数据库备份,或者备份数据库的一个或多个文件或文件组以创建文件备份 (BACKUP DATABASE)。 另外,在完整恢复模式或大容量日志恢复模式下备份数据库事务日志以创建日志备份 (BACKUP LOG)。

语法

--Back 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 ] } ]
[;]

--Back 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 ] } ]
[;]

--Create 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 ] } ]
[;]

--Back 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 ] ]
[;]

--Back up all the databases on an instance of SQL Server (a server)

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
[;]

BACKUP SERVER
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { METADATA_ONLY
           | <general_WITH_options> [ ,...n ] } ]
[;]

--Back up a group of databases
ALTER DATABASE <database>
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON

ALTER DATABASE <...>
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
...

BACKUP GROUP {<database> [,... ]}
  TO <backup_device> [ ,...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { METADATA_ONLY
           | <general_WITH_options> [ ,...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 [ ALGORITHM = { MS_XPRESS | accelerator_algorithm } ] | 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 } }
 | { METADATA_ONLY | SNAPSHOT }

--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

参数

DATABASE

指定一个完整数据库备份。 如果指定了一个文件和文件组的列表,则仅备份该列表中的文件和文件组。 在进行完整数据库备份或差异数据库备份的过程中,SQL Server 会备份足够多的事务日志,以便在还原备份时生成一个一致的数据库。

还原由 BACKUP DATABASE(“数据备份”)创建的备份时,将还原整个备份。 只有日志备份才能还原到备份中的特定时间或事务。

注意

master 数据库,只能执行完整数据库备份。

LOG

指定仅备份事务日志。 该日志是从上一次成功执行的日志备份到当前日志的末尾。 必须创建完整备份,才能创建第一个日志备份。

通过在 RESTORE LOG 语句中指定 WITH STOPATSTOPATMARKSTOPBEFOREMARK,可以将日志备份还原到备份中的特定时间或事务。

注意

执行典型日志备份后,如果没有指定 WITH NO_TRUNCATECOPY_ONLY,某些事务日志记录将变为不活动状态。 一个或多个虚拟日志文件中的所有记录变为不活动状态后,日志将被截断。 如果日志在常规日志备份后未被截断,则可能是某些操作延迟了日志截断。 有关详细信息,请参阅可能延迟日志截断的因素

GROUP (<database>,...n)

在 SQL Server 2022 (16.x) 中引入。

备份一组数据库。 使用快照备份。 需要 WITH METADATA_ONLY。 请参阅创建 Transact-SQL 快照备份

SERVER

在 SQL Server 2022 (16.x) 中引入。

备份 SQL Server 实例上的所有数据库。 使用快照备份。 需要 WITH METADATA_ONLY。 请参阅创建 Transact-SQL 快照备份

METADATA_ONLY

在 SQL Server 2022 (16.x) 中引入。

快照备份所必需的。 BACKUP SERVERBACKUP GROUP...,请参阅创建 Transact-SQL 快照备份

METADATA_ONLY 与 SNAPSHOT 同义。 虚拟设备接口 (VDI) 使用 SNAPSHOT。 有关 VDI 的详细信息,请参阅虚拟设备接口 (VDI) 参考

{ database_name | @database_name_var }

备份事务日志、部分数据库或完整的数据库时所用的源数据库。 如果作为变量 (@database_name_var) 提供,则可以将此名称指定为字符串常量 (@database_name_var=database_name) 或指定为字符串数据类型(ntext 或 text 数据类型除外)的变量。

注意

不能备份数据库镜像伙伴关系中的镜像数据库。

<file_or_filegroup> [ ,...n ]

只能与 BACKUP DATABASE 一起使用,用于指定某个数据库文件或文件组包含在文件备份中,或指定某个只读文件或文件组包含在部分备份中。

FILE = { logical_file_name | @logical_file_name_var }

文件或变量的逻辑名称,其值等于要包含在备份中的文件的逻辑名称。

FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

文件组或变量的逻辑名称,其值等于要包含在备份中的文件组的逻辑名称。 在简单恢复模式下,只允许对只读文件组执行文件组备份。

注意

如果数据库的大小和性能要求使得进行数据库备份不切实际,则应考虑使用文件备份。 NUL 设备可用于测试备份的性能,但不应在生产环境中使用。

n
一个占位符,表示可以在逗号分隔的列表中指定多个文件和文件组。 数量不受限制。

有关详细信息,请参阅完整文件备份备份文件和文件组

READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ]

指定部分备份。 部分备份包括数据库中的所有读/写文件:主文件组和任何读/写辅助文件组,以及任何指定的只读文件或文件组。

READ_WRITE_FILEGROUPS

指定在部分备份中备份所有读/写文件组。 如果数据库是只读的,则 READ_WRITE_FILEGROUPS 仅包括主文件组。

重要

使用 FILEGROUP 而不是 READ_WRITE_FILEGROUPS 显式列出读/写文件组将会创建文件备份。

FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

只读文件组或变量的逻辑名称,其值等于要包含在部分备份中的只读文件组的逻辑名称。 有关详细信息,请参阅本文前面的“<file_or_filegroup>”。

n
一个占位符,表示可以在逗号分隔的列表中指定多个只读文件组。

有关部分备份的详细信息,请参阅部分备份

TO backup_device<> [ ,...n ]

指示附带的备份设备集是一个未镜像的媒体集,或者是镜像媒体集中的第一批镜像(为其声明了一个或多个 MIRROR TO 子句)。

<backup_device>
指定用于备份操作的逻辑备份设备或物理备份设备。

{ logical_device_name | @logical_device_name_var }

适用范围:SQL Server
要将数据库备份到的备份设备的逻辑名称。 逻辑名称必须遵守标识符规则。 如果作为变量 (@logical_device_name_var) 提供,则可以将该备份设备名称指定为字符串常量(@logical_device_name_var= 逻辑备份设备名称)或任何字符串数据类型(ntext 或 text 数据类型除外)的变量。

{ DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' }

适用范围:SQL Server(以 SQL Server 2012 (11.x) SP1 CU2 开头的 URL)

指定磁盘文件或磁带设备,或 URL。

此 URL 格式用于创建到 Microsoft Azure Blob 存储或 S3 兼容对象存储的备份。 有关详细信息和示例,请参阅:

注意

NUL 磁盘设备将弃用发送给它的所有信息,且仅应用于测试。 这不适用于生产用途。

重要

从 SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2014 (12.x),备份到 Azure Blob 存储的 URL 时只能备份到单个设备。 备份到 URL 时,若要备份到多个设备,必须使用 SQL Server 2016 (13.x) 和更高版本以及共享访问签名 (SAS) 令牌。 有关创建共享访问签名的示例,请参阅 SQL Server 备份到 URL使用 Powershell 简化在 Azure 存储空间中使用共享访问签名 (SAS) 令牌创建 SQL 凭据的过程

如果某一磁盘设备不存在,也可以在 BACKUP 语句中指定它。 如果存在物理设备且 BACKUP 语句中未指定 INIT 选项,则备份将追加到该设备。

注意

NUL 设备将弃用发送到此文件的所有输入,但备份仍将所有页面标记为备份。

有关详细信息,请参阅 备份设备

注意

在 SQL Server 的未来版本中将删除 TAPE 选项。 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。

n
一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。

MIRROR TO <backup_device> [ ,...n ]

指定一组辅助备份设备(最多三个),其中每个设备都镜像 TO 子句中指定的备份设备。 MIRROR TO 子句和 TO 子句必须指定相同类型和数量的备份设备。 最多可以使用三个 MIRROR TO 子句。

此选项仅在 SQL Server 的 Enterprise 版中可用。

注意

对于 MIRROR TO = DISK,BACKUP 自动基于磁盘的扇区大小来决定磁盘设备合适的块大小。 如果使用与指定为主备份设备的磁盘不同的扇区大小格式化 MIRROR TO 磁盘,则备份命令将失败。 为了将备份镜像到具有不同扇区大小的设备,必须指定 BLOCKSIZE 参数,并且应将其设置为所有目标设备中的最大扇区大小。 有关块大小的详细信息,请参阅此主题后面的 "BLOCKSIZE"。

<backup_device>
请参阅本部分前面的“<backup_device>”。

n
一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。 MIRROR TO 子句中的设备数必须等于 TO 子句中的设备数。

有关详细信息,请参阅本文后面备注部分中的“镜像媒体集中的媒体簇”。

[ next-mirror-to ]
一个占位符,表示一个 BACKUP 语句除了包含一个 TO 子句外,最多还可包含三个 MIRROR TO 子句。

WITH 选项

指定要用于备份操作的选项。

CREDENTIAL

适用范围:SQL Server(从 SQL Server 2012 (11.x) SP1 CU2 开始)。

仅在创建到 Microsoft Azure Blob 存储的备份时使用。

FILE_SNAPSHOT

适用范围:SQL Server(从 SQL Server 2016 (13.x) 开始) 。

用于在使用 Azure Blob 存储来存储所有 SQL Server 数据库文件时,创建数据库文件的 Azure 快照。 有关详细信息,请参阅 Microsoft Azure 中的 SQL Server 数据文件。 SQL Server 快照备份将数据库文件(数据和日志文件)的 Azure 快照保持为一致的状态。 一组一致的 Azure 快照构成备份并记录在备份文件中。 BACKUP DATABASE TO URL WITH FILE_SNAPSHOTBACKUP LOG TO URL WITH FILE_SNAPSHOT 之间的唯一区别是后者会截断事务日志,而前者不会。 借助 SQL Server 快照备份,在完成 SQL Server 建立备份链时所需的初始完整备份之后,只需单个事务日志备份即可将数据库还原到事务日志备份的时间点。 此外,只需两次事务日志备份即可将数据库还原到两次事务日志备份之间的时间点。

DIFFERENTIAL

只能与 BACKUP DATABASE 一起使用,指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。 差异备份一般会比完整备份占用更少的空间。 对于上一次完整备份后执行的所有单个日志备份,使用该选项可以不必再进行备份。

注意

默认情况下,BACKUP DATABASE 创建完整备份。

有关详细信息,请参阅 差异备份

ENCRYPTION

用于指定将备份加密。 可指定加密备份所用的加密算法,或指定 NO_ENCRYPTION 以不加密备份。 建议进行加密以帮助保护备份文件的安全。 可指定的算法的列表如下:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果决定加密,则还必须使用加密程序选项指定加密程序:

  • SERVER CERTIFICATE = Encryptor_Name
  • SERVER ASYMMETRIC KEY = Encryptor_Name

SERVER CERTIFICATESERVER ASYMMETRIC KEY 是在 master 数据库中创建的证书和非对称密钥。 有关详细信息,请分别参阅 CREATE CERTIFICATECREATE ASYMMETRIC KEY

警告

将加密与 FILE_SNAPSHOT 参数一起使用时,使用指定的加密算法对元数据文件本身进行加密,并且系统会验证是否已完成对数据库的透明数据加密 (TDE)。 不会再对数据本身进行其他加密。 如果未加密数据库或发出备份语句之前未完成加密,则备份失败。

备份集选项

这些选项对此备份操作创建的备份集进行操作。

注意

若要为还原操作指定备份集,请使用 FILE = <backup_set_file_number> 选项。 有关如何指定备份集的详细信息,请参阅 RESTORE 参数 中的“指定备份集”。

COPY_ONLY

指定备份为“仅复制备份”,该备份不影响正常的备份顺序。 仅复制备份是独立于定期计划的常规备份而创建的。 仅复制备份不会影响数据库的总体备份和还原过程。

应在出于特殊目的而进行备份的情况下使用仅复制备份,例如在进行联机文件还原前备份日志。 通常,仅复制日志备份仅使用一次即被删除。

  • BACKUP DATABASE 一起使用时,COPY_ONLY 选项创建的完整备份不能用作差异基准。 差异位图不会被更新,因此差异备份的表现就像仅复制备份不存在一样。 后续差异备份将最新的常规完整备份用作它们的基准。

    重要

    如果将 DIFFERENTIALCOPY_ONLY 一起使用,则忽略 COPY_ONLY 并创建差异备份。

  • BACKUP LOG 一起使用时,COPY_ONLY 选项将创建“仅复制日志备份”,该备份不会截断事务日志。 仅复制日志备份对日志链没有任何影响,因此其他日志备份的表现就像仅复制备份不存在一样。

有关详细信息,请参阅仅复制备份

[ COMPRESSION [ ALGORITHM = ( { MS_XPRESS | accelerator_algorithm } ) ] | NO_COMPRESSION ]

指定是否为此备份执行备份压缩,该设置将替代服务器级默认设置。

安装时,默认行为是不进行备份压缩。 但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。 有关查看此选项的当前值的信息,请参阅查看或更改服务器属性面板

有关对支持透明数据加密 (TDE) 的数据库使用备份压缩的信息,请参阅备注部分。

COMPRESSION
显式启用备份压缩。

NO_COMPRESSION
显式禁用备份压缩。

SQL Server 2022 (16.x) 引入了 ALGORITHM,用于标识操作的压缩算法。 默认为 MS_XPRESS。 如果已配置集成加速和卸载,则可以使用解决方案提供的加速器。 例如,如果配置了用于 SQL Server 的 Intel® QuickAssist Technology (QAT),则以下示例使用加速器解决方案完成备份,其中 QATzip 库使用压缩级别为 1 的 QZ_DEFLATE

BACKUP DATABASE <database_name> TO DISK WITH COMPRESSION (ALGORITHM = QAT_DEFLATE) 

DESCRIPTION = { 'text' | @text_variable }

指定说明备份集的自由格式文本。 该字符串最长可达 255 个字符。

NAME = { backup_set_name | @backup_set_var }

指定备份集的名称。 名称最长可达 128 个字符。 如果未指定 NAME,它将为空。

{ EXPIREDATE ='date' | RETAINDAYS =days }

指定允许覆盖该备份的备份集的日期。 如果同时使用这两个选项,RETAINDAYS 的优先级别将高于 EXPIREDATE。

如果这两个选项均未指定,则过期日期由 mediaretention 配置设置确定。 有关详细信息,请参阅服务器配置选项

重要

这些选项仅仅阻止 SQL Server 覆盖文件。 用其他方法仍可擦除磁带,而通过操作系统也可以删除磁盘文件。 有关过期验证的详细信息,请参阅本主题中的 SKIP 和 FORMAT。

EXPIREDATE = { 'date' | @date_var }
指定备份集到期和允许被覆盖的日期。 如果作为变量 (@date_var) 提供,则该日期必须采用已配置系统日期/时间的格式,并指定为下列类型之一:

  • 字符串常量 (@date_var= date)
  • 字符串数据类型(ntext 或 text 数据类型除外)的变量
  • smalldatetime
  • datetime 变量

例如:

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

有关如何指定 datetime 值的信息,请参阅日期和时间类型

注意

若要忽略过期日期,请使用 SKIP 选项。

RETAINDAYS = { days | @days_var }
指定必须经过多少天才可以覆盖该备份介质集。 如果作为变量 (@days_var) 提供,则必须指定为整数。

{ METADATA_ONLY | SNAPSHOT }

适用于:SQL Server 2022 (16.x)

METADATA_ONLY 和 SNAPSHOT 是同义词。

介质集选项

这些选项作为一个整体对介质集进行操作。

{ NOINIT | INIT }

控制备份操作是追加到还是覆盖备份介质中的现有备份集。 默认为追加到介质中最新的备份集 (NOINIT)。

注意

有关 { NOINIT | INIT } and { NOSKIP | SKIP } 之间交互的信息,请参阅本主题后面的备注

NOINIT
表示备份集将追加到指定的介质集上,以保留现有的备份集。 如果为介质集定义了介质密码,则必须提供密码。 NOINIT 是默认设置。

有关详细信息,请参阅 媒体集、媒体簇和备份集

INIT
指定应覆盖所有备份集,但是保留介质标头。 如果指定了 INIT,将覆盖该设备上所有现有的备份集(如果条件允许)。 默认情况下,BACKUP 将检查下列条件,如果其中的任一条件存在,都不会覆盖备份介质:

  • 所有备份集都未过期。 有关详细信息,请参阅 EXPIREDATERETAINDAYS 选项。
  • 如果 BACKUP 语句给出了备份集名,则该备份集名与备份介质上的名称不匹配。 有关详细信息,请参阅本部分前面介绍的 NAME 选项。

若要替代这些检查,请使用 SKIP 选项。

有关详细信息,请参阅 媒体集、媒体簇和备份集

{ NOSKIP | SKIP }

控制备份操作是否在覆盖介质中的备份集之前检查它们的过期日期和时间。

注意

有关 { NOINIT | INIT } and { NOSKIP | SKIP } 之间交互的信息,请参阅本主题后面的“备注”。

NOSKIP
指示 BACKUP 语句在可以覆盖介质上的所有备份集之前先检查它们的过期日期。 此选项为默认行为。

SKIP
禁用备份集的过期和名称检查,这些检查一般由 BACKUP 语句执行以防覆盖备份集。 有关 { NOINIT | INIT } 和 { NOSKIP | SKIP } 之间交互的信息,请参阅本文后面的“备注”。 若要查看备份集的过期日期,请查询 backupset 历史记录表的 expiration_date 列。

{ NOFORMAT | FORMAT }

指定是否应该在用于此备份操作的卷上写入介质标头,以覆盖任何现有的介质标头和备份集。

NOFORMAT
指定备份操作在用于此备份操作的介质卷上保留现的有介质标头和备份集。 此选项为默认行为。

FORMAT
指定创建新的介质集。 FORMAT 将使备份操作在用于备份操作的所有介质卷上写入新的介质标头。 卷的现有内容将变为无效,因为覆盖了任何现有的介质标头和备份集。

重要

请谨慎使用 FORMAT。 格式化介质集的任何一个卷都将使整个介质集不可用。 例如,如果初始化现有条带介质集中的单个磁带,则整个介质集都将变得不可用。

指定 FORMAT 即表示 SKIPSKIP 无需显式声明。

MEDIADESCRIPTION = { text | @text_variable }

指定介质集的自由格式文本说明,最多为 255 个字符。

MEDIANAME = { media_name | @media_name_variable }

指定整个备份介质集的介质名称。 介质名称的长度不能多于 128 个字符。 如果指定了 MEDIANAME,则该名称必须匹配备份卷上已存在的先前指定的介质名称。 如果未指定该选项或指定了 SKIP 选项,将不会对介质名称进行验证检查。

BLOCKSIZE = { blocksize | @blocksize_variable }

用字节数来指定物理块的大小。 支持的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字节。 对于磁带设备默认为 65536,其他情况为 512。 通常,由于 BACKUP 自动选择适合于设备的块大小,因此不需要此选项。 显式声明块大小将覆盖自动选择块大小。

如果要建立一个计划在 CD-ROM 上进行复制和还原的备份,请指定 BLOCKSIZE=2048。

注意

通常,只有写入磁带设备时,此选项才会影响性能。

数据传输选项

BUFFERCOUNT = { buffercount | @buffercount_variable }

指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。

缓冲区使用的总空间是由以下公式确定:BUFFERCOUNT * MAXTRANSFERSIZE

注意

有关使用 BUFFERCOUNT 选项的重要信息,请参阅不正确的 BufferCount 数据传输选项可导致 OOM 情况博客。

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }

指定要在 SQL Server 和备份介质之间使用的最大传输单元(字节)。 可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。

使用 SQL 编写器服务创建备份时,如果数据库已配置 FILESTREAM(文件流)或包含内存优化文件组,则恢复时的 MAXTRANSFERSIZE 应大于或等于创建备份时使用的 MAXTRANSFERSIZE

对于具有单个数据文件且支持透明数据加密 (TDE) 的数据库,默认 MAXTRANSFERSIZE 为 65536 (64 KB)。 对于非 TDE 加密数据库,使用备份到 DISK 时,默认 MAXTRANSFERSIZE 为 1048576 (1 MB),使用 VDI 或 TAPE.时为 65536 (64 KB)。 有关对 TDE 加密数据库使用备份压缩的详细信息,请参阅备注部分。

错误管理选项

使用这些选项可以确定是否为备份操作启用了备份校验和,以及备份操作是否在遇到错误时停止。

{ NO_CHECKSUM | CHECKSUM }

控制是否启用备份校验和。

NO_CHECKSUM
显式禁用备份校验和的生成(以及页校验和的验证)。 此选项为默认行为。

CHECKSUM
如果此选项已启用并且可用,则指定备份操作将验证每页的校验和及页残缺,并生成整个备份的校验和。

使用备份校验和可能会影响工作负荷以及备份吞吐量。

有关详细信息,请参阅在备份和还原期间可能的媒体错误

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

控制备份操作在遇到页校验和错误后是停止还是继续。

STOP_ON_ERROR
如果未验证页校验和,则指示 BACKUP 失败。 此选项为默认行为。

CONTINUE_AFTER_ERROR
指示 BACKUP 继续执行,不管是否遇到无效校验和或页撕裂之类的错误。

数据库损坏时,如果无法使用 NO_TRUNCATE 选项备份日志尾部,则可以通过指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份

有关详细信息,请参阅在备份和还原期间可能的媒体错误

兼容性选项

RESTART

从 SQL Server 2008 开始不起作用。 此版本接受该选项,以便与旧版本的 SQL Server 保持兼容。

监视选项

STATS [ =percentage ]

每当另一个百分比完成时显示一条消息,并用于测量进度。 如果省略百分比,则 SQL Server 在每完成 10% 就显示一条消息。

STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。 这是指定百分比的近似值;例如,当 STATS=10 时,如果完成进度为 40%,则该选项可能显示 43%。 对于较大的备份集,这不是问题,因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。

磁带选项

这些选项只用于 TAPE 设备。 如果使用的是非磁带设备,则会忽略这些选项。

{ REWIND | NOREWIND }

REWIND
指定 SQL Server 释放和倒带磁带。 REWIND 是默认设置。

NOREWIND
指定在备份操作之后 SQL Server 让磁带一直处于打开状态。 在对磁带执行多个备份操作时,可以使用此选项来帮助改进性能。

NOREWIND 包含 NOUNLOAD,并且这些选项在单个 BACKUP 语句中不兼容。

注意

如果使用 NOREWIND,则 SQL Server 实例将一直保留磁带机的所有权,直到在同一进程中运行的 BACKUP 或 RESTORE 语句使用 REWINDUNLOAD 选项或服务器实例关闭为止。 磁带保持打开将防止其他进程访问磁带。 有关如何显示打开的磁带列表和如何将打开的磁带关闭的信息,请参阅备份设备

{ UNLOAD | NOUNLOAD }

注意

UNLOADNOUNLOAD 会话设置可在整个会话期间存在,或者在通过指定其他设置而进行重置之前一直存在。

UNLOAD
指定在备份完成后自动重绕并卸载磁带。 会话开始时 UNLOAD 是默认值。

NOUNLOAD
指定在 BACKUP 操作之后磁带继续在磁带机中加载。

注意

备份到磁带备份设备时,BLOCKSIZE 选项会影响备份操作的性能。 通常,只有写入磁带设备时,此选项才会影响性能。

特定于日志的选项

这些选项仅与 BACKUP LOG 一起使用。

注意

如果不想进行日志备份,则请使用简单恢复模式。 有关详细信息,请参阅恢复模式

{ NORECOVERY | STANDBY =undo_file_name }

NORECOVERY
备份日志的尾部并使数据库处于 RESTORING 状态。 当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时,NORECOVERY 很有用。

若要执行最大程度的日志备份(跳过日志截断)并自动将数据库置于 RESTORING 状态,请同时使用 NO_TRUNCATENORECOVERY 选项。

STANDBY =standby_file_name
备份日志的尾部并使数据库处于只读和 STANDBY 状态。 将 STANDBY 子句写入备用数据(执行回滚,但需带进一步还原选项)。 使用 STANDBY 选项等同于 BACKUP LOG WITH NORECOVERY 后跟 RESTORE WITH STANDBY。

使用备用模式需要一个备用文件,该文件由 standby_file_name 指定,其位置存储于数据库的日志中。 如果指定的文件已经存在,则数据库引擎会覆盖该文件;如果指定的文件不存在,则数据库引擎将创建它。 备用文件将成为数据库的一部分。

该文件将保存对回滚所做的更改,如果要在以后应用 RESTORE LOG 操作,则必须反转这些更改。 必须有足够的磁盘空间供备用文件增长,以使备用文件能够包含数据库中由回滚的未提交事务修改的所有不重复的页。

NO_TRUNCATE

指定事务日志不应被截断,并使数据库引擎尝试执行备份,而不考虑数据库的状态。 因此,使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据。 该选项允许在数据库损坏时备份事务日志。

BACKUP LOG 的 NO_TRUNCATE 选项相当于同时指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。

如果不使用 NO_TRUNCATE 选项,则数据库必须处于 ONLINE 状态。 如果数据库处于 SUSPENDED 状态,则可以通过指定 NO_TRUNCATE 来创建备份。 但是,如果数据库处于 OFFLINE 或 EMERGENCY 状态,即便使用了 NO_TRUNCATE,也不允许执行 BACKUP。 有关数据库状态的详细信息,请参阅数据库状态

关于使用 SQL Server 备份

本节简要说明了以下基本备份概念:

备份类型事务日志截断格式化备份媒体使用备份设备和媒体集 SQL Server 备份

注意

有关 SQL Server 中备份的说明,请参阅备份概述

备份类型

支持的备份类型取决于数据库的恢复模式,如下所示:

  • 所有恢复模式都支持数据的完整备份和差异备份。

    备份范围 备份类型
    整个数据库 数据库备份涵盖整个数据库。

    或者,每个数据库备份都可以充当由一个或多个差异数据库备份构成的系列的基础。
    部分数据库 部分备份涵盖读/写文件组,也可能涵盖一个或多个只读文件或文件组。

    或者,每个部分备份都可以充当由一个或多个差异部分备份构成的系列的基础。
    文件或文件组 文件备份涵盖一个或多个文件或文件组,仅与包含多个文件组的数据库相关。 在简单恢复模式下,文件备份实质上仅限于只读辅助文件组。
    或者,每个文件备份都可以充当由一个或多个差异文件备份构成的系列的基础。
  • 在完整恢复模式或大容量日志恢复模式下,常规备份还包括顺序“事务日志备份”(或称“日志备份”),这是必需的备份。 每个日志备份均涵盖创建备份时处于活动状态的事务日志部分,并包括在上次日志备份中没有备份的所有日志记录。

    若要以增加管理开销为代价最大限度地降低工作丢失的风险,您应该安排对日志进行频繁的备份。 在完整备份之间安排差异备份可减少数据还原后需要还原的日志备份数,从而缩短还原时间。

    建议您将日志备份和数据库备份分别放在不同的卷上。

    注意

    必须创建完整备份,才能创建第一个日志备份。

  • “仅复制备份”是特殊用途的完整备份或日志备份,它独立于正常的常规备份顺序。 若要创建仅复制备份,请在 BACKUP 语句中指定 COPY_ONLY 选项。 有关详细信息,请参阅仅复制备份

事务日志截断

若要避免填满数据库的事务日志,例行备份至关重要。 在简单恢复模式下,备份了数据库后会自动截断日志,而在完整恢复模式下,只有备份了事务日志后方才截断日志。 但是,截断过程有时也可能发生延迟。 有关延迟日志截断的因素的信息,请参阅事务日志

注意

已停用 BACKUP LOG WITH NO_LOGWITH TRUNCATE_ONLY 选项。 使用完整恢复模式或大容量日志恢复模式时,如果必须删除数据库中的日志备份链,请切换至简单恢复模式。 有关详细信息,请参阅查看或更改数据库的恢复模式

格式化备份介质

当且仅当满足任何以下条件时,BACKUP 语句才会格式化备份介质:

  • 未指定 FORMAT 选项。
  • 介质为空。
  • 操作正在写入延续磁带。

使用备份设备和媒体集

条带媒体集(条带集)中的备份设备

“条带集”是一组磁盘文件,其中的数据划分为若干块并按固定顺序分发。 条带集中使用的备份设备数目必须保持不变(除非以 FORMAT 命令重新初始化介质)。

下面的示例将 AdventureWorks2012 数据库的备份写入使用三个磁盘文件的新条带介质集。

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。 同样,一个含有非条带备份的备份设备不能用于条带集,除非指定了 FORMAT。 若要拆分条带备份集,请使用 FORMAT。

如果写入介质标头时未指定 MEDIANAME 或 MEDIADESCRIPTION,则与空项对应的介质标头字段将为空。

使用镜像媒体集

通常,备份是非镜像的,而且 BACKUP 语句仅包含一个 TO 子句。 但是,每个介质集可能总共包含四个镜像。 对于镜像介质集,备份操作写入到多组备份设备。 每组备份设备均包含镜像介质集中的一个镜像。 每个镜像都必须使用相同数量和类型的物理备份设备,而且这些设备必须都具有相同的属性。

若要备份到镜像介质集,则所有的镜像服务器必须存在。 若要备份到镜像媒体集,请指定 TO 子句来指定第一个镜像,并为其他每个镜像指定 MIRROR TO 子句。

对于镜像媒体集,每个 MIRROR TO 子句列出的设备数量和类型都必须与 TO 子句列出的相同。 下面的示例写入到包含两个镜像并在每个镜像中使用三个设备的镜像介质集:

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

重要

此示例旨在允许您在本地系统上对其进行测试。 实际上,备份到同一驱动器中的多个设备会降低性能并不存在冗余,而镜像介质集正是为冗余而设计的。

镜像媒体集中的媒体簇

BACKUP 语句的 TO 子句中指定的每个备份设备均对应于一个媒体簇。 例如,如果 TO 子句列出三个设备,则 BACKUP 将数据写入三个媒体簇。 在镜像介质集中,每个镜像都必须包含各个介质簇的副本。 这正是各个镜像中的设备数量必须相同的原因。

当对每个镜像列出多个设备时,这些设备的顺序将决定将哪个介质簇写入特定的设备。 例如,在每个设备列表中,第二个设备都对应于第二个介质簇。 对于上例中的设备,设备与介质簇间的对应关系如下表所示。

镜像 媒体簇 1 媒体簇 2 介质簇 3
0 Z:\AdventureWorks1a.bak Z:\AdventureWorks2a.bak Z:\AdventureWorks3a.bak
1 Z:\AdventureWorks1b.bak Z:\AdventureWorks2b.bak Z:\AdventureWorks3b.bak

介质簇必须总是备份到特定镜像中的同一个设备。 因此,每次使用现有介质集时,请按照创建介质集时指定的相同顺序列出各个镜像的设备。

有关镜像媒体集的详细信息,请参阅镜像备份媒体集。 有关媒体集和媒体簇的常规信息,请参阅媒体集、媒体簇和备份集

还原 SQL Server 备份

要还原数据库,选择联机恢复数据库或使其还原文件或文件组,请使用 Transact-SQL RESTORE 语句或 SQL Server Management Studio“还原”任务。 有关详细信息,请参阅还原和恢复概述

有关 BACKUP 选项的其他注意事项

SKIP、NOSKIP、INIT 和 NOINIT 之间的交互

下表说明了 { NOINIT | INIT } and { NOSKIP | SKIP } 选项之间的交互。

注意

如果磁带介质为空或磁盘备份文件不存在,则所有这些交互将写入介质标头并继续进行。 如果介质不为空但缺少有效的介质标头,则这些操作将反馈相关信息,指出这是无效的 MTF 介质,然后终止备份操作。

Skip 选项 NOINIT INIT
NOSKIP 如果卷中包含有效的介质标头,则验证介质名称是否匹配给定的 MEDIANAME(如果有)。 如果匹配,则追加备份集,同时保留所有现有的备份集。
如果卷中不含有效的介质标头,则会发生错误。
如果卷中包含有效的介质标头,将执行以下检查:
  • 如果指定了 MEDIANAME,则验证给定的介质名称是否匹配介质标头的介质名称。1
  • 确保介质上没有未过期的备份集。 如果有,则终止备份。

如果这些检查都通过了,则覆盖该介质上的所有备份集,只保留介质标头。
如果卷中不含有效的介质标头,则使用指定的 MEDIANAMEMEDIADESCRIPTION(如果有)生成一个介质标头。
SKIP 如果卷中包含有效的介质标头,则追加备份集,并保留所有现有备份集。 如果卷中包含有效的2 介质标头,则覆盖介质上的所有备份集,仅保留介质标头。
如果介质为空,则使用指定的 MEDIANAMEMEDIADESCRIPTION(如果有)生成一个介质标头。

1 用户必须属于相应的固定数据库或服务器角色才能执行备份操作。

2 有效性包括 MTF 版本号和其他标头信息。 如果不支持指定的版本或指定的版本不是期望值,将会发生错误。

兼容性

注意

无法在早期版本的 SQL Server 中还原较新版本的 SQL Server创建的备份。

BACKUP 支持 RESTART 选项以提供与 SQL Server 早期版本的向后兼容性。 但是,RESTART 不起作用。

一般备注

可以将数据库或日志备份追加到任何磁盘或磁带设备上,从而将数据库及其事务日志保存在一个物理位置中。

不允许在显式或隐式事务中使用 BACKUP 语句。

只要操作系统支持数据库的排序规则,就可以在不同的平台之间执行备份操作,即使这些平台使用不同的处理器类型。

从 SQL Server 2016 (13.x) 开始,MAXTRANSFERSIZE 大于 65536 (64 KB) 的设置为透明数据加密 (TDE) 加密数据库启用优化的压缩算法,该算法先解密页面,然后将其压缩并再次对其进行加密。 如果未指定 MAXTRANSFERSIZE 或者使用了 MAXTRANSFERSIZE = 65536 (64 KB),对使用 TDE 加密的数据库执行备份压缩时会直接压缩加密的页面,且可能不会得到良好的压缩比率。 有关详细信息,请参阅支持 TDE 的数据库的备份压缩

从 SQL Server 2019 (15.x) CU5 开始,不再需要 MAXTRANSFERSIZE 设置来对 TDE 启用此优化的压缩算法。 如果备份命令指定 WITH COMPRESSION,或者将备份压缩默认服务器配置设置为 1,则 MAXTRANSFERSIZE 将自动增加到 128 K 以启用优化算法。 如果备份命令指定 MAXTRANSFERSIZE 的值为 > 64 K,则将采用提供的值。 换句话说,SQL Server 绝不会自动减小该值,只会增加它。 如果需要使用 MAXTRANSFERSIZE = 65536 备份 TDE 加密的数据库,则必须指定 WITH NO_COMPRESSION,或者确保将备份压缩默认服务器配置设置为 0。

注意

某些情况下,默认的 MAXTRANSFERSIZE 大于 64K:

  • 数据库创建了多个数据文件时,它使用 MAXTRANSFERSIZE> 64K。
  • 执行到 Azure Blob 存储的 URL 备份时,默认 MAXTRANSFERSIZE = 1048576 (1 MB)。
  • 执行到 S3 兼容对象存储的 URL 备份时,默认 MAXTRANSFERSIZE = 10485760 (10 MB)。

即使其中一个条件适用,也必须在备份命令中显式设置 MAXTRANSFERSIZE 大于 64K,才能获得优化备份压缩算法,除非使用 SQL Server 2019 (15.x) CU5 或更高版本。

默认情况下,每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果非常频繁地备份日志,这些成功消息会迅速累积,从而产生一个很大的错误日志,这样会使查找其他消息变得非常困难。 在这些情况下,如果任何自动化或监视均不依赖于这些日志条目,则可以使用跟踪标志 3226 取消这些条目。 有关更多信息,请参见跟踪标记

互操作性

在数据库仍在使用时,SQL Server 使用联机备份过程对数据库进行备份。 在备份过程中,可以进行多个操作;例如:在执行备份操作期间允许使用 INSERT、UPDATE 或 DELETE 语句。

在数据库或事务日志备份的过程中无法运行的操作包括:

  • 文件管理操作,例如带有 ADD FILEREMOVE FILE 选项的 ALTER DATABASE 语句。

  • 收缩数据库或文件操作。 这包括自动收缩操作。

如果备份操作与文件管理操作或收缩操作重叠,则产生冲突。 无论哪个冲突操作先行开始,第二个操作总会等待第一个操作设置的锁超时(超时期限由会话超时设置控制)。 如果在超时期限内释放锁,第二个操作将继续执行。 如果锁超时,则第二个操作失败。

元数据

SQL Server 包含以下备份历史记录表以跟踪备份活动:

在执行还原操作时,如果尚未在 msdb 数据库中记录备份集,则可以修改备份历史记录表。

安全性

从 SQL Server 2012 (11.x) 开始,PASSWORDMEDIAPASSWORD 选项不可再用于创建备份。 仍可以还原使用密码创建的备份。

权限

默认情况下,为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASEBACKUP LOG 权限 。

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。 确保 SQL Server 帐户需要具有对备份设备和备份文件写入到的文件夹的读取和写入权限。 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。

示例

本部分包含以下示例:

注意

备份操作指南主题还包含其他示例。 有关详细信息,请参阅备份概述

A. 备份整个数据库

以下示例将 AdventureWorks2012 数据库备份到磁盘文件。

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

B. 备份数据库和日志

下面的示例备份 AdventureWorks2019 示例数据库,默认情况下,该数据库使用简单恢复模式。 若要支持日志备份,请将 AdventureWorks2019 数据库改为使用完整恢复模式。

接下来,该示例使用 sp_addumpdevice 创建一个逻辑备份设备以备份数据 (AdvWorksData),并创建另一个逻辑备份设备以备份日志 (AdvWorksLog)。

然后,该示例对 AdvWorksData 创建完整数据库备份,并在一段更新活动过后将日志备份到 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

注意

对于生产数据库,需要定期备份日志。 应当经常进行日志备份,以提供足够的保护来防止数据丢失。

C. 创建辅助文件组的完整文件备份

下面的示例将对两个辅助文件组中的各个文件创建完整文件备份。

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

D. 创建辅助文件组的差异文件备份

下面的示例将对两个辅助文件组中的各个文件创建差异文件备份。

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

E. 创建和备份到单簇镜像介质集

下面的示例将创建包含一个介质簇和四个镜像的镜像介质集,并将 AdventureWorks2012 数据库备份到其中。

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

F. 创建和备份到多簇镜像介质集

下面的示例将创建镜像介质集,其中每个镜像包含两个介质簇。 然后将 AdventureWorks2012 数据库备份到这两个镜像中。

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

G. 备份到现有镜像介质集

下面的示例将备份集追加到在前面的示例中创建的介质集上。

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

注意

为清楚起见,此处显示默认的 NOINIT。

H. 在新的介质集中创建压缩备份

下面的示例格式化介质,并创建新的介质集,然后对 AdventureWorks2012 数据库执行压缩完整备份。

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

I. 备份到 Microsoft Azure Blob 存储

以下示例向 Microsoft Azure Blob 存储执行完整的 Sales 数据库备份。 存储帐户名称为 mystorageaccount。 容器名称为 myfirstcontainer。 已经创建具有读取、写入、删除和列表权限的存储访问策略。 已使用与存储访问策略相关联的共享访问签名创建 SQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainer。 有关 SQL Server 备份到 Microsoft Azure Blob 存储的详细信息,请参阅使用 Microsoft Azure Blob 存储进行 SQL Server 备份和还原SQL Server 备份到 URL

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

J. 备份到 S3 兼容的对象存储

适用于:SQL Server 2022 (16.x)

本示例将 Sales 数据库的完整备份数据库执行到 S3 兼容的对象存储平台。 语句中不需要凭据名称,也不需要与确切的 URL 路径匹配,但会对提供的 URL 执行适当的凭据查找。 有关详细信息,请参阅使用 S3 兼容对象存储预览版进行 SQL Server 备份和还原

BACKUP DATABASE Sales
TO      URL = 's3://10.10.10.10:8787/sqls3backups/sales_01.bak'
,       URL = 's3://10.10.10.10:8787/sqls3backups/sales_02.bak'
,       URL = 's3://10.10.10.10:8787/sqls3backups/sales_03.bak'
WITH    FORMAT
,       STATS               = 10
,       COMPRESSION;

K. 跟踪备份语句的进度

以下查询返回有关当前正在运行的备份语句的信息:

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%'

后续步骤

* SQL 托管实例 *  

 

Azure SQL 托管实例

备份 Azure SQL 托管实例中的 SQL 数据库。 Azure SQL 托管实例具有自动备份。 可以创建完整的数据库 COPY_ONLY 备份。 不支持差异、日志和文件快照备份。

也适用于已启用 Azure Arc 的 SQL 托管实例

语法

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

参数

DATABASE

指定一个完整数据库备份。 在数据库备份过程中,Azure SQL 托管实例会备份足够多的事务日志,以便在还原备份时生成一致的数据库。

重要

在托管实例上创建的数据库备份只能在另一个 Azure SQL 托管实例上还原。 无法将其还原到 SQL Server 本地实例(类似于无法将 SQL Server 2016 数据库的备份还原到 SQL Server 2012 实例)。

还原由 BACKUP DATABASE(“数据备份”)创建的备份时,将还原整个备份。 若要从 SQL 托管实例自动备份还原,请参阅将数据库还原为 Azure SQL 托管实例

{ database_name | @database_name_var }

是备份完整的数据库时所用的源数据库。 如果作为变量 (@database_name_var) 提供,则可以将此名称指定为字符串常量 (@database_name_var=database_name) 或指定为字符串数据类型(ntext 或 text 数据类型除外)的变量。

有关详细信息,请参阅完整文件备份备份文件和文件组

TO URL

指定要用于备份操作的 URL。 此 URL 格式用于创建到 Microsoft Azure 存储服务的备份。

重要

备份到 URL 时,若要备份到多个设备,必须使用共享访问签名 (SAS) 令牌。 有关创建共享访问签名的示例,请参阅 SQL Server 备份到 URL使用 Powershell 简化在 Azure 存储空间中使用共享访问签名 (SAS) 令牌创建 SQL 凭据的过程

n
一个占位符,表示最多可以在逗号分隔的列表中指定 64 个备份设备。

WITH 选项 指定要用于备份操作的选项

CREDENTIAL

仅在创建到 Microsoft Azure Blob 存储的备份时使用。

ENCRYPTION

用于指定将备份加密。 可指定加密备份所用的加密算法,或指定 NO_ENCRYPTION 以不加密备份。 建议进行加密以帮助保护备份文件的安全。 可指定的算法的列表如下:

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY
  • NO_ENCRYPTION

如果决定加密,则还必须使用加密程序选项指定加密程序:

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

备份集选项

COPY_ONLY

指定备份为“仅复制备份”,该备份不影响正常的备份顺序。 仅复制备份是独立于 Azure SQL 数据库自动备份创建的。 有关详细信息,请参阅仅复制备份

{ COMPRESSION | NO_COMPRESSION }

指定是否为此备份执行备份压缩,该设置将替代服务器级默认设置。

默认行为是不进行备份压缩。 但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。 有关查看此选项的当前值的信息,请参阅查看或更改服务器属性面板

COMPRESSION
显式启用备份压缩。

NO_COMPRESSION
显式禁用备份压缩。

DESCRIPTION = { 'text' | @text_variable }

指定说明备份集的自由格式文本。 该字符串最长可达 255 个字符。

NAME = { backup_set_name | @backup_set_var }

指定备份集的名称。 名称最长可达 128 个字符。 如果未指定 NAME,它将为空。

MEDIADESCRIPTION = { text | @text_variable }

指定介质集的自由格式文本说明,最多为 255 个字符。

MEDIANAME = { media_name | @media_name_variable }

指定整个备份介质集的介质名称。 介质名称的长度不能多于 128 个字符,如果指定了 MEDIANAME,则该名称必须匹配备份卷上已存在的先前指定的介质名称。 如果未指定该选项或指定了 SKIP 选项,将不会对介质名称进行验证检查。

BLOCKSIZE = { blocksize | @blocksize_variable }

用字节数来指定物理块的大小。 支持的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字节。 对于磁带设备默认为 65536,其他情况为 512。 通常,由于 BACKUP 自动选择适合于设备的块大小,因此不需要此选项。 显式声明块大小将覆盖自动选择块大小。

数据传输选项

BUFFERCOUNT = { buffercount | @buffercount_variable }

指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。

缓冲区使用的总空间是由以下公式确定:BUFFERCOUNT * MAXTRANSFERSIZE

注意

有关使用 BUFFERCOUNT 选项的重要信息,请参阅不正确的 BufferCount 数据传输选项可导致 OOM 情况博客。

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable }

指定要在 SQL Server 和备份介质之间使用的最大传输单元(字节)。 可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。

对于具有单个数据文件且支持透明数据加密 (TDE) 的数据库,默认 MAXTRANSFERSIZE 为 65536 (64 KB)。 对于非 TDE 加密数据库,使用备份到 DISK 时,默认 MAXTRANSFERSIZE 为 1048576 (1 MB),使用 VDI 或 TAPE.时为 65536 (64 KB)。

备注

MAXTRANSFESIZE 指定了最大的传输单位,并不保证每次写入操作都会传输指定的最大大小。 用于条带化事务日志备份写入操作的 MAXTRANSFERSIZE 设置为 64 KB。

错误管理选项

使用这些选项可以确定是否为备份操作启用了备份校验和,以及备份操作是否在遇到错误时停止。

{ NO_CHECKSUM | CHECKSUM }

控制是否启用备份校验和。

NO_CHECKSUM
显式禁用备份校验和的生成(以及页校验和的验证)。 此选项为默认行为。

CHECKSUM
如果此选项已启用并且可用,则指定备份操作将验证每页的校验和及页残缺,并生成整个备份的校验和。

使用备份校验和可能会影响工作负荷以及备份吞吐量。

有关详细信息,请参阅在备份和还原期间可能的媒体错误

{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

控制备份操作在遇到页校验和错误后是停止还是继续。

STOP_ON_ERROR
如果未验证页校验和,则指示 BACKUP 失败。 此选项为默认行为。

CONTINUE_AFTER_ERROR
指示 BACKUP 继续执行,不管是否遇到无效校验和或页撕裂之类的错误。

数据库损坏时,如果无法使用 NO_TRUNCATE 选项备份日志尾部,则可以通过指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份

有关详细信息,请参阅在备份和还原期间可能的媒体错误

兼容性选项

RESTART

无效。 此版本接受该选项,以便与以前版本的 SQL Server 保持兼容。

监视选项

STATS [ =percentage ]

每当另一个百分比完成时显示一条消息,并用于测量进度。 如果省略百分比,则 SQL Server 在每完成 10% 就显示一条消息。

STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。 这是指定百分比的近似值;例如,当 STATS=10 时,如果完成进度为 40%,则该选项可能显示 43%。 对于较大的备份集,这不是问题,因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。

SQL 托管实例的限制

最大备份带状线大小为 195 GB(最大 blob 大小)。 增加备份命令中的带状线数量以缩小单个带状线大小,将其保持在限制范围内。

安全性

权限

默认情况下,为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 权限 。

URL 的所有权和权限问题可能会妨碍备份操作。 SQL Server 必须能够读取和写入设备;运行 SQL Server 服务的帐户必须具有写入权限。

示例

以下示例向 Microsoft Azure Blob 存储执行 Sales 的 COPY_ONLY 备份。 存储帐户名称为 mystorageaccount。 容器名称为 myfirstcontainer。 已经创建具有读取、写入、删除和列表权限的存储访问策略。 已使用与存储访问策略相关联的共享访问签名创建 SQL Server 凭据 https://mystorageaccount.blob.core.windows.net/myfirstcontainer。 有关 SQL Server 备份到 Microsoft Azure Blob 存储的详细信息,请参阅使用 Microsoft Azure Blob 存储进行 SQL Server 备份和还原SQL Server 备份到 URL

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

后续步骤

还原数据库

* Analytics
Platform System (PDW) *
 

 

分析平台系统

创建 Analytics Platform System (PDW) 数据库的备份并将该备份存储在设备以外的用户指定网络位置。 将此语句与 RESTORE DATABASE - Analytics Platform System 配合使用,用于灾难恢复或用于将数据库从一台设备复制到另一台设备。

开始之前,请参阅 Analytics Platform System (PDW) 产品文档中的“获取和配置备份服务器”。

Analytics Platform System (PDW) 中有两种类型的备份。 完整数据库备份是指备份整个 Analytics Platform System (PDW) 数据库。 差异数据库备份只包含自上次完整备份后所做的更改。 用户数据库备份包含数据库用户和数据库角色。 master 数据库的备份包含登录信息。

有关 Analytics Platform System (PDW) 数据库备份的详细信息,请参阅 Analytics Platform System (PDW) 产品文档中的“备份和还原”。

语法

--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'

参数

database_name

要在其中创建备份的数据库的名称。 该数据库既可以是 master 数据库,也可以是用户数据库。

TO DISK = '\\UNC_path\backup_directory'

Analytics Platform System (PDW) 将写入备份文件的网络路径和目录。 例如,\\\xxx.xxx.xxx.xxx\backups\2012\Monthly\08.2012.Mybackup

  • 备份目录名称的路径必须已存在,并且必须指定为完全限定的通用命名约定 (UNC) 路径。
  • 备份目录 backup_directory 在运行备份命令前不得存在。 Analytics Platform System (PDW) 将创建备份目录。
  • 备份目录的路径不能是本地路径,并且不能是任何Analytics Platform System (PDW)设备节点上的位置。
  • UNC 路径和备份目录名称的最大长度为 200 个字符。
  • 必须以 IP 地址的形式指定服务器或主机。 不能以主机名称或服务器名称的形式指定。

DESCRIPTION = 'text'

指定备份的文字描述。 文本的最大长度为 255 个字符。

描述存储在元数据中,在使用 RESTORE HEADERONLY 还原备份标头时显示。

NAME = 'backup _name'

指定备份的名称。 备份名称可以与数据库名称不同。

  • 名称最长可达 128 个字符。
  • 不能包含路径。
  • 必须以字母或数字字符或下划线 (_) 开头。 允许使用的特殊字符包括下划线 (_)、连字符 (-) 或空格 ( )。 备份名称不能以空格字符结尾。
  • 如果指定位置已存在 backup_name,该语句会失败。

此名称存储在元数据中,在使用 RESTORE HEADERONLY 还原备份标头时显示。

DIFFERENTIAL

指定执行用户数据库的差异备份。 如果省略,默认执行完整数据库备份。 差异备份的名称不必与完整备份的名称匹配。 如需跟踪差异备份及其对应的完整备份,请考虑使用相同的名称并追加“full”或“diff”。

例如:

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

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

权限

要求具有 BACKUP DATABASE 权限,或者在 db_backupoperator 固定数据库角色中具有成员身份。 添加到 db_backupoperator 固定数据库角色的普通用户无法备份 master 数据库。 仅 sa、构造管理员或 sysadmin 固定服务器角色的成员可备份 master 数据库。

需要有权访问、创建和写入备份目录的 Windows 帐户。 还必须将 Windows 帐户名称和密码存储在Analytics Platform System (PDW)中。 若要将这些网络凭据添加到 Analytics Platform System (PDW),可使用 sp_pdw_add_network_credentials - Azure Synapse Analytics 存储过程。

有关如何管理 Analytics Platform System (PDW) 中凭据的详细信息,请参阅安全部分。

错误处理

BACKUP DATABASE 错误会在以下情况中发生:

  • 用户权限不足以执行备份。
  • Analytics Platform System (PDW) 对存储备份所在的网络位置没有正确的权限。
  • 数据库不存在。
  • 网络共享上已存在目标目录。
  • 目标网络共享不可用。
  • 目标网络共享没有用于备份的足够空间。 BACKUP DATABASE 命令在启动备份前不会确认是否存在足够的磁盘空间,因而可能在运行 BACKUP DATABASE 时生成磁盘空间不足错误。 磁盘空间不足时,Analytics Platform System (PDW) 会回滚 BACKUP DATABASE 命令。 若要缩小数据库的大小,可运行 DBCC SHRINKLOG (Azure Synapse Analytics)
  • 尝试在事务中启动备份。

一般备注

执行数据库备份前,请使用 DBCC SHRINKLOG (Azure Synapse Analytics) 缩小数据库的大小。

Analytics Platform System (PDW) 备份会以多个文件的集合的形式存储在相同目录中。

差异备份所需的时间通常比完整备份所需的时间少,并且可以更频繁地执行。 如果多个差异备份基于同一完整备份,每个差异备份会包含之前差异备份中的所有更改。

如果取消 BACKUP 命令,Analytics Platform System (PDW) 将删除目标目录以及为备份创建的任何文件。 如果 Analytics Platform System (PDW) 失去到共享的网络连接,则无法完成回滚。

完整备份和差异备份存储在不同的目录中。 指定完整备份和差异备份拥有共同来源时,不强制使用命名约定。 可通过自己的命名约定进行跟踪。 或者,可使用 WITH DESCRIPTION 选项添加描述,然后使用 RESTORE HEADERONLY 语句检索该描述来进行跟踪。

限制和局限

不能对 master 数据库执行差异备份。 仅支持对 master 数据库执行完整备份。

备份文件的存储格式仅适合使用 RESTORE DATABASE - Analytics Platform System 语句将备份还原到 Analytics Platform System (PDW) 设备。

使用 BACKUP DATABASE 语句的备份不能用于将数据或用户信息传输到 SMP SQL Server 数据库。 如需获取该功能,可使用远程表复制功能。 有关详细信息,请参阅 Analytics Platform System (PDW) 产品文档中的“远程表复制”。

Analytics Platform System (PDW) 使用 SQL Server 备份技术备份和还原数据库。 SQL Server 备份选项已预先配置为使用备份压缩。 不能设置压缩、校验和、块大小和缓冲区计数等备份选项。

任何给定时间都只能在设备上运行一个数据库备份或还原。 Analytics Platform System (PDW) 会让备份或还原命令排队,直到当前备份或还原命令完成。

用于还原备份的目标设备使用的计算节点数量至少应与源设备使用的计算节点数量相等。 目标设备拥有的计算节点数量可多于源设备,但不可少于源设备。

Analytics Platform System (PDW) 不跟踪备份的位置和名称,因为备份存储在设备以外的位置。

Analytics Platform System (PDW) 会跟踪数据库备份是成功还是失败。

仅当上次完整备份成功完成时才允许执行差异备份。 例如,假设在星期一创建 Sales 数据库的完整备份且备份成功完成。 随后在星期二创建 Sales 数据库的完整备份并失败。 此次失败后,不能基于星期一的完整备份创建差异备份。 在创建差异备份前,必须先创建成功的完整备份。

元数据

这些动态管理视图包含关于所有备份、还原和加载操作的信息。 信息在两次系统重启之间仍会保留。

性能

若要执行备份,Analytics Platform System (PDW) 首先会备份元数据,然后对存储在计算节点上的数据库数据执行并行备份。 数据会直接从每个计算节点复制到备份目录。 若要在将数据从计算节点移至备份目录的过程中获得最佳性能,Analytics Platform System (PDW) 可控制要并发复制数据的计算节点的数量。

锁定

在 DATABASE 对象上使用 ExclusiveUpdate 锁。

安全性

Analytics Platform System (PDW) 备份不存储在设备上。 因此,IT 团队负责管理备份安全的所有方面。 例如,这包括管理备份数据的安全、用于存储备份的服务器的安全和将备份服务器连接到 Analytics Platform System (PDW) 设备的网络基础结构的安全。

管理网络凭据

对备份目录的网络访问权限基于标准操作系统文件共享安全。 在执行备份前,需要创建或指定用于向备份目录验证 Analytics Platform System (PDW) 身份的 Windows 帐户。 此 Windows 帐户必须有权访问、创建和写入备份目录。

重要

若要降低数据的安全风险,建议指定一个 Windows 帐户专门用于执行备份和还原操作。 仅允许此帐户访问备份位置,不要授予对其他位置的访问权限。

需要通过运行 sp_pdw_add_network_credentials - Azure Synapse Analytics 存储过程,在 Analytics Platform System (PDW) 中存储用户名和密码。 Analytics Platform System (PDW) 使用 Windows 凭据管理器在控制节点和计算节点上存储及加密用户名和密码。 不使用 BACKUP DATABASE 命令备份凭据。

若要从 Analytics Platform System (PDW) 删除网络凭据,可使用 sp_pdw_remove_network_credentials - Azure Synapse Analytics

若要列出 Analytics Platform System (PDW) 中存储的所有网络凭据,可使用 sys.dm_pdw_network_credentials 动态管理视图。

示例

A. 添加备份位置的网络凭据

若要创建备份,Analytics Platform System (PDW) 必须具有备份目录的读/写权限。 以下示例显示如何添加用户凭据。 Analytics Platform System (PDW) 会存储这些凭据并将其用于备份和还原操作。

重要

出于安全原因,建议创建一个域帐户专门用于执行备份。

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

B. 删除备份位置的网络凭据

以下示例显示如何从 Analytics Platform System (PDW) 删除域用户凭据。

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

C. 创建用户数据库的完整备份

以下示例创建 Invoices 用户数据库的完整备份。 Analytics Platform System (PDW) 会创建 Invoices2013 目录并将备份文件保存到 \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Full 目录。

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

D. 创建用户数据库的差异备份

以下示例创建差异备份,其中包含自 Invoices 数据库上次完整备份以来所做的全部更改。 Analytics Platform System (PDW) 会创建 \\xxx.xxx.xxx.xxx\backups\yearly\Invoices2013Diff 目录用于存储文件。 描述“Invoices 2013 differential backup”会随备份的标头信息一起存储。

仅当 Invoices 的上次完整备份成功完成时才会运行差异备份。

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

E. 创建 master 数据库的完整备份

以下示例创建 master 数据库的完整备份,并将其存储在 \\\xxx.xxx.xxx.xxx\backups\2013\daily\20130722\master 目录中,其中 IP 是网络 IP 地址。

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

F. 创建设备登录信息的备份

master 数据库存储设备登录信息。 若要备份设备登录信息,需要备份 master 数据库。

以下示例创建 master 数据库的完整备份。

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

后续步骤

RESTORE DATABASE - 并行数据仓库