自动化SQL Server之维护计划

自动化SQL Server之维护计划

数据库管理员所做的大部分工作是重复性的，包括备份数据库、重建索引、检查文件大小以及磁盘空间，对于像事务日志已满或磁盘空间不足的情况采取措施也是一些DBA日常工作的一部分。随着管理的服务器数量的增加，问题也迅速增多。自动化这些工作不仅可以提供便利，也是企业系统的需求。

SQL Server2008中的有两个功能可以提供自动化的管理：维护计划和SQL Server代理

维护计划可以自动化数据库的例行维护活动，如：备份、数据库完整性检查、索引维护等都可以随维护计划一起自动化。其中维护计划向导使你可以很容易地通过回答简单的问题来创建维护计划。

SQL Server代理可用于手动创建在SQL Server上运行的作业计划，进一步增强了DBA自动化的能力。

维护计划：

维护计划是在SQL Server中快速而轻松地自动化例行维护任务的方法。它们只是常规SQL Server代理作业之上的用户界面。

常见的SQL Server维护任务：

数据备份：这是最常用的

数据完整性检查：日志文件在一定程度是可以实现数据完整性的，但有些特殊情况，如突然掉电，磁盘等问题也有可能会造成写数据的不完整。所以需要对数据完整性做检查。“检查数据库完整性”任务检查指定数据库中所有对象的分配和结构完整性。此任务可以检查单个数据库或多个数据库，您还可以选择是否也检查数据库索引。“检查数据库完整性”任务封装 DBCC CHECKDB 语句。

历史数据清理：经常对数据库进行备份，作业，维护任务时，会产生一些历史数据。我们可以清除不需要的历史数据。

执行作业：可以SQL Server的代理作业

执行T-SQL脚本：执行一些T-SQL的脚本

注意：“清除历史记录”任务与“清除维护”任务的区别：

清除历史记录：语句从 msdb 表中删除历史记录信息

备份和还原历史记录：当您希望还原数据库时，保留有关最近备份创建时间的记录可帮助 SQL Server 创建恢复计划。保持期应当至少为完整数据库备份的频率。

SQL Server 代理作业历史记录：使用此历史记录有助于排除失败作业的故障，或者确定数据库操作发生的原因。

维护计划历史记录：使用此历史记录有助于排除失败的维护计划作业的故障，或者确定数据库操作发生的原因。

删除历史数据，如果其保留时间超过：指定要删除项的保留时间。

清除维护任务：使用“‘清除维护’任务”可以删除与维护计划相关的旧文件，包括由维护计划文件和数据库备份文件创建的文本报告。

备份文件：删除备份文件。

维护计划文本报告：删除以前运行的维护计划的文本报告。

删除特定文件：删除在“文件名”框中提供的特定文件。

文件名 ：要删除的文件的路径和名称。

搜索文件夹并根据扩展名删除文件：删除指定文件夹中带有指定扩展名的所有文件。使用此选项可一次删除多个文件，例如 Tuesday 文件夹中带有 .bak 扩展名的所有备份文件。

文件夹：要删除文件所在的文件夹的路径和名称。

文件扩展名：提供要删除的文件的文件扩展名。

包括一级子文件夹：从“文件夹”下的一级子文件夹中删除具有为“文件扩展名”指定的扩展名的文件。

在任务运行时根据文件保留时间删除文件：通过在“删除文件，如果其保留时间超过”框中提供数字和时间单位，指定将要删除的文件所要保留的最短时间。

删除文件，如果其保留时间超过：通过提供数字和时间单位（天、周、月或年），指定将要删除的文件所要保留的最短时间。保留时间长于指定时间长度的文件将被删除。

查看 T-SQL：根据所选选项，查看针对此任务的服务器执行的 Transact-SQL 语句。

从SQL Server 2005开始维护计划的底层使用的SQL Server Intergration Services服务，最终会生成一个SSIS包，然后通过作业来调用这个包。这一点咱们通过后面实验大家会看的很清楚。

为了实现上述的这些维护计划，在SQL Server2008中，可以通过有两种方法：

快速而简单的方法就是使用维护计划向导

手动方法是使用维护计划设计器：

演示：维护计划向导：

前提是:代理服务启用。

DEMO1：同时对所有的用户数据库进行备份  这个操作比较简单。

启用维护计划向导：

可以为每个数据库生成一个文件夹，里面是每个数据库自己的备份文件。还有一个报告文件，打开之后，内容如下图所示：

当配置成功时，作业下会自动多出一个作业。这个作业会去调用我们刚才新建的计划。

而且也可以不要为每个数据库都创建一个文件夹：

这个计划又是存放在什么地方的呢？我们来连接一下SSIS。

在SQL 代理的作业下，dbbak.subplan_1的步骤属性里可以看到，调用的是SSIS中的包：

DEMO2：检查数据库的完整性：

DEMO3: 可以同时设置多个维护任务

希望同时：检查数据库完整性，收缩数据库，备份数据库

可以分别设置三个维护任务，但也可以一个维护计划来完成多个任务。

维护计划设计器：手动创建维护计划，不使用向导

我们看一个例子，也是对数据库备份吧。

如果有多个任务，可以根据工作需要改变工作流。

随着SQL Server维护计划的运行，产生的文件会越来越行，就需要定期删除文件，那么咱们来看一下，我们现在希望产生的bak备份文件只能保存1个月，1个月以上的，全部删除。

那么就可以做一个维护计划：

维护任务生成的结果可以作为报表写入文本文件，或写入 msdb 中的 sysmaintplan_log 和 sysmaintplan_logdetail 维护计划表。若要在日志文件查看器中查看结果，请右键单击“维护计划”，再单击“查看历史记录”。

也可以通过查看历史记录来查看维护计划的执行过程。

特别注意：不要通过删除SSIS Package的方法来删除维护计划，否则会导致元数据残留在

Msdb.dbo.sysmaintplan_subplans系统表中。

在Development Studio中也可以设计维护计划：

完全基于 Intergration  Service，也很简单，创建出来之后上载到SSIS中

好处就是可以加入复杂任务。

演示：

启动 Visual Studio，创建项目：

然后一个SSIS包：可以为包重命名：

展开工具箱：

在这里可以看到有很多的工具。其中就包括咱们前面用到的一些维护任务。

Demo: 如何对所有的用户数据库进行收缩和备份

步骤1：

剩下的配置基本上是一样的。然后保存（生成）

然后到指定目录下就可以找到这个文件：

然后可以到SSIS中导入刚才创建的SSIS包。

一定要选择文件系统：

然后在作业里新建作业，调用此SSIS包，即可，如图所示：