SQL Server 批量完整备份

zhaoke0727

一.本文所涉及的内容（Contents）

• 　　本文所涉及的内容（Contents）
  
• 　　背景（Contexts）
  
• 　　实现代码（SQL Codes）
  
  
  
  • 　　实现方式一（One）
    
  • 　　实现方式二（Two）
    
  • 　　实现方式三（Three）
    
  
  
• 　　参考文献（References）
  

二.背景（Contexts）
　　在公司的内网有台数据库的测试服务器，这台服务器是提供给开发人员使用的，在上面有很多的数据库，有些是临时系统用到的数据库，这些数据库有一个共同点：数据库表结构比较重要，数据库只有一些测试数据，也就是说这些数据库都很小，而整台服务器的数据库又非常多；
　　现在有这样一个需求，希望间隔一段时间就备份所有数据库，所以这里写了这篇文章，这也是另外一篇文章SQL Server 批量主分区备份（One Job）的基础；
三.实现代码（SQL Codes）
　　下面是实现批量备份数据库的3种方式，大家可以细细体会其中的差别：
　　1) 实现方式1：使用游标
　　2) 实现方式2：使用拼凑SQL的方式
　　3) 实现方式3：使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）
　　(一)实现方式1：使用游标
　　执行下面的SQL脚本就可以备份当前数据库实例的所有数据库（除了系统数据库）；
-- =============================================　　
-- Author:      
　　
-- Blog:        
　　
-- Create date:
　　
-- Description:
　　
-- =============================================
　　
DECLARE
　　@FileName VARCHAR(200),
　　@CurrentTime VARCHAR(50),
　　@DBName VARCHAR(100),
　　@SQL VARCHAR(1000)
　　
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
　　
DECLARE CurDBName CURSOR FOR
　　SELECT NAME FROM Master..SysDatabases where dbid>4
　　
OPEN CurDBName
　　
FETCH NEXT FROM CurDBName INTO @DBName
　　
WHILE @@FETCH_STATUS = 0
　　
BEGIN
　　--Execute Backup
　　SET @FileName = 'E:\DBBackup\' + @DBName + '_' + @CurrentTime
　　SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
　　''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
　　EXEC(@SQL)
　　--Get Next DataBase
　　FETCH NEXT FROM CurDBName INTO @DBName
　　
END
　　
CLOSE CurDBName
　　
DEALLOCATE CurDBName
　　执行完上面的SQL脚本，会在E:\DBBackup的目录下生成类似下图的备份文件：

http://images.cnitblog.com/blog/48305/201402/101459046268968.jpg

　　（Figure1：数据库备份文件）
　　(二)实现方式2：使用拼凑SQL的方式
--使用拼凑SQL的方式　　
DECLARE @SQL VARCHAR(MAX)
　　
SELECT @SQL = COALESCE(@SQL,'') + '
　　
BACKUP DATABASE '+ QUOTENAME(name,'[]')
　　
+ ' TO DISK = ''E:\DBBackup\'+ name + '_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak'
　　
+ ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
　　
FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0
　　
PRINT(@SQL)
　　
EXECUTE(@SQL)
　　生成的脚本如Figure2所示，如果想脚本更加美观，可以加上GO语句，如Figure3所示：

http://images.cnitblog.com/blog/48305/201402/101459054842099.jpg

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

http://images.cnitblog.com/blog/48305/201402/101459096854316.jpg

　　（Figure3：生成的T-SQL脚本）
　　(三)实现方式3：使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）
　　通过查看系统存储过程sp_MSforeachdb的T-SQL源代码可以发现是没有提供@whereand参数可以过滤数据库的，参考系统存储过程sp_MSforeachtable后，在sp_MSforeachdb的基础上创建带@whereand参数的存储过程sp_MSforeachdb_Filter，这样你就可以让SQL在指定的数据库上执行；
-- =============================================　　
-- Author:      
　　
-- Blog:        
　　
-- Create date:
　　
-- Description:
　　
-- =============================================
　　
USE [master]
　　
GO
　　
SET ANSI_NULLS ON
　　
GO
　　
SET QUOTED_IDENTIFIER OFF
　　
GO
　　
create proc [dbo].[sp_MSforeachdb_Filter]
　　@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
　　@whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(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), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(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 @origdb nvarchar(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'declare hCForEachDatabase 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 @retval int
　　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 @tempdb nvarchar(258)
　　SELECT @tempdb = REPLACE(@origdb, N']', N']]')
　　exec (N'use ' + N'[' + @tempdb + N']')
　　return @retval
　　上面的存储过程sp_MSforeachdb_Filter与sp_MSforeachdb的区别有以下两点：

http://images.cnitblog.com/blog/48305/201402/101459104936730.gif

　　（Figure4：添加内容1）

http://images.cnitblog.com/blog/48305/201402/101459115931931.gif

　　（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如下所示：
--使用更新的存储过程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的效果如下图所示：

http://images.cnitblog.com/blog/48305/201402/101459129097132.jpg

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

http://images.cnitblog.com/blog/48305/201402/101459143556377.gif

　　（Figure7：错误信息）
　　如果想查看存储过程sp_MSforeachdb的详细代码，可以在通过访问路径：数据库-可编程性-存储过程-系统存储过程-sp_MSforeachdb找到，或者通过下面的脚本查看：
--显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本　　
EXEC sp_helptext N'sp_MSforeachdb';
　　更多批量备份数据库的文章可以参考：
　　SQL Server 批量主分区备份（One Job）
　　SQL Server批量主分区备份（Multiple Jobs）
四.参考文献（References）
　　SET QUOTED_IDENTIFIER (Transact-SQL)（英文）
　　SET QUOTED_IDENTIFIER (Transact-SQL)（中文）