SQL Server 批量主分区备份（One Job）

　　在我的数据库实例中，有很多下图所示的数据库，这些数据库的名称是有规律的，每个数据库包含的表都是相同的，其中2个表是类似流水记录的表，表的数据量会比较大，占用的空间有几十G到上百G不等，这2个表相对于其它的配置表来说是比较不重要的。

　　现在有一个需求就是对数据库进行备份，允许丢失这两个表的数据，保留重要的配置表数据，你是否遇到过同样的问题呢？这个时候你会怎么做呢？你有什么方案呢？有什么方法可以快速备份这些数据库呢？

（Figure1：数据库列表）

阅读本文之前你可以先参考：SQL Server 批量完整备份

　　通过上面的描述，其中很重要的一点就是每个数据库中有2个大表，而且这些数据是不重要的，那么我们对这2个大表做表分区，把大数据放到其它文件组中，只留重要的配置表在主文件组（PRIMARY）中，接着就可以对主文件组进行备份，这样既满足了备份重要表数据，而且不会造成备份文件过大、占用磁盘空间、备份时间过长等问题。

　　确定了方向之后我们接着考虑作业的问题，通过作业备份类似Figure1的数据库，有下面两种方案可供选择：

（Figure2：作业列表）

（Figure3：作业列表）

　　Figure2是一个方案，这些作业是可以自动化创建，但是会用到批处理，代码会复杂一点，唯一的缺点就是当新创建了数据库，是无法自动备份的（不过可以通过专门创建一个监控数据库的新建、删除状态的Job来解决这个问题）详情请参考：SQL Server 批量主分区备份（Multiple Jobs）

　　Figure3就是我们这篇文章需要讲述的方案，这里把所有的数据库的备份都集中到一个作业中，这个方案有以下缺点：

　　　　1) 整个备份过程是串行的；

　　　　2) 如果没有异常处理，那么后面的数据库就没有办法备份了；

　　　　3) 在作业执行的时候对服务器压力比较大（没有分散执行时间）；

　　　　4) 做本身的msdb数据库中记录的作业日志也没有那么清晰，排错比较困难（只有整个作业的信息，msdb.dbo.sysjobhistory的message字段保存不了太多信息），需要自己创建表进行记录；

　　尽管有以上的缺点，但是也是有优点的：当新创建了一个类似的数据库（业务需要），这个时候作业也会备份这个数据，不用人工去创建作业；另外还有一个，就是当数据库多而小的时候，这个方案特别有用；下面就来讲讲这个Job实现的具体步骤。

实现步骤概要：

　　1. 批量创建文件夹；

　　2. 创建维护表：[JobLog]和[ErrorLog]；

　　3. 创建备份所有数据库的SQL脚本；

　　4. 创建Job执行上面的脚本；

(一) 为了方便管理备份文件，我们为每个数据库创建单独的文件夹，下面的SQL代码实现根据数据库批量创建数据库名对应的文件夹，使用了游标循环数据库名进行创建文件夹，执行cmd命令需要开启数据库的xp_cmdshell开关；

执行上面的脚本后，会在E:\DBBackup\目录下创建如下图所示的文件夹：

（Figure4：创建的文件夹）

(二) 对备份的维护，我希望可以了解到所有数据库的备份情况，所以下面创建2个维护表：[JobLog]和[ErrorLog]，这两个表用于记录作业的执行情况，通过这两个表，可以实现如Figure5、Figure6的效果；

(三) 下面的代码实现了主分区完整备份和主分区差异备份（主分区可参考：SQL Server 维护计划备份主分区），当是星期一的深夜的时候，我们做完整备份，如果是其它时候我们就做差异备份，具体是什么时候，这个就通过计划里面的时候来控制了（计划的执行时间为星期一、星期三、星期五，这就代表星期一深夜做了完整备份、星期三和星期五分别做了差异备份）。（备份实践可参考：SQL Server 2008 维护计划实现数据库备份）

　　下面是生成的备份文件命名的范例，这样的备份文件的命名可以方便维护，而且直观知道备份文件创建的时间，可以精确到秒，文件名重复的几率不大；

DBName _Primary_Full_2013_01_14_002007.bak

DBName_Primary_Diff_2013_01_16_002034.bak

　　下面是整个批量备份数据库的核心SQL脚本，如果你是创建维护计划，那可以把这个SQL放到“执行 T-SQL 语句”任务，如果是创建Job的，可以放到作业的步骤里；

　　这个备份脚本中使用了游标循环获取数据库名进行备份，在【--防止作业遗漏备份】标签的SQL语句是为了保证记录表[JobLog]每次执行都有新的记录，即使备份失败（如何查看后面会讲到）也可以观察到对应的记录；

　　脚本中加入了异常处理，可以有效的防止某个数据库备份失败后，后面数据库的备份不受影响，把异常信息插入到[ErrorLog]。

　　SQL代码里面强制了星期一进行主分区的完整备份，其它什么时候做差异备份，这个就完全由作业中计划来控制（如果你想，你可以通过作业中的计划来调整每天都进行差异备份）。

(四) 下面的代码实现了删除备份文件，从下面的代码实现删除14天之前的备份文件，这个可以作为第三步骤的下一个步骤，但是需要注意有相应的机制可以检测到备份失败的数据库，不然一段时间备份都失败了，会造成最后没有了备份文件（可以通过邮件查询[ErrorLog]进行预警，可以参考：SQL Server 创建数据库邮件）

(五) 查看作业的运行情况；

（Figure5：作业备份类型）

（Figure6：作业执行时间）

　　既然做了上面主文件组的备份，当然我们需要去测试这个主文件组的还原了，这样才可以当遇到问题可以快速还原备份文件，达到还原数据的目的；

　　接下来会在另外一篇文章里面专门讲解；