本文詳細介紹了在SQL Server2005及以上版本中如何使用存儲過程進行數據庫備份的方法。

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-18 10:45:14
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--  Blog   :http://blog.csdn.net/htl258
--  Subject:數據庫批量備份存儲過程(判斷盤符、路徑，錯誤盤符返回，不存在的路徑自動創(chuàng)建)
--------------------------------------------------------------------------
--適用環(huán)境：SQL Server 2005及以上版本(主要用于作業(yè)中調用以及程序開發(fā)中調用)
USE master
GO
--1.周期性備份數據庫代碼(保留原來備份的):
--備份文件名為：原數據庫名稱+'_'+備份日期.bak
IFOBJECT_ID('sp_backupdatabase') ISNOTNULL
    DROPPROC sp_backupdatabase
GO
CREATEPROC sp_backupdatabase
@pathNVARCHAR(100)--路徑
AS
--路徑名格式標準化
IFRIGHT(@path,1)<>'/'SET@path=@path+'/'
--獲取文件夾信息
DECLARE@tTABLE(id INTIDENTITY,a INT,b INT,c INT)
DECLARE@fpathNVARCHAR(3)
SET@fpath=LEFT(@path,3)
INSERT@tEXEC master..xp_fileexist @fpath
INSERT@tEXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則返回錯誤提示：
IFEXISTS(SELECT1FROM@tWHERE id=1AND c=0)
BEGIN
    RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
    RETURN
END
--如果不存在指定的文件夾，則創(chuàng)建:
ELSEIFEXISTS(SELECT1FROM@tWHERE b=0AND id=2)
BEGIN
    DECLARE@mddirNVARCHAR(100)
    SET@mddir='md '+@path
    EXEC master..xp_cmdshell @mddir
END
--開始備份數據庫到指定的目錄
DECLARE@snvarchar(4000)
SELECT@s=ISNULL(@s+';','')
        +N'BACKUP database ['+name+'] TO DISK = '''
        +@path+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
FROM master..sysdatabases
WHERE name NOTIN('master','tempdb','model','msdb','pubs')--這里篩選不參加備份的數據庫
EXEC(@S)
GO
--調用方法：
EXEC sp_backupdatabase 'f:/Backup/tony'
/*
--返回信息：
已為數據庫 'mydb'，文件 'mydb' (位于文件 1 上)處理了 312 頁。
已為數據庫 'mydb'，文件 'mydb_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 313 頁，花費 0.733 秒(3.336 MB/秒)。
已為數據庫 'test'，文件 'test' (位于文件 1 上)處理了 208 頁。
已為數據庫 'test'，文件 'test_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 209 頁，花費 0.413 秒(3.951 MB/秒)。
--備份后的文件列表：
mydb_20100418.bak
test_20100418.bak
*/


--2.周期性備份數據庫代碼(自動刪除原備份文件):
--備份文件名為：原數據庫名稱.bak
IFOBJECT_ID('sp_backupdatabase') ISNOTNULL
    DROPPROC sp_backupdatabase
GO
CREATEPROC sp_backupdatabase
@pathNVARCHAR(100)--路徑
AS
--路徑名格式標準化
IFRIGHT(@path,1)<>'/'SET@path=@path+'/'
--獲取文件夾信息
DECLARE@tTABLE(id INTIDENTITY,a INT,b INT,c INT)
DECLARE@fpathNVARCHAR(3)
SET@fpath=LEFT(@path,3)
INSERT@tEXEC master..xp_fileexist @fpath
INSERT@tEXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則返回錯誤提示：
IFEXISTS(SELECT1FROM@tWHERE id=1AND c=0)
BEGIN
    RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
    RETURN
END
--如果不存在指定的文件夾，則創(chuàng)建:
ELSEIFEXISTS(SELECT1FROM@tWHERE b=0AND id=2)
BEGIN
    DECLARE@mddirNVARCHAR(100)
    SET@mddir='md '+@path
    EXEC master..xp_cmdshell @mddir
END
--開始備份數據庫到指定的目錄
DECLARE@snvarchar(4000)
SELECT@s=ISNULL(@s+';','')
        +N'BACKUP database ['+name+'] TO DISK = '''
        +@path+name+N'.bak''  WITH INIT'
FROM master..sysdatabases
WHERE name NOTIN('master','tempdb','model','msdb','pubs')--這里篩選不參加備份的數據庫
EXEC(@S)
GO
--調用方法：
EXEC sp_backupdatabase 'f:/Backup/tony2'
/*
--返回信息：
已為數據庫 'mydb'，文件 'mydb' (位于文件 1 上)處理了 312 頁。
已為數據庫 'mydb'，文件 'mydb_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 313 頁，花費 0.599 秒(4.082 MB/秒)。
已為數據庫 'test'，文件 'test' (位于文件 1 上)處理了 208 頁。
已為數據庫 'test'，文件 'test_log' (位于文件 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 209 頁，花費 0.351 秒(4.651 MB/秒)。
--備份后的文件列表：
mydb.bak
test.bak
*/