sql server维护计划配置

一：目标任务：

　　使用SQL Server 自带的维护计划自动备份数据库，并且自动删除超过设定期限的历史备份文件：

      

 二：前提条件

             1.   已经安装SQL Server Integration Services 服务

             2.  已经安装SQL Server 代理 (MSSQLSERVER) 服务 

           

              如果未安装SQL Server Integration Services 服务，依然可以创建维护计划，但是不会生效

 

三：通过维护计划来备份与删除

该部分转自：罗哥的笔记~ http://note.youdao.com/noteshare?id=4b82ac53315db460002209d32b6d726e&sub=43ADFD951B2A43AA9CF0F992A88229E6

数据库备份是数据库管理的一项重要工作，是数据安全保障的一部分，所以数据库备份是数据库日常管理的一项必需任务，那么怎么做这项任务呢？在做这个项任务之前，需要先了数据库备份的一此基础知识：

1.数据库恢复模式，包括完整、简单、大容量日志等；

2.数据库备份类型，包括完整、日志、差异等；

 

了解了它们之间的特点，才能更好的制定备份还原方案，好的备份还原方案能最大化的减少灾难或失误带来的损失，因此备份还原方案也是很重要的。

 

常用的备份还原方案有：

1. .完整备份，备份方案比较单一，只能还原到备份时的状态
2. .完整备份 + 差异备份，只能还原到备份时的状态，但是相比完整备份，可以节省磁盘空间
3. .完整备份 + 日志备份，节省磁盘空间，通过日志备份链还原到中完整备份与最后日志备份之间的任意时间点，当日志备份比较多，还原过程可能比较长。
4. .完整备份 + 差异备份 + 日志备份，该方案是上面2、3方案的折中方案，可以加速还原，但是比较2、3方案，磁盘空间消耗多点，但是相比单一的完整备份要好很多。

 

从以上方案来看，完整备份是必需要做的，差异备份和日志备份是可以取舍的

 

通过什么方式来备份数据库呢？

1.手动备份

2.维护计划

3.作业

 

方式1每次都要手动操作，太过繁琐，日常维护不太适用；

方式2、3是自动化操作，相对智能，方式2有图形界面，相对比较简单；方式3虽然也有图形界面，但是需要写代码或调用ssis包，相对比较复杂。

 

那就以简单的方式2为例，看看如何备份数据库吧

1.创建维护计划

 

 

 

2.添加备份任务

 

双击“备份数据库”任务

 

 

 

 

3.创建清除历史文件任务

 

 

 

4.创建”清除历史记录“任务，即清除msdb库中备份记录

 

 

5.备份计划规则

 

 

 

6.维护计划的执行用户

 

 

该用户一定要具体相应的权限

 

至此，一个简单的完整备份计划任务创建完成，以同样的方法创建差异备份、日志备份等备份计划

 

 

我们来看看维护计划的原理

1.备份数据库

实际上它也是调用T-SQL语句去执行的

 

2.清除维护”务

它是调用了存储过程xp_delete_file

那么看看xp_delete_file里面是怎么删除的

发现居然是直接使用动态库来实现，删除文件是操作系统级的，毕竟sqlserver擅长的，本身也并不能直接删除文件，只能通过外部调用来实现，间接的通过动态库也实现不失一种很好办法。

有关于Xp_delete_file信息有限，在msdn中不存在任何文档，并且不存在非Microsoft参考文档中的任何文档。

Xp_delete_file有五个参数：

1. 备份文件的文件类型 = 0或报告文件的文件类型 = 1。
2. Folder Path =要删除文件的文件夹。路径必须以反斜杠“\”结尾。
3. 文件扩展名 =这可能是'BAK'或'TRN'或您通常使用的任何内容。
4. Date =需要删除哪些文件的截止日期。
5. Subfolder = 0忽略子文件夹，1删除子文件夹中的文件（目录递归）。

 

只有拥有sysadmin权限才能运行xp_delete_file

 

 

 

 

 

3.清除历史记录

它是调用两个存储过程msdb.dbo.sp_purge_jobhistory、msdb..sp_maintplan_delete_log

看看这两个存储是什么鬼

它主要是删除msdb库中的一些历史记录而已

这里测试需要注意的是sp_helptext这个存储过程有点特殊，调用时是原本是sp_helptext '存储过程名'

如果存储过程名无特殊符号，直接使用sp_helptext 存储过程名 也是可以的，但是如果遇到特殊符号就不行了，比如例中

所以很多时候，在使用sp_helptext存储过程时，尽量带在存储过程名两端带上单引号还有一个问题需要注意

两行语句都是正常语句，而且单独执行都没有问题，但是一同执行就出现了上面的问题

但是中间加上go后执行就没问题了，其中原由甚是不明

 

维护计划的原理大概弄明白了，那么通过作业来实现备份与删除的功能就并不难了，稍有一点T-SQL功底的都可以实现，用作业自己写代码会比较灵活一点，维护计划相当于已经定好了模板，只能按模板来执行。