SQL BACKUP [1]- 备份数据库-介绍

SQL BACKUP [1]- 备份数据库-介绍

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

2、语法：

--备份整个数据库--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

3、参数：

  • DATABASE //指定一个完整数据库备份。
    • 如果指定了一个文件和文件组的列表，则仅备份该列表中的文件和文件组。 在进行完整数据库备份或差异数据库备份的过程中，SQL Server 会备份足够多的事务日志，以便在还原备份时生成一个一致的数据库。
    • 还原由 BACKUP DATABASE（“数据备份”）创建的备份时，将还原整个备份。 只有日志备份才能还原到备份中的特定时间或事务。
  • LOG //指定仅备份事务日志。{ database_name | @database\_name\_var } 是备份事务日志、部分数据库或完整的数据库时所用的源数据库。 如果作为变量 (@database_name_var) 提供，则可以将此名称指定为字符串常量 (@database_name_var=databasename) 或指定为字符串数据类型（ntext 或 text 数据类型除外）的变量。　
    • 该日志是从上一次成功执行的日志备份到当前日志的末尾。 必须创建完整备份，才能创建第一个日志备份。
    • 通过在 RESTORE LOG 语句中指定 WITH STOPAT、STOPATMARK 或 STOPBEFOREMARK，可以将日志备份还原到备份中的特定时间或事务。
  • <file_or_filegroup> [ ,...n ] 只能与 BACKUP DATABASE 一起使用，用于指定某个数据库文件或文件组包含在文件备份中，或指定某个只读文件或文件组包含在部分备份中。
  • FILE = { logical_file_name | @logical\_file\_name\_var } 是文件或变量的逻辑名称，其值等于要包含在备份中的文件的逻辑名称。
  • FILEGROUP = { logical_filegroup_name | @logical\_filegroup\_name\_var } 文件组或变量的逻辑名称，其值等于要包含在备份中的文件组的逻辑名称。 在简单恢复模式下，只允许对只读文件组执行文件组备份。
    • 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 子句）。MIRROR TO <backup_device> [ ,...n ] 指定一组辅助备份设备（最多三个），其中每个设备都镜像 TO 子句中指定的备份设备。 MIRROR TO 子句和 TO 子句必须指定相同类型和数量的备份设备。 最多可以使用三个 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 的 URL。 指定磁盘文件或磁带设备，或者 Microsoft Azure 存储服务。 此 URL 格式用于创建到 Microsoft Azure 存储服务的备份。
    • 如果某一磁盘设备不存在，也可以在 BACKUP 语句中指定它。 如果存在物理设备且 BACKUP 语句中未指定 INIT 选项，则备份将追加到该设备。
    • n 一个占位符，表示最多可以在逗号分隔的列表中指定 64 个备份设备。

4、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_SNAPSHOT 和 BACKUP LOG TO URL WITH FILE_SNAPSHOT 之间的唯一区别是后者会截断事务日志，而前者不会。 借助 SQL Server 快照备份，在完成 SQL Server 建立备份链时所需的初始完整备份之后，只需单个事务日志备份即可将数据库还原到事务日志备份的时间点。 此外，只需两次事务日志备份即可将数据库还原到两次事务日志备份之间的时间点。
  • DIFFERENTIAL
    • 只能与 BACKUP DATABASE 一起使用，指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。 差异备份一般会比完整备份占用更少的空间。 对于上一次完整备份后执行的所有单个日志备份，使用该选项可以不必再进行备份。
  • ENCRYPTION 用于指定将备份加密。 可指定加密备份所用的加密算法，或指定 NO_ENCRYPTION 以不加密备份。 建议进行加密以帮助保护备份文件的安全。 可指定的算法的列表如下：
    • AES_128
    • AES_192
    • AES_256
    • TRIPLE_DES_3KEY
    • NO_ENCRYPTION
  • 如果决定加密，则还必须使用加密程序选项指定加密程序：SERVER CERTIFICATE 和 SERVER ASYMMETRIC KEY 是在 master 数据库中创建的证书和非对称密钥。 有关详细信息，请分别参阅 CREATE CERTIFICATE 和 CREATE ASYMMETRIC KEY。
    • SERVER CERTIFICATE = Encryptor_Name
    • SERVER ASYMMETRIC KEY = Encryptor_Name

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

  • COPY_ONLY //指定备份为“仅复制备份”，该备份不影响正常的备份顺序。 仅复制备份是独立于定期计划的常规备份而创建的。 仅复制备份不会影响数据库的总体备份和还原过程。
    • 应在出于特殊目的而进行备份的情况下使用仅复制备份，例如在进行联机文件还原前备份日志。 通常，仅复制日志备份仅使用一次即被删除。
    • 与 BACKUP DATABASE 一起使用时，COPY_ONLY 选项创建的完整备份不能用作差异基准。 差异位图不会被更新，因此差异备份的表现就像仅复制备份不存在一样。 后续差异备份将最新的常规完整备份用作它们的基准。
    • 重要提示：如果将 DIFFERENTIAL 和 COPY_ONLY 一起使用，则忽略 COPY_ONLY 并创建差异备份。
    • 与 BACKUP LOG 一起使用时，COPY_ONLY 选项将创建“仅复制日志备份”，该备份不会截断事务日志。 仅复制日志备份对日志链没有任何影响，因此其他日志备份的表现就像仅复制备份不存在一样。
  • { COMPRESSION | NO_COMPRESSION } 仅适用于 SQL Server 2008 Enterprise 和更高版本；指定是否对此备份执行备份压缩，覆盖服务器级默认设置。DESCRIPTION = { 'text' | @text\_variable } 指定说明备份集的自由格式文本。 该字符串最长可达 255 个字符。
    • 安装时，默认行为是不进行备份压缩。 但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。 有关查看此选项的当前值的信息，请参阅查看或更改服务器属性面板。
    • 有关对支持透明数据加密 (TDE) 的数据库使用备份压缩的信息，请参阅备注部分。
      • COMPRESSION //显式启用备份压缩。
      • NO_COMPRESSION //显式禁用备份压缩。
  • 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) 提供，则该日期必须采用已配置系统日期/时间的格式，并指定为下列类型之一：RETAINDAYS = { days | @days\_var } 指定必须经过多少天才可以覆盖该备份媒体集。 如果作为变量 (@days_var) 提供，则必须指定为整数。
    • 字符串常量 (@date_var = date)
    • 字符串数据类型（ntext 或 text 数据类型除外）的变量
    • smalldatetime
    • datetime 变量
    • 例如：
      • 'Dec 31, 2020 11:59 PM'
      • '1/1/2021'

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

  • { NOINIT | INIT } 控制备份操作是追加到还是覆盖备份媒体中的现有备份集。 默认为追加到介质中最新的备份集 (NOINIT)。
    • NOINIT 表示备份集将追加到指定的媒体集上，以保留现有的备份集。 如果为介质集定义了介质密码，则必须提供密码。 NOINIT 是默认设置。
    • 有关详细信息，请参阅 媒体集、媒体簇和备份集。
  • INIT 指定应覆盖所有备份集，但是保留媒体标头。 如果指定了 INIT，将覆盖该设备上所有现有的备份集（如果条件允许）。 默认情况下，BACKUP 将检查下列条件，如果其中的任一条件存在，都不会覆盖备份介质：
    • 所有备份集都未过期。 有关详细信息，请参阅 EXPIREDATE 和 RETAINDAYS 选项。
    • 如果 BACKUP 语句给出了备份集名，则该备份集名与备份介质上的名称不匹配。 有关详细信息，请参阅本部分前面介绍的 NAME 选项。
    • 若要替代这些检查，请使用 SKIP 选项。
    • 有关详细信息，请参阅 媒体集、媒体簇和备份集。
  • { NOSKIP | SKIP } 控制备份操作是否在覆盖媒体中的备份集之前检查它们的过期日期和时间。
    • NOSKIP 指示 BACKUP 语句在可以覆盖媒体上的所有备份集之前先检查它们的过期日期。 此选项为默认行为。
    • SKIP 禁用备份集的过期和名称检查，这些检查一般由 BACKUP 语句执行以防覆盖备份集。 有关 { NOINIT | INIT } 和 { NOSKIP | SKIP } 之间交互的信息，请参阅本主题后面的“备注”。 若要查看备份集的过期日期，请查询 backupset 历史记录表的 expiration_date 列。
  • { NOFORMAT | FORMAT } 指定是否应该在用于此备份操作的卷上写入媒体标头，以覆盖任何现有的媒体标头和备份集。MEDIADESCRIPTION = { text | @text\_variable } 指定媒体集的自由格式文本说明，最多为 255 个字符。
    • NOFORMAT 指定备份操作在用于此备份操作的媒体卷上保留现的有媒体标头和备份集。 此选项为默认行为。
    • FORMAT 指定创建新的媒体集。 FORMAT 将使备份操作在用于备份操作的所有介质卷上写入新的介质标头。 卷的现有内容将变为无效，因为覆盖了任何现有的介质标头和备份集。
    • 重要提示：请谨慎使用 FORMAT。 格式化介质集的任何一个卷都将使整个介质集不可用。 例如，如果初始化现有条带介质集中的单个磁带，则整个介质集都将变得不可用。
    • 指定 FORMAT 即表示 SKIP；SKIP 无需显式声明。
  • 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。

7、数据传输选项

  • BUFFERCOUNT = { buffercount | @buffercount\_variable } 指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数；但是，较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } 指定要在 SQL Server 和备份介质之间使用的最大传输单元（字节）。 可能的值是 65536 字节 (64 KB) 的倍数，最多可到 4194304 字节 (4 MB)。
    • 缓冲区使用的总空间是由以下公式确定：BUFFERCOUNT * MAXTRANSFERSIZE。

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

{ 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 尝试执行尾日志备份。

9、兼容性选项

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

10、监视选项

  • STATS [ = percentage ] 每当另一个 percentage 完成时显示一条消息，并用于测量进度。 如果省略百分比，则 SQL Server 在每完成 10% 就显示一条消息。
    • STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。 这是指定百分比的近似值；
    • 例如，当 STATS=10 时，如果完成进度为 40%，则该选项可能显示 43%。 对于较大的备份集，这不是问题，因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。

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

  • { REWIND | NOREWIND }
    • REWIND //指定 SQL Server 释放和倒带磁带。 REWIND 是默认设置。
    • NOREWIND //指定在备份操作之后 SQL Server 让磁带一直处于打开状态。 在对磁带执行多个备份操作时，可以使用此选项来帮助改进性能。
    • NOREWIND 包含 NOUNLOAD，并且这些选项在单个 BACKUP 语句中不兼容。
  • { UNLOAD | NOUNLOAD }
    • UNLOAD //指定在备份完成后自动重绕并卸载磁带。 会话开始时 UNLOAD 是默认值。
    • NOUNLOAD //指定在 BACKUP 操作之后磁带继续在磁带机中加载。

12、特定于日志的选项 //这些选项仅与 BACKUP LOG 一起使用。

  • { NORECOVERY | STANDBY = undo_file_name }
    • NORECOVERY //备份日志的尾部并使数据库处于 RESTORING 状态。 当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时，NORECOVERY 很有用。
    • 若要执行最大程度的日志备份（跳过日志截断）并自动将数据库置于 RESTORING 状态，请同时使用 NO_TRUNCATE 和 NORECOVERY 选项。
    • 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。 有关数据库状态的详细信息，请参阅数据库状态。

 

创建时间：2021.07.27　　更新时间：

博客园 滔Roy https://www.cnblogs.com/guorongtao 希望内容对你所有帮助，谢谢！
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/guorongtao/p/15065999.html