SQL Server 批量完?备份 实现方式三

使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）

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

-- =============================================
-- Create date: <2016.05.06>
-- Description: <扩展sp_MSforeachdb，增加@whereand参数>
-- =============================================
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 
create proc [dbo].[sp_MSforeachdb_Filter]
    @command1 nvarchar(2000), @replacecharnchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @whereandnvarchar(2000) = null,@precommandnvarchar(2000) = null, @postcommandnvarchar(2000) = null
as
    set deadlock_priority low
 
    /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */
 
    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @inaccessible nvarchar(12), @invalidloginnvarchar(12), @dbinaccessiblenvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
 
    if (@precommand is not null)
        exec(@precommand)
 
    declare @origdbnvarchar(128)
    select @origdb = db_name()
 
    /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
exec(N'declarehCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
            N' where (d.status& ' + @inaccessible + N' = 0)' +
            N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)
 
    declare @retvalint
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
 
    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)
 
   declare @tempdbnvarchar(258)
   SELECT @tempdb = replace(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')
 
    return @retval

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

（Figure1：添加内容1）

（Figure2：添加内容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如下所示：

--使用更新的存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）
USE [master]
GO

DECLARE @SQL NVARchar(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
BACKUP DATABASE [?] 
TO DISK = ''E:DBBackup?_' + CONVERT(char(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak'' 
WITH NOINIT, NOUNLOAD, NAME = N''?_backup'', NOSKIP, STATS = 10, NOFORMAT'

PRINT @SQL

--过滤数据库
EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
@whereand=" and [name] not in('tempdb','master','model','msdb') "

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

（Figure3：备份信息）

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

（Figure4：错误信息）

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

--显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的?本EXEC sp_helptext N'sp_MSforeachdb';