SQL Server 备份参数如何优化备份效率？

核心备份类型

在深入参数之前,首先要明确三种核心的备份类型，因为它们决定了备份的基本行为：

1. 完整备份

   • 作用：备份整个数据库，包括部分事务日志，这是所有恢复策略的基础。
   • 特点：备份文件最大，耗时最长，但恢复最简单（只需一个备份文件即可恢复到备份时间点）。

2. 差异备份

   • 作用：备份自上次完整备份以来发生变化的所有数据页。
   • 特点：比完整备份小、快，它必须基于一个完整的备份，恢复时，需要先恢复完整备份，再恢复最新的差异备份。

3. 事务日志备份

   • 作用：备份自上次事务日志备份（或自上次完整备份/差异备份后第一次日志备份）以来所有的事务日志记录。
   • 特点：
     • 仅用于完整恢复模式和大容量日志恢复模式，在简单恢复模式下，事务日志会被自动截断，无法进行日志备份。
     • 备份文件非常小,速度极快。
     • 实现了时点恢复，可以将数据库恢复到日志备份中的任何一个精确时间点。
     • 恢复顺序：完整备份 -> 差异备份（可选） -> 一系列日志备份（按顺序）。

BACKUP DATABASE 命令详解

这是备份数据库数据文件和部分日志的命令。

语法结构

BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ , ] [ BUFFERCOUNT = { buffercount | @buffercount_variable } ]
    [ , ] [ { COMPRESSION | NO_COMPRESSION } ]
    [ , ] [ COPY_ONLY ]
    [ , ] [ DESCRIPTION = { 'text' | @text_variable } ]
    [ , ] [ DIFFERENTIAL ]
    [ , ] [ ENCRYPTION ( ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , SERVER CERTIFICATE = certificate_name | SERVER ASYMMETRIC KEY = asymmetric_key_name ) ]
    [ , ] [ FILEGROUP = { filegroup_name | @filegroup_name_var } [ ,...n ] ]
    [ , ] [ { MAXTRANSFERSIZE = { size | @size_variable } } ]
    [ , ] [ { NO_CHECKSUM | CHECKSUM } ]
    [ , ] [ { NO_TRUNCATE | TRUNCATE_LOG } ] -- 注意：NO_TRUNCATE 主要用于 BACKUP LOG
    [ , ] [ { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
    [ , ] [ { NOUNLOAD | UNLOAD } ]
    [ , ] [ { RESTART | NORESTART } ]
    [ , ] [ RETAINDAYS = { days | @days_variable } ] [ , ] [ EXPIREDATE = { date | @date_variable } | EXPIREDATE = { date | @date_variable } ]
    [ , ] [ STATS = [ percentage ] ]
    [ , ] [ { STOPAT | STOPATMARK | STOPBEFOREMARK } = { 'date_time' | 'lsn:lsn_number' | 'mark_name' [ 'after_mark' ] } ]
    [ , ] [ { NAME = { backup_set_name | @backup_set_name_var } } ]
    [ , ] [ MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ , ] [ MEDIANAME = { media_name | @media_name_variable } ]
    [ , ] [ MEDIAPASSWORD = { media_password | @media_password_variable } ]
    [ , ] [ NOFORMAT | { NOINIT | INIT } ]
    [ , ] [ NOSKIP | { SKIP | SNAPSHOT } ]
    [ , ] [ { ONLY_RESTORE | NOONLY_RESTORE } ]
    [ , ] [ PASSWORD = { password | @password_variable } ]
    [ , ] [ REWIND | NOREWIND ]
    [ , ] [ { UNLOAD | NOUNLOAD } ]
    [ , ] [ { WITH | STATS [ , ] | CHECKSUM [ , ] | STOPAT [ , ] | CONTINUE_AFTER_ERROR } ]
]
[; ]

关键参数分类详解

目标设备

指定备份文件存储在哪里。

• TO <backup_device>: 必须指定。
  • 磁盘文件: TO DISK = 'C:\Backup\MyDB.bak'
  • 磁带: TO TAPE = '\\.\Tape0'
  • 逻辑设备: 需要先通过 sp_addumpdevice 创建逻辑备份设备，然后使用 TO MyBackupDevice。

备份选项

• DIFFERENTIAL: 创建差异备份，如果省略，则创建完整备份。

  -- 创建完整备份
  BACKUP DATABASE MyDB TO DISK = 'C:\Backup\MyDB_Full.bak';
  -- 创建差异备份
  BACKUP DATABASE MyDB TO DISK = 'C:\Backup\MyDB_Diff.bak' WITH DIFFERENTIAL;

• COPY_ONLY: 创建一个不影响常规备份序列的“仅复制”备份。

  • 完整备份的 COPY_ONLY: 不会影响后续的差异数据库备份。
  • 日志备份的 COPY_ONLY: 不会截断事务日志，不影响后续的常规日志备份。
  • 适用场景: 在不干扰计划好的备份和还原策略的情况下，进行一次性备份（在升级前）。

• FILEGROUP = [...]: 备份特定的文件组或文件，这对于非常大的数据库非常有用，可以实现文件级恢复。

  -- 备份特定文件组
  BACKUP DATABASE MyDB FILEGROUP = 'FG_Sales' TO DISK = 'C:\Backup\MyDB_FG_Sales.bak';

高性能与可靠性

• BUFFERCOUNT = buffercount: 指定用于备份操作的 I/O 缓冲区数量，增加此值可以提高吞吐量，尤其是在高延迟的存储上（如网络存储）。

• MAXTRANSFERSIZE = size: 指定 SQL Server 和备份设备之间一次读写操作的最大字节数（以 KB 为单位），较大的值可以提高性能，尤其是在使用高性能存储时。

• CHECKSUM / NO_CHECKSUM:

  • CHECKSUM: 在写入数据前对备份页面进行校验和计算，并在读取时进行验证，这会增加少量开销，但能确保备份数据的完整性，防止因存储介质损坏导致的数据问题。
  • NO_CHECKSUM: 默认值，不进行校验和计算。

• COMPRESSION / NO_COMPRESSION:

  • COMPRESSION: 强烈推荐，使用内置的备份压缩算法，可以显著减小备份文件大小（通常可减少 60%-80%），并提高备份/还原速度。
  • NO_COMPRESSION: 不压缩备份文件，默认值。

加密

• ENCRYPTION (...): 对备份文件进行加密，防止未经授权的访问。

  • 需要先在 SQL Server 中创建证书 或非对称密钥。
  • 算法: AES_128, AES_192, AES_256, TRIPLE_DES_3KEY，推荐使用 AES。
  • 示例:

    -- 1. 首先创建一个服务器证书（如果还没有）
    CREATE CERTIFICATE MyDB_Backup_Cert
    WITH SUBJECT = 'MyDB Backup Encryption Certificate';

  -- 2. 执行加密备份 BACKUP DATABASE MyDB TO DISK = 'C:\Backup\MyDB_Encrypted.bak' WITH ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = MyDB_Backup_Cert );

媒质集管理

这些参数控制如何覆盖或追加到备份设备（磁盘文件）。

• INIT / NOINIT:
  • INIT: 覆盖，如果备份设备已存在，则删除其所有现有备份集，并写入新的备份集，这是最常用的选项。
  • NOINIT: 追加，将新的备份集追加到设备文件的末尾，可以用于将多个备份放在同一个文件中（不推荐，难以管理）。
• MEDIADESCRIPTION: 为整个媒体集（磁盘文件）添加描述信息。
• MEDIANAME: 为媒体集指定一个名称，如果使用 INIT，新备份的媒体名称必须与现有媒体名称匹配，否则会失败。
• MEDIAPASSWORD: 为媒体集设置密码，访问备份文件时需要此密码。

还原选项

这些参数通常在准备用于还原的链式备份时使用。

• NORECOVERY: 重要，备份完成后，数据库将处于“还原中”状态，无法用户访问，这允许你继续应用下一个备份（一个差异备份或另一个日志备份），这是备份链的中间步骤。
• RECOVERY: 默认值，备份完成后，数据库恢复为可正常使用的状态，这是最后一个备份操作（完整备份链的最后一个日志备份）应使用的选项。
• STANDBY = undo_file_name: 类似于 NORECOVERY，但允许数据库处于只读状态，以便在恢复过程中进行查询。undo_file_name 是一个用于存储回滚操作的文件的路径。

其他实用参数

• STATS = percentage: 显示备份操作的进度。STATS = 10 表示每完成 10% 的进度就显示一条消息。
• DESCRIPTION = 'text': 为备份集添加描述性文本，方便日后识别。
• RETAINDAYS = days / EXPIREDATE = date: 设置备份文件的保留策略，SQL Server 不会强制执行此策略，它主要用于记录和提醒。
• TRUNCATE_LOG: 主要用于 BACKUP LOG，表示在备份后截断非活动的事务日志，释放空间，在简单恢复模式下是自动的。

BACKUP LOG 命令详解

此命令专门用于备份事务日志。

核心语法

BACKUP LOG { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    -- ... (与 BACKUP DATABASE 大部分参数相同)
    [ , ] [ { NO_TRUNCATE | TRUNCATE_LOG } ] -- 关键区别
    [ , ] [ { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
    -- ... (其他参数)
]

BACKUP LOG 的关键区别

1. NO_TRUNCATE:

   • 作用: 即使数据库处于“正在还原”状态或因故障而关闭，也能备份事务日志。
   • 场景: 这是在发生灾难后（如数据文件损坏），你需要备份当前的日志以实现点恢复时使用的，它会备份所有未提交的事务，以便在恢复后重做或回滚。
   • 注意: 使用 NO_TRUNCATE 时，日志不会截断，可能会很快占满磁盘空间，恢复后应尽快进行常规日志备份。

2. TRUNCATE_LOG:

   • 作用: 这是默认行为，在成功备份日志后，会截断非活动的事务日志，释放日志空间。
   • 场景: 日常的、计划性的日志备份。

综合示例

假设我们有一个名为 SalesDB 的数据库，我们希望建立一个标准的备份策略。

准备工作：创建备份目录和逻辑设备（可选）

-- 创建备份文件夹
-- 在操作系统上手动创建，C:\SQL_Backups\SalesDB
-- （可选）创建逻辑备份设备
USE master;
GO
EXEC sp_addumpdevice
    @deviceType = 'DISK',
    @logicalName = 'SalesDB_Full_Backup',
    @physicalName = 'C:\SQL_Backups\SalesDB\SalesDB_Full.bak';
GO

完整备份（使用压缩和校验和）

BACKUP DATABASE SalesDB
TO DISK = 'C:\SQL_Backups\SalesDB\SalesDB_Full.bak'
WITH
    NAME = 'SalesDB-Full-Backup-20251027',
    DESCRIPTION = 'Full backup of SalesDB on 2025-10-27',
    COMPRESSION,
    CHECKSUM,
    INIT, -- 覆盖旧备份
    STATS = 10;
GO

差异备份

-- 在完整备份后，数据库有了一些数据变更
BACKUP DATABASE SalesDB
TO DISK = 'C:\SQL_Backups\SalesDB\SalesDB_Diff.bak'
WITH
    NAME = 'SalesDB-Diff-Backup-20251027',
    DESCRIPTION = 'Differential backup of SalesDB on 2025-10-27',
    DIFFERENTIAL, -- 指定为差异备份
    COMPRESSION,
    INIT,
    STATS = 10;
GO

事务日志备份

-- 在差异备份后，又有事务发生
BACKUP LOG SalesDB
TO DISK = 'C:\SQL_Backups\SalesDB\SalesDB_Log_1.trn'
WITH
    NAME = 'SalesDB-Log-Backup-1-20251027',
    DESCRIPTION = 'Log backup #1 for SalesDB',
    COMPRESSION,
    INIT,
    STATS = 10;
GO
-- 又有一些事务发生
BACKUP LOG SalesDB
TO DISK = 'C:\SQL_Backups\SalesDB\SalesDB_Log_2.trn'
WITH
    NAME = 'SalesDB-Log-Backup-2-20251027',
    DESCRIPTION = 'Log backup #2 for SalesDB',
    COMPRESSION,
    INIT,
    STATS = 10;
GO

灾难后的日志备份（使用 NO_TRUNCATE） 假设 SalesDB 的数据文件损坏，但日志文件可用，我们需要备份当前的日志以恢复到故障点。

BACKUP LOG SalesDB
TO DISK = 'C:\SQL_Backups\SalesDB\SalesDB_Log_Before_Crash.trn'
WITH
    NAME = 'SalesDB-Log-Backup-Before-Crash',
    NO_TRUNCATE, -- 关键参数，即使数据库不可用也能备份日志
    INIT;
GO

掌握这些参数,你就可以为 SQL Server 数据库设计出健壮、高效且安全的备份策略。