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
'
;
更多批量備份數據庫的文章可以參考：
?
一、 參考文獻
-------------------華麗分割線-------------------
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
