DBAtlas · 数据库运维图谱

SQL Server 备份策略、自动化与还原

SQL Server难度: 中级耗时: 30–90 分钟

为已安装的 SQL Server 实例（Linux 或 Windows；单实例、FCI 或 AG 副本）配置备份并从备份中恢复。备份： 选择恢复模式，每周完整 + 每日差异 + 每 n 分钟一次日志备份，COMPRESSION、CHECKSUM、证书加密，保留期与调度（SQL Server Agent T-SQL 作业或 Ola Hallengren；Express 版使用 Task Scheduler / cron），一次性 COPY_ONLY 备份，备份验证与备份链检查。恢复： 完整 / 差异 / 日志链还原，使用 STOPAT 还原到某个时间点（PITR），标记事务，使用 WITH MOVE 以其他名称或还原到其他服务器，数据文件丢失后的尾日志 + 还原，页还原，master / msdb / model 还原，登录名映射（孤立用户），以及为加密备份加载证书。

在场景卡片中选择要做的事；也可以在目录搜索中输入错误号（例如 824、4305、33111、18456）直接跳到对应场景。如需将表或查询结果导出到文件（数据迁移，不是备份），参见 使用 bcp 导出。

架构: 1 台服务器、故障转移群集实例或 Always On AG 副本（还原到其他服务器：源 + 目标）

场景

  • 建立备份策略（计划备份 + 保留期） — 新服务器或没有定期备份的数据库：配置完整 / 差异 / 日志作业、保留期和监控。
  • 执行一次性备份（例如变更前的 COPY_ONLY） — 在变更、补丁或升级之前，或为复制到测试环境而做的单次备份；COPY_ONLY 不会破坏备份链。
  • 验证备份（历史记录、VERIFYONLY、测试还原） — 用于证明备份确实可以还原：备份历史记录、VERIFYONLY、HEADERONLY / FILELISTONLY，以及还原到临时数据库进行测试 + CHECKDB。
  • 备份链是否完整？（LSN 断档、缺失文件） — 在还原之前或作为定期审核：根据 msdb 历史记录重建备份链，找出 LSN 断档、磁盘上不存在的文件以及中间插入的备份。不做任何更改。
  • 从备份还原数据库（备份链、STOPAT / STOPATMARK、MOVE / REPLACE、其他服务器） — 数据被删除或被错误更新，需要数据库副本，或要迁移到其他服务器；实例正在运行。完整 + 差异 + 日志链，还原到某个时间点或标记事务，使用不同名称 / 目录，借助尾日志还原到最后时刻。
  • 数据库无法打开：数据文件丢失或损坏（尾日志 + 还原） — 数据库处于 RECOVERY_PENDING 或 SUSPECT 状态，.mdf/.ndf 无法读取，但实例正在运行：先使用 NO_TRUNCATE 进行尾日志备份，再还原到最后时刻。在操作完成之前数据库不可用。
  • 少数页损坏（Msg 823 / 824）：页还原 — 查询或 CHECKDB 在特定页上报错（msdb.dbo.suspect_pages）：只从备份还原损坏的页。在 Enterprise/Developer 版中数据库保持联机，在 Standard/Express 版中会短时间脱机。
  • 还原 master 数据库（单用户模式） — master 损坏，或登录名 / 链接服务器 / 服务器设置丢失：以单用户模式（-m）启动实例，还原 master，SQL Server 随后自行关闭。需要停机；如果实例完全无法启动，先重新生成系统数据库。
  • 还原 msdb 或 model 数据库 — 作业、备份历史记录、Database Mail（msdb）或新数据库模板（model）损坏 / 丢失：使用同一内部版本上做的备份 WITH REPLACE 还原；Agent 会短暂停止。
  • 还原后用户无法连接（孤立用户、登录名映射） — 数据库被迁移或还原到其他服务器，应用程序收到 'Login failed'：从源服务器连同 SID 和密码哈希一起传输登录名，其余用户映射到登录名。数据库保持联机。
  • 加密备份无法还原（缺少证书，Msg 33111） — 备份（或启用 TDE 的数据库的备份）使用证书加密，而本服务器上没有该证书：找出 thumbprint，连同私钥加载证书，然后验证备份。不触及现有数据库。

开始之前

  • 已与业务部门书面确定 RPO（可接受的数据丢失）和 RTO（可接受的停机时间）。所选方案：…最多 … 分钟的数据丢失…回到最后一次完整/差异备份（SIMPLE：最多一天的数据丢失）…；还原时间将通过对最大数据库进行测试还原来测量。
  • SQL Server 正在运行；已安装 mssql-tools18（sqlcmd），且 … 为 sysadmin。运行脚本之前，以不回显的方式将密码读入变量：read -rsp '密码: ' SQLCMDPASSWORD; export SQLCMDPASSWORD，然后执行 sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh。
  • 已以管理员身份打开 PowerShell（开始 → "PowerShell" → 右键 → Run as administrator；标题栏显示 Administrator:），使用的 Windows 帐户在 SQL Server 中为 sysadmin；sqlcmd 在 PATH 中。
  • 备份目录（…）位于与数据磁盘不同的磁盘上，可用空间至少够存放一周的备份（粗略估算：已用数据大小 × 2 + 每日变化量 × 天数；使用压缩时通常少 3–5 倍）。
  • 备份目录（…）位于与数据磁盘不同的磁盘上，可用空间至少够存放一周的备份（粗略估算：已用数据大小 × 2 + 每日变化量 × 天数；使用压缩时通常少 3–5 倍）。
  • 已知各数据库的大小；备份窗口（完整备份所需时间）不与业务时间重叠。
  • 已确定将备份复制到服务器之外（其他服务器、NAS、对象存储、备份软件）的方法。与数据位于同一磁盘的备份在磁盘或服务器丢失时毫无用处。
  • 还原目标的 SQL Server 版本与执行备份的版本相同或更新。在较新版本上做的备份无法还原到较旧版本（例如 2022 → 2019 不可行）。
  • 已通知应用团队并停止应用程序：被覆盖还原的数据库的所有连接都会断开，还原期间数据库不可用。
  • 目标上存在数据目录（…）和日志目录（…），所有者为 mssql，可用空间足以容纳要还原的数据库的文件大小（不是备份大小，而是 FILELISTONLY 中的 Size 值）。
  • 目标上存在数据目录（…）和日志目录（…），SQL Server 服务帐户具有写入权限，可用空间足以容纳要还原的数据库的文件大小。
  • 可以从目标服务器（…）…通过 SSH（root 或具有 sudo 权限的用户）…通过 SMB 管理共享（…）…访问源服务器（…）；可以复制备份文件。

指南阶段

  • 当前状态 — 列出数据库和最近的备份
  • 备份策略 — 准备备份目录 · 准备备份目录 · 设置恢复模式（…） · 设置 AG 备份首选项（…） · 检查首选副本 · 创建证书并备份（加密） …
  • 一次性备份 — …执行 COPY_ONLY 完整备份…执行完整备份…执行差异备份…执行日志备份…（…） · 检查备份历史记录（msdb.dbo.backupset）
  • 还原 — 从备份历史记录中找出还原链 · 将备份文件复制到目标服务器 · 将备份文件复制到目标服务器 · 在目标服务器上加载证书 · 在目标服务器上加载证书 · 检查备份文件（VERIFYONLY、HEADERONLY、FILELISTONLY） …
  • 备份验证 — 检查备份历史记录（msdb.dbo.backupset） · 检查备份文件（VERIFYONLY、HEADERONLY、FILELISTONLY） · 执行测试还原并运行 DBCC CHECKDB
  • 备份链检查 — 诊断：备份链的状态（数据库 + msdb 历史记录） · 备份文件是否在磁盘上？ · 备份文件是否在磁盘上？ · 验证备份链中的每个备份（VERIFYONLY） · 后续：如果备份链断开，执行新的完整备份
  • 数据文件丢失 / 损坏：尾日志 + 还原 — 诊断：数据库及其文件的状态 · 诊断：文件是否在磁盘上，日志文件是否完好？ · 诊断：文件是否在磁盘上，日志文件是否完好？ · 安全网：复制剩余的文件 · 安全网：复制剩余的文件 · 执行尾日志备份（NO_TRUNCATE） …
  • 页还原（损坏页） — 诊断：找出损坏页（suspect_pages、CHECKDB） · 诊断：该页属于哪个表 / 索引？ · 安全网：执行日志备份，停止日志备份作业 · 根据 msdb 历史记录生成还原顺序（PAGE） · 执行尾日志备份（NORECOVERY）：数据库脱机 · 还原损坏的页并应用日志备份（NORECOVERY） …
  • master restore — 诊断：备份文件、版本以及将丢失的内容 · 诊断：SQL Server 为什么无法启动？ · 诊断：SQL Server 为什么无法启动？ · 重新生成系统数据库（master 无法启动） · 重新生成系统数据库（master 无法启动） · 以单用户模式启动 SQL Server …
  • msdb / model 还原 — 诊断：状态、版本和备份文件 · 安全网：备份当前的 … 数据库 · 停止 SQL Server Agent · 还原 … 数据库（REPLACE） · 启动 SQL Server Agent · 验证
  • 登录名映射（孤立用户） — 诊断：找出孤立用户（目标服务器） · 在源服务器上生成登录名脚本（SID + 密码哈希） · 在源服务器上生成登录名脚本（SID + 密码哈希） · 将登录名脚本复制到目标服务器并执行 · 将登录名脚本复制到目标服务器并执行 · 将其余用户映射到登录名（ALTER USER WITH LOGIN） …
  • 加密备份：加载证书 — 诊断：本服务器上的证书和 master key · 诊断：证书文件的 thumbprint · 诊断：证书文件的 thumbprint · 连同私钥加载证书 · 连同私钥加载证书 · 验证：现在可以读取备份 …
按您的环境打开分步计划 →