SQL Server 批量完整备份

　　在公司的内网有台数据库的测试服务器，这台服务器是提供给开发人员使用的，在上面有很多的数据库，有些是临时系统用到的数据库，这些数据库有一个共同点：数据库表结构比较重要，数据库只有一些测试数据，也就是说这些数据库都很小，而整台服务器的数据库又非常多；

　　现在有这样一个需求，希望间隔一段时间就备份所有数据库，所以这里写了这篇文章，这也是另外一篇文章SQL Server 批量主分区备份（One Job）的基础；

下面是实现批量备份数据库的3种方式，大家可以细细体会其中的差别：

1) 实现方式1：使用游标

2) 实现方式2：使用拼凑SQL的方式

3) 实现方式3：使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）

(一)实现方式1：使用游标

执行下面的SQL脚本就可以备份当前数据库实例的所有数据库（除了系统数据库）；

执行完上面的SQL脚本，会在E:\DBBackup的目录下生成类似下图的备份文件：

（Figure1：数据库备份文件）

(二)实现方式2：使用拼凑SQL的方式

生成的脚本如Figure2所示，如果想脚本更加美观，可以加上GO语句，如Figure3所示：

（Figure2：生成的T-SQL脚本）

（Figure3：生成的T-SQL脚本）

(三)实现方式3：使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）

通过查看系统存储过程sp_MSforeachdb的T-SQL源代码可以发现是没有提供@whereand参数可以过滤数据库的，参考系统存储过程sp_MSforeachtable后，在sp_MSforeachdb的基础上创建带@whereand参数的存储过程sp_MSforeachdb_Filter，这样你就可以让SQL在指定的数据库上执行；

上面的存储过程sp_MSforeachdb_Filter与sp_MSforeachdb的区别有以下两点：

（Figure4：添加内容1）

（Figure5：添加内容2）

而且需要注意在创建存储过程的时候需要设置SET QUOTED_IDENTIFIER OFF，当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔；当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。具体可以参考：SET QUOTED_IDENTIFIER (Transact-SQL)

调用sp_MSforeachdb_Filter实现批量备份数据库的T-SQL如下所示：

执行上面的存储过程就可以备份所有数据库（系统数据库除外，想要过滤数据库可以填写@whereand参数的条件），执行上面SQL的效果如下图所示：

（Figure6：错误信息）

如果没有设置SET QUOTED_IDENTIFIER 这个选项为 OFF ，那么在调用存储过程sp_MSforeachdb_Filter的时候会出现下图所示的错误信息：

（Figure7：错误信息）

如果想查看存储过程sp_MSforeachdb的详细代码，可以在通过访问路径：数据库-可编程性-存储过程-系统存储过程-sp_MSforeachdb找到，或者通过下面的脚本查看：

更多批量备份数据库的文章可以参考：