SQL Server 游標(biāo)運(yùn)用：批量備份數(shù)據(jù)庫(kù)
2014-02-10 14:59 by 聽風(fēng)吹雨,? 590 ?閱讀,? 8 ?評(píng)論,? 收藏 ,? 編輯
一、 背景
在公司的內(nèi)網(wǎng)有臺(tái)數(shù)據(jù)庫(kù)的測(cè)試服務(wù)器，這臺(tái)服務(wù)器是提供給開發(fā)人員使用的，在上面有很多的數(shù)據(jù)庫(kù)，有些是臨時(shí)系統(tǒng)用到的數(shù)據(jù)庫(kù)，這些數(shù)據(jù)庫(kù)有一個(gè)共同點(diǎn)：數(shù)據(jù)庫(kù)表結(jié)構(gòu)比較重要，數(shù)據(jù)庫(kù)只有一些測(cè)試數(shù)據(jù)，也就是說這些數(shù)據(jù)庫(kù)都很小，而整臺(tái)服務(wù)器的數(shù)據(jù)庫(kù)又非常多；
現(xiàn)在有這樣一個(gè)需求，希望間隔一段時(shí)間就備份所有數(shù)據(jù)庫(kù)，所以這里寫了這篇文章，這也是另外一篇文章 SQL Server 批量備份數(shù)據(jù)庫(kù)（主分區(qū)） 的基礎(chǔ)；
?
二、 實(shí)現(xiàn)過程
下面是實(shí)現(xiàn)批量備份數(shù)據(jù)庫(kù)的3種方式，大家可以細(xì)細(xì)體會(huì)其中的差別：
1) 實(shí)現(xiàn)方式1：使用游標(biāo)
2) 實(shí)現(xiàn)方式2：使用拼湊SQL的方式
3) 實(shí)現(xiàn)方式3：使用存儲(chǔ)過程sp_MSforeachdb_Filter（以sp_MSforeachdb為基礎(chǔ)）
?
(一)? 實(shí)現(xiàn)方式1：使用游標(biāo)
執(zhí)行下面的SQL腳本就可以備份當(dāng)前數(shù)據(jù)庫(kù)實(shí)例的所有數(shù)據(jù)庫(kù)（除了系統(tǒng)數(shù)據(jù)庫(kù)）；
--
=============================================
--
Author: <聽風(fēng)吹雨>
--
Blog: <http://gaizai.cnblogs.com/>
--
Create date: <2011/12/03>
--
Description: <批量備份數(shù)據(jù)庫(kù)>
--
=============================================
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
執(zhí)行完上面的SQL腳本，會(huì)在E:\DBBackup的目錄下生成類似下圖的備份文件：
（Figure1：數(shù)據(jù)庫(kù)備份文件）
?
(二)? 實(shí)現(xiàn)方式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語(yǔ)句，如Figure3所示：
（Figure2：生成的T-SQL腳本）
（Figure3：生成的T-SQL腳本）
?
(三)? 實(shí)現(xiàn)方式3：使用存儲(chǔ)過程sp_MSforeachdb_Filter（以sp_MSforeachdb為基礎(chǔ)）
通過查看系統(tǒng)存儲(chǔ)過程sp_MSforeachdb的T-SQL源代碼可以發(fā)現(xiàn)是沒有提供@whereand參數(shù)可以過濾數(shù)據(jù)庫(kù)的，參考系統(tǒng)存儲(chǔ)過程sp_MSforeachtable后，在sp_MSforeachdb的基礎(chǔ)上創(chuàng)建帶@whereand參數(shù)的存儲(chǔ)過程sp_MSforeachdb_Filter，這樣你就可以讓SQL在指定的數(shù)據(jù)庫(kù)上執(zhí)行；
--
=============================================
--
Author: <聽風(fēng)吹雨>
--
Blog: <http://gaizai.cnblogs.com/>
--
Create date: <2013.05.06>
--
Description: <擴(kuò)展sp_MSforeachdb，增加@whereand參數(shù)>
--
=============================================
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
上面的存儲(chǔ)過程sp_MSforeachdb_Filter與sp_MSforeachdb的區(qū)別有以下兩點(diǎn)：
（Figure4：添加內(nèi)容1）
（Figure5：添加內(nèi)容2）
而且需要注意在創(chuàng)建存儲(chǔ)過程的時(shí)候需要設(shè)置SET QUOTED_IDENTIFIER OFF，當(dāng) SET QUOTED_IDENTIFIER 為 ON 時(shí)，標(biāo)識(shí)符可以由雙引號(hào)分隔，而文字必須由單引號(hào)分隔；當(dāng) SET QUOTED_IDENTIFIER 為 OFF 時(shí)，標(biāo)識(shí)符不可加引號(hào)，且必須符合所有 Transact-SQL 標(biāo)識(shí)符規(guī)則。具體可以參考： SET QUOTED_IDENTIFIER (Transact-SQL)
調(diào)用sp_MSforeachdb_Filter實(shí)現(xiàn)批量備份數(shù)據(jù)庫(kù)的T-SQL如下所示：
--
使用更新的存儲(chǔ)過程sp_MSforeachdb_Filter（以sp_MSforeachdb為基礎(chǔ)）
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
--
過濾數(shù)據(jù)庫(kù)
EXEC
[
sp_MSforeachdb_Filter
]
@command1
=
@SQL
,
@whereand
=
"
and
[
name
]
not
in
(
'
tempdb
'
,
'
master
'
,
'
model
'
,
'
msdb
'
) "
執(zhí)行上面的存儲(chǔ)過程就可以備份所有數(shù)據(jù)庫(kù)（系統(tǒng)數(shù)據(jù)庫(kù)除外，想要過濾數(shù)據(jù)庫(kù)可以填寫@whereand參數(shù)的條件），執(zhí)行上面SQL的效果如下圖所示：
（Figure6：錯(cuò)誤信息）
如果沒有設(shè)置SET QUOTED_IDENTIFIER 這個(gè)選項(xiàng)為 OFF ，那么在調(diào)用存儲(chǔ)過程sp_MSforeachdb_Filter的時(shí)候會(huì)出現(xiàn)下圖所示的錯(cuò)誤信息：
（Figure7：錯(cuò)誤信息）
如果想查看存儲(chǔ)過程sp_MSforeachdb的詳細(xì)代碼，可以在通過訪問路徑：數(shù)據(jù)庫(kù)-可編程性-存儲(chǔ)過程-系統(tǒng)存儲(chǔ)過程-sp_MSforeachdb找到，或者通過下面的腳本查看：
--
顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本
EXEC
sp_helptext N
'
sp_MSforeachdb
'
;
更多批量備份數(shù)據(jù)庫(kù)的文章可以參考：
SQL Server 批量備份數(shù)據(jù)庫(kù)（主分區(qū)）
SQL Server批量創(chuàng)建作業(yè)（備份主分區(qū)）
?
一、 參考文獻(xiàn)
-------------------華麗分割線-------------------
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
