SQL Server 2016 数据库自动备份之维护计划
一、前言
通过使用 SQL Server 2016 数据库的维护计划，达到自动执行数据库备份的目的。在此之前，同样是自动备份数据库，用到的是 SQL Server 2016 数据库代理作业，可查看这篇文章《SQL Server 2016 数据库自动备份之代理作业》。维护计划与代理作业，相同点是，两者都需要开启 SQL Server 数据库的代理服务，通过 SQL Server 作业的方式来运行；不同的是，维护计划比代理作业操作方便，不需要写 T-SQL 脚本，维护计划是 SSIS 包，代理作业用 T-SQL 脚本，动动鼠标即可完成，功能更多，可清除历史记录和清除维护等，重要的是清理过期备份文件很方便。总之，维护计划与代理作业各有优缺点，适合实际情况的才是最好的。
二、维护计划
维护计划可创建所需的任务工作流，以确保优化数据库、定期进行备份并确保数据库一致。维护计划向导还可创建核心维护计划，但手动创建计划具有更大的灵活性。
三、维护计划的优点
在 SQL Server 2016 数据库引擎中，维护计划将创建由 Integration Services 代理作业运行的 SQL Server 包。可以按预订的时间间隔手动或自动运行维护计划。
SQL Server 2016 维护计划可提供以下功能：
- 使用各种典型维护任务创建工作流的功能。此外，还可以创建自己的 Transact-SQL 脚本。
- 概念性层次结构。使用每个计划可创建或编辑任务工作流。每个计划的任务可分组到子计划中，可以安排这些子计划在不同时间运行。
- 支持可以用于主服务器/目标服务器环境中的多服务器计划。
- 支持在远程服务器上记录计划历史记录。
- 支持 Windows 身份验证和 SQL Server 身份验证。请尽可能使用 Windows 身份验证。
四、维护计划功能
可以创建维护计划来执行以下任务：
- 用新填充因子重新生成索引来重新组织数据和索引页上的数据。 用新填充因子重新生成索引会确保数据库页中包含的数据量和可用空间的平均分布。 还使得以后能够更快地增长。
- 通过删除空数据库页压缩数据文件。
- 更新索引统计信息，确保查询优化器含有关于表中数据值分布的最新信息。这使得查询优化器能够更好地确定访问数据的最佳方法，因为可以获得数据库中存储数据的详细信息。虽然 SQL Server 会定期自动更新索引统计信息，但是此选项可以对统计信息立即进行强制更新。
- 对数据库内的数据和数据页执行内部一致性检查，确保系统或软件故障没有损坏数据。
- 备份数据库和事务日志文件。数据库和日志备份可以保留一段指定时间。这样，您就可以为备份创建一份历史记录，以便在需要将数据库还原到早于上一次数据库备份的时间的时候使用。还可以执行差异备份。
- 运行 SQL Server 代理作业。这可以用来创建可执行各种操作的作业以及运行这些作业的维护计划。
维护任务生成的结果可以作为报表写入文本文件，或写入 msdb 维护计划表（sysmaintplan_log 和 sysmaintplan_logdetail）。若要在日志文件查看器中查看结果，请右键单击“维护计划”，再单击“查看历史记录”。
五、数据库备份需求
1. 问题描述
现有一个数据库每天做一次完整备份，随着数据量增长，每次备份产生的文件变得越来越大，占用了过多磁盘空间，备份速度越来越慢，占用服务器内存和 CPU 资源越来越多。由于早期数据量较小，可满足日常需求，后期数据量增长，占用资源过多，早期备份计划已不能满足，需要重新规划。
2. 解决方案
针对数据量比较大的数据库，可使用完整备份加差异备份方式，进行数据库备份。
- 调整备份时间点。
根据网站访问日志，或程序执行日志，或其它记录，分析出用户操作最少的时间点。根据这个时间点，调整备份时间点，以降低对用户的影响，此时服务器空闲资源相对较多。 - 使用数据库备份压缩。
将备份文件变小，减少占用磁盘空间，同时可提高存储速度。关于数据库备份压缩，可查看这篇文章《SQL Server 数据库备份压缩》 - 使用完整备份加差异备份方式。
假设以一周为一个循环，周日进行一次数据库完整备份；周一至周六，分别进行一次数据库差异备份。提示：差异备份是针对完整备份的，它备份上一次完整备份后，发生变化的所有文件，单独的差异备份是不能还原数据库的。
六、使用维护计划向导
1、展开要创建您的管理计划的服务器。
2、展开“管理”文件夹。
3、右键单击“维护计划”文件夹，然后选择“维护计划向导”。
4、在 “SQL Server 维护计划向导” 页上，单击 “下一步”。
5、关于“选择计划属性”页：在“名称”框中，输入您创建的维护计划的名称。在“说明”框中，简要介绍您的维护计划。在“运行身份”列表中，选择“SQL Server 代理服务账户”。注意：两个单选里，选择“每项任务单独计划”，因为“完整备份”和“差异备份”执行不一样。然后点击“下一步”。
6、选择“备份数据库(完整)”和“备份数据库(差异)”两项任务。点击“下一步”。
7、任务顺序无需调整，因为这两项任务是分别执行，这与前面第 5 步骤选择“每项任务单独计划”有关，并且“上移”和“下移”按钮不可用。点击“下一步”即可。
8、定义“备份数据库(完整)”任务。默认选项卡“常规”下，点击“特定数据库”选择需要备份的数据库，选择备份组件为“数据库”，选择备份到“磁盘”。这时不要点击下一步，将选项卡切换到“目标”。
9、选项卡“目标”下，单选选择“为每个数据库创建备份文件”，并选择文件夹，填写备份文件扩展名为 bak。将选项卡切换到“选项”。
10、选项卡“选项”下，设置备份压缩为“压缩备份”，勾选“验证备份完整性”，点击计划“更改”按钮。
11、在“新建作业计划”中，填写名称，选择计划类型为“重复执行”，并勾选“启用”，设置频率为“每周”的“星期日”的凌晨 3 点执行，设置“开始日期”，并选择“无结束日期”，最终在摘要说明中会显示：在每个星期日的 3: 00:00 执行。将从 2019/1/18 开始使用计划。最后点击“确定”。
12、在“维护计划向导”中，点击“下一步”。
13、定义“备份数据库(差异)”任务。下面将重复上面设置操作，但有不同，请注意。默认选项卡“常规”下，点击“特定数据库”选择需要备份的数据库，选择备份组件为“数据库”，选择备份到“磁盘”。这时不要点击下一步，将选项卡切换到“目标”。
14、选项卡“目标”下，单选选择“为每个数据库创建备份文件”，并选择文件夹，填写备份文件扩展名为 bak。注意，为了与完整备份文件做区分，将差异备份文件设置到 Diff 目录。将选项卡切换到“选项”。
15、选项卡“选项”下，设置备份压缩为“压缩备份”，勾选“验证备份完整性”，点击计划“更改”按钮。
16、在“新建作业计划”中，填写名称，选择计划类型为“重复执行”，并勾选“启用”，设置频率为“每周”的周一至周六的凌晨 3 点执行，设置“开始日期”，并选择“无结束日期”，最终在摘要说明中会显示：在每个星期一, 星期二, 星期三, 星期四, 星期五, 星期六的 3: 00:00 执行。将从 2019/1/18 开始使用计划。最后点击“确定”。
17、在“维护计划向导”中，点击“下一步”。
18、在“选择报告选项”中，勾选“将报告写入文本文件”，并设置“文件夹位置”。也可以尝试“以电子邮件形式发送报告”，这里不再详述。点击“下一步”。
19、在“完成向导”中，详细说明了，即将执行的操作。确认无误后，点击“完成”。
20、使用“维护计划向导”创建维护计划完成。点击“关闭”。
21、在“维护计划”上鼠标右键“刷新”，可查看新建的维护计划。展开代理作业或刷新，可以看到新建了两个作业。这时，可不必等待维护计划自动执行，可以直接执行代理作业。作业执行成功后，可查看，是否创建了数据库备份文件。
22、需要注意的是，如果不是周日新建的维护计划，则要主动去执行一次数据库完整备份，也就是代理作业的第一个。如果没有完整备份文件，单独的差异备份文件，是不能还原数据库的。
23、修改维护计划，添加“清除历史记录”。在具体的维护计划上，鼠标右键选择“修改”。从左侧“工具箱”中选择“清除历史记录”任务，拖放到右侧，在“清除历史记录”任务上鼠标右键，选择“编辑”。根据实际需要编辑，这里我们保留最近 4 周的历史纪录。编辑完成点击确定。然后，点击“数据库备份(完整)”出现箭头，将箭头链接到“清除历史记录”。最后保存修改。
总结第 23 步骤的操作，添加“清除历史记录”，意思是，在每周进行数据库完整备份后，进行清除历史记录任务，只保留最近 4 周的历史记录。
24、修改维护计划，添加“清除维护”。从左侧“工具箱”中选择“清除维护”任务，拖放到右侧，在“清除维护”任务上鼠标右键，选择“编辑”。根据实际需要编辑，这里我们保留最近 4 周的备份文件。编辑完成点击确定。然后，点击“清除历史记录”出现箭头，将箭头链接到“清除维护”。最后保存修改。
总结第 24 步骤的操作，添加“清除维护”，意思是，在每周进行数据库完整备份后，进行清除历史记录任务，进行清除维护任务，只保留最近 4 周的历史记录，和最近 4 周的数据库完整备份文件。注意，这里只是添加了，删除完整备份文件，同理可再添加删除差异备份文件。修改维护计划一定要保存哦。
进行到这里，此次方案已经实施完毕。这里只做抛砖引玉，请根据实际需求，灵活调整。
七、问题解答
1、能正常新建维护计划，但执行维护计划失败。报错 sa 登陆失败等。
答：权限不够。请查看当前登陆身份权限。文中前面提到，支持 Windows 身份验证和 SQL Server 身份验证。请尽可能使用 Windows 身份验证。
八、参考文献
（完）