SQL Server 游标运用：批量备份数据库
2014-02-10 14:59 by 听风吹雨, 590 阅读, 8 评论, 收藏 , 编辑
一、 背景
在公司的内网有台数据库的测试服务器，这台服务器是提供给开发人员使用的，在上面有很多的数据库，有些是临时系统用到的数据库，这些数据库有一个共同点：数据库表结构比较重要，数据库只有一些测试数据，也就是说这些数据库都很小，而整台服务器的数据库又非常多；
现在有这样一个需求，希望间隔一段时间就备份所有数据库，所以这里写了这篇文章，这也是另外一篇文章 SQL Server 批量备份数据库（主分区） 的基础；
二、 实现过程
下面是实现批量备份数据库的3种方式，大家可以细细体会其中的差别：
1) 实现方式1：使用游标
2) 实现方式2：使用拼凑SQL的方式
3) 实现方式3：使用存储过程sp_MSforeachdb_Filter（以sp_MSforeachdb为基础）
(一) 实现方式1：使用游标
执行下面的SQL脚本就可以备份当前数据库实例的所有数据库（除了系统数据库）；
--
=============================================
--
Author: <听风吹雨>
--
Blog: <http://gaizai.cnblogs.com/>
--
Create date: <2011/12/03>
--
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: <http://gaizai.cnblogs.com/>
--
Create date: <2013.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
),
@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）
而且需要注意在创建存储过程的时候需要设置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的效果如下图所示：
（Figure6：错误信息）
如果没有设置SET QUOTED_IDENTIFIER 这个选项为 OFF ，那么在调用存储过程sp_MSforeachdb_Filter的时候会出现下图所示的错误信息：
（Figure7：错误信息）
如果想查看存储过程sp_MSforeachdb的详细代码，可以在通过访问路径：数据库-可编程性-存储过程-系统存储过程-sp_MSforeachdb找到，或者通过下面的脚本查看：
--
显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本
EXEC
sp_helptext N
'
sp_MSforeachdb
'
;
更多批量备份数据库的文章可以参考：
一、 参考文献
-------------------华丽分割线-------------------
