SQL Server 维护计划实现数据库备份（策略实战）

小fish

　　一、背景
　　之前写过一篇关于备份的文章：SQL Server 维护计划实现数据库备份，上面文章使用完整备份和差异备份基本上能解决数据库备份的问题，但是为了保障数据更加安全，我们需要再次完善我们的备份计划；
　　下面这篇文章主要加入了日志备份，并对设计备份的频率和设计命名规范等问题进行实战；
　　
　　二、最佳实践
　　(一) 备份计划
　　1) 每周星期日的2:00:00执行数据库的完整备份；
　　2) 每周星期一至星期六每天的2:00:00执行数据库的差异备份；
　　3) 每天在8:00:00和23:59:59之间、每1小时执行数据库的日志备份；
　　4) 每个月的最后一个星期日的1:00:00执行数据库的完整备份；
　　
　　(二) 计划讲解
　　1. 根据备份文件的不同，我创建了4个文件用于存放备份文件：（Medusa是数据库名）
　　1) Medusa_Full：用于存放完整备份文件；
　　2) Medusa_Diff：用于存放差异备份文件；
　　3) Medusa_Log：用于存放日志备份文件；
　　4) Medusa_Save：用于存放保留备份文件；
　　上面的4个文件夹与备份计划的4个步骤是一 一对应的；

　　（Figure1：文件夹命名规范）
　　
　　2. 创建名为Medusa的维护计划，并创建如下图的4个子计划：

　　（Figure2：子计划列表）
　　
　　3. 设置【清除历史记录】、【清除维护】的保留时间：
　　1) Subplan_Full：保留完整备份文件4周；（1个*4周=4个文件）
　　2) Subplan_Diff：保留差异备份文件2周；（6个*2周=12个文件）
　　3) Subplan_Log：保留日志备份文件1周；（16个*7天=112个文件）
　　4) Subplan_Save：保留备份文件1年；（1个*12月=12个文件）
　　上面的时间策略需要根据数据库的安全级别、磁盘空间、业务特性等情况共同解决的，你可以根据的实际情况进行修改和调整；
　　
　　4. 下面的Figure3、Figure4、Figure5、Figure6是具体设置的效果图：

　　（Figure3：子计划Full）

　　（Figure4：子计划Diff）

　　（Figure5：子计划Log）

　　（Figure6：子计划Save）
　　
　　5. 保存维护计划后，在SQL Server代理的作业中会生成Figure7的作业列表：

　　（Figure7：Job列表）
　　
　　6. 执行一段时间后，在各个文件夹中会生成一系列的文件：

　　（Figure8：Full文件）

　　（Figure9：Diff文件）

　　（Figure10：Log文件）

　　（Figure11：Save文件）
　　
　　7. 有了这些文件之后，我们可以虽然还原到某个时间点上的数据，数据的差异为一个小时，例如你是在2012-12-11的15:48:00分误操作了数据库，我能回滚到最接近这个时间的是：2012-12-11的15:00:00

　　（Figure12：还原完整备份文件）

　　（Figure13：还原差异、日志备份文件）
　　在使用SSMS的还原的时候，系统会帮你找到最近的备份文件，并且进行了勾选，要完成上面的还原，根据我们之前的备份计划，我们需要文件：
　　1) 完整备份文件：Barfoo.Medusa_backup_2012_12_09_020005_0612500.bak
　　2) 差异备份文件：Barfoo.Medusa_backup_2012_12_11_020005_8354977.bak
　　3) 日志备份文件：
　　Barfoo.Medusa_backup_2012_12_11_080005_4808937.trn
　　Barfoo.Medusa_backup_2012_12_11_090005_4816881.trn
　　Barfoo.Medusa_backup_2012_12_11_100005_6543553.trn
　　Barfoo.Medusa_backup_2012_12_11_110005_7645233.trn
　　Barfoo.Medusa_backup_2012_12_11_120005_8434417.trn
　　Barfoo.Medusa_backup_2012_12_11_130004_9379977.trn
　　Barfoo.Medusa_backup_2012_12_11_140005_2044137.trn
　　Barfoo.Medusa_backup_2012_12_11_150005_2208329.trn
　　还原的步骤就是先还原完整备份文件，接着还原差异备份文件，再依次执行日志备份文件，这样就能回滚到你想要的时间点：2012-12-11的15:00
　　
　　三、注意事项

• 备份计划中“每天在8:00:00和23:59:59之间、每1小时执行数据库的日志备份”很多人会问：这中间相差8个小时的数据不做日志备份呢？其实我是这样考虑的，在凌晨的时候，大部分的情况这段时间是在跑定时器处理数据，如果不是程序有BUG，一般不会造成数据错误需要恢复，而且这个时候用户操作页面也不频繁（个人认为更多数据误操作是因为人为操作不当引起的），你可以根据自己的业务情况做些调整；
  
• 备份计划中“每个月的最后一个星期日的1:00:00执行数据库的完整备份”，这样做的目的是尽量不影响全备份又能保留一份完整的备份文件，如果需要更加严谨一点，你可以把它换成“仅复制备份（COPY_ONLY）”，这需要在维护计划中使用T-SQL进行备份，这就可以完全不影响日志链；
  
• 维护计划的可以通过T-SQL脚本生成嘛？
  

　　sp_add_maintenance_plan
　　sp_delete_maintenance_plan_db
　　sp_add_maintenance_plan_db
　　sp_delete_maintenance_plan_job
　　sp_add_maintenance_plan_job
　　sp_help_maintenance_plan
　　sp_delete_maintenance_plan
　　
　　四、参考文献
　　SQL Server 备份和还原全攻略
　　SQL Server 维护计划实现数据库备份
　　数据库维护计划存储过程 (Transact-SQL)