SQL Server 数据库维护计划建立全教程

为什么需要维护计划？

想象一下你的数据库就像一辆汽车，如果不定期保养（更换机油、检查轮胎、清洗发动机），它迟早会出故障，性能下降，甚至抛锚，数据库也是如此,定期的维护可以：

sql 数据库维护计划建立教程
（图片来源网络，侵删）
  1. 保证性能：定期重建和重组索引,可以提高查询速度。
  2. 释放空间：定期清理日志和删除旧数据,可以回收磁盘空间。
  3. 确保数据完整性和一致性：定期执行数据库完整性检查,可以发现并修复潜在问题。
  4. 防止数据丢失：定期备份数据库,是数据恢复的最后防线。
  5. 自动化任务：将繁琐、重复的维护工作自动化，解放 DBA（数据库管理员）的时间和精力。

准备工作：你需要什么？

  1. SQL Server 实例：你拥有一个 SQL Server 服务器（可以是本地实例或远程服务器）。
  2. SQL Server Management Studio (SSMS)：这是管理 SQL Server 的主要工具。
  3. 适当的权限：执行维护任务需要 sysadmin 服务器角色或 db_owner 数据库角色的权限。

维护计划的核心组件（任务）

一个维护计划通常由一系列任务组成，通过逻辑连接（如成功后执行、失败后执行）串联起来,以下是几个最核心的任务：

任务名称 作用 说明
备份数据库 最重要的任务 创建完整、差异或事务日志备份，用于灾难恢复。
检查数据库完整性 检查数据页和记录的物理和逻辑错误。 包括 CHECKDB 命令，可以及时发现并修复数据损坏。
重组索引 提高索引的读取性能，但不释放空间。 适用于碎片率较低（5%-30%）的索引，速度较快。
重建索引 彻底消除碎片，释放空间，并提高性能。 适用于碎片率很高（>30%）的索引，会锁定表，消耗更多资源。
执行 SQL Server 代理作业 运行一个预先定义好的作业。 可以用来执行自定义的脚本，如发送报告、清理特定表等。
清理历史记录 清理维护计划本身生成的日志和报告。 防止 msdb 数据库被日志填满。

手把手创建维护计划

我们将创建一个典型的维护计划,包含以下步骤：

  1. 检查数据库完整性。
  2. 备份数据库（完整备份）。
  3. 重组和重建索引。
  4. 清理维护历史记录。

步骤 1：打开“管理”文件夹

  1. 启动 SSMS 并连接到你的 SQL Server 实例。
  2. 在 对象资源管理器 中,展开服务器节点。
  3. 展开 管理 文件夹，你会看到 维护计划 选项。

步骤 2：创建新的维护计划

  1. 右键单击 维护计划，然后选择 维护计划...。
  2. 在弹出的窗口中，给你的维护计划起一个有意义的名字，Daily_Maintenance_Plan。
  3. 点击 确定。

步骤 3：设计维护计划界面

点击确定后，你会看到一个设计界面,主要包括：

  • 工具箱：左侧,包含了所有可用的维护任务。
  • 设计 surface：中间的空白区域,用于拖放和连接任务。
  • 属性窗口：右侧,用于配置选中任务的详细参数。

步骤 4：添加并配置任务

我们将按顺序添加任务。

sql 数据库维护计划建立教程
（图片来源网络，侵删）
任务 1：检查数据库完整性
  1. 从 工具箱 中，将 检查数据库完整性 任务拖拽到设计界面上。
  2. 双击该任务,打开其属性窗口。
  3. 数据库：选择你要维护的数据库，如果你想对所有数据库执行，可以选择 <所有数据库>，对于新手,建议先选择一个具体的数据库进行测试。
  4. 要求所有文件均在线：建议勾选此项,避免在文件离线时检查失败。
  5. 修复页错误：谨慎勾选，这会尝试修复找到的错误，但可能需要独占锁，影响业务，通常建议在维护窗口期执行,并先备份数据库。
  6. 点击 确定 保存。
任务 2：备份数据库
  1. 从 工具箱 中，将 备份数据库 任务拖拽到设计界面上。
  2. 双击打开属性窗口。
  3. 数据库：同样选择目标数据库。
  4. 备份组件：选择 数据库。
  5. 备份类型：选择 完整。
  6. 目标：
    • 点击 按钮。
    • 删除 默认的 disk 条目。
    • 点击 添加，选择 文件。
    • 在 备份文件 路径中，指定一个可靠的备份位置。强烈建议使用动态路径，D:\Backup\YourDB\YourDB_FULL_YYYYMMDD.bak，你可以使用 [DATE] 或 [TIME] 等宏来自动生成文件名。
  7. overwrite media：选择 覆盖所有现有备份集，这样每天只会保留一个最新的备份文件,避免空间浪费。
  8. 验证备份：勾选此项，SQL Server 会在备份完成后验证备份集是否可用，虽然会慢一点,但更可靠。
  9. 点击 确定 保存。
任务 3：优化索引（重组和重建）

这个任务需要两个子任务：先重组，后重建，因为重组可以在不锁表的情况下进行,而重建会消耗更多资源。

  1. 添加“重组索引”任务：

    • 从工具箱拖出 重组索引 任务。
    • 双击打开属性,选择目标数据库。
    • 限制：你可以设置一个阈值，仅当碎片超过 10% 时才执行”,这是一个很好的优化。
    • 点击 确定。

  2. 添加“重建索引”任务：

    • 从工具箱拖出 重建索引 任务。
    • 双击打开属性,选择目标数据库。
    • 限制：设置一个更高的阈值，仅当碎片超过 30% 时才执行”。
    • 排序结果：勾选此项,可以进一步优化索引。
    • 点击 确定。

  3. 连接任务：

    sql 数据库维护计划建立教程
    （图片来源网络，侵删）
    • 将鼠标光标放在 检查数据库完整性 任务的绿色箭头（成功）上，按住并拖动到 重组索引 任务的绿色箭头上。
    • 再从 重组索引 任务的绿色箭头拖动到 重建索引 任务的绿色箭头上。
    • 从 重建索引 的绿色箭头拖动到 备份数据库 的绿色箭头上。
任务 4：清理维护历史记录

这个任务非常重要，可以防止 msdb 数据库膨胀。

  1. 从工具箱拖出 清理历史记录 任务。

  2. 双击打开属性窗口。

  3. 要维护的子计划：选择 所有子计划。

  4. 要维护的日志：选择 所有日志。

  5. 历史记录要保留的单位：选择 周 或 天，并输入一个数字（保留 4 周的历史）。

  6. 点击 确定 保存。

  7. 连接任务：将 备份数据库 任务的绿色箭头连接到 清理历史记录 任务的绿色箭头。

步骤 5：设置计划

现在我们需要让这个计划自动运行。

  1. 在设计界面的下方，点击 计划 选项卡。
  2. 点击 新建... 按钮。
  3. 名称：给计划起个名字，如 Daily at 2 AM。
  4. 计划类型：选择 重复执行。
  5. 频率：
    • 每天：选择此项。
    • 每天重复执行时间：选择一个业务低峰期，比如凌晨 2:00。
  6. 点击 确定，然后再次点击 确定 保存维护计划的整体属性。

步骤 6：保存并启用计划

  1. 点击 SSMS 工具栏上的 保存 图标（或按 Ctrl + S）。
  2. SQL Server 代理服务应该正在运行，如果服务未运行，你需要手动启动它，否则计划无法执行，你可以在 对象资源管理器 -> SQL Server 代理 中检查其状态。

验证和维护

  1. 查看历史记录：

    • 展开 SQL Server 代理 -> 作业。
    • 你会看到一个与你维护计划同名的作业。
    • 右键单击它，选择 查看历史记录,可以检查每次执行是否成功。

  2. 测试运行：

    • 右键单击作业，选择 启动作业，可以立即手动执行一次,方便测试。

  3. 定期检查：

    • 定期检查备份文件是否成功生成并可以还原。
    • 监控数据库性能和空间使用情况，根据实际情况调整维护计划中的参数（如索引碎片阈值、备份保留策略等）。

高级提示与最佳实践

  • 使用 T-SQL（备选方案）：对于高级用户，可以使用 msdb.dbo.sp_add_maintenance_plan 等存储过程来以脚本方式创建维护计划,这更适合版本控制和自动化部署。
  • 分批处理：如果你的数据库非常大，可以考虑将索引维护任务分批在多个晚上进行,以减少对生产环境的冲击。
  • 监控警报：配置 SQL Server 代理作业失败时的警报,通过邮件或短信通知管理员。
  • 文档化：为你的每个维护计划创建文档，说明其目的、执行频率和负责人。

通过以上步骤，你就已经成功建立了一个强大且自动化的 SQL Server 数据库维护计划,这将是保障你数据库稳定运行的基石。