SQL Server 批量完整备份

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQLCodes）实现方式一（One）实现方式二（Two）实现方式三（Three）参

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

参考文献（References）

二.背景（Contexts）三.实现代码（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的目录下生成类似下图的备份文件：

（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所示：

（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在指定的数据库上执行；

-- ============================================= -- 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的区别有以下两点：

（Figure4：添加内容1）

（Figure5：添加内容2）