*以下全文可在下列地址中完整查看

http://p.blog.csdn.net/images/p_blog_csdn_net/Rulition/EntryImages/20091017/數據庫備份腳本.txt.jpg

 關于 SQL2005 遠程備份

Rulition 10:19 2009-10-17

要實現 SQL 遠程備份,必須處理好本機遠程訪問的問題：

第一、遠程計算機與本機最好在同一網段內，如果跨了網段（或VLAN），需交換機路由支持，甚至修改本地路由表。

第二、為本機提供 SQL 服務的“用戶名”和“密碼”必須是[遠程計算機]的[合法用戶]，且能對[遠程計算機]的[備份文件夾]進行[完全操作]。
      一般情況下，在安裝 SQL 服務時使用的是系統帳戶（多半是 administrator），如果剛好是遠程計算機的合法用戶，則處理起來就簡單多了。
      否則，在進行遠程備份時必須使用 xp_cmdshell 調用 MS-DOS 的 net use 命令，使用對方的[合法用戶]進行登記，以操作[備份文件夾]。
     
第三、至于[遠程計算機]的[備份文件夾]，可以事先在[遠程計算機]上將[備份文件夾]進行共享，并指定可完全訪問的用戶名。
      如果不想讓無關的人看到這個[備份文件夾]，可在共享時將共享名后加上$，例如：備份文件夾$
      或者，在使用 net use 進行[合法用戶]登記時，直接使用對方超級用戶，這樣可直接訪問[備份文件夾]。
      例如：//COMPUTER/E$/備份文件夾

下面是我寫的數據庫備份腳本，僅用于 SQL2005 環境。

[主要功能]
在無法利用[差異備份]+[完整備份]不過多影響業務數據庫性能的前提下，提高[完整備份]的頻率，將[完整備份]進行多處異地存放，
如有必要可進行壓縮以減小體積，同時刪除過期備份，最后對整個操作的過程進行記錄，生成日志文件。

[注意事項]
1、數據庫備份前的[一致性檢查]需要使用 SQL2000 的 isql.exe ，數據壓縮時使用 7-Zip ,這兩個文件必須事先指定路徑，詳見腳本內的說明。
2、異地備份的命名格式為：備份文件夾/This_Backup/數據庫名+_BackUp_+日期+時間.bak 。/This_Backup/ 可在 {組合 @備份路徑名稱} 小節處修改。
3、腳本中沒有對異地備份的路徑進行校驗，自己修改時請先用 net use 進行測試。

[簡單參考]
SQL 2005 下開啟 xp_cmdshell的辦法    [EXEC sp_configure 'xp_cmdshell', 0 ----關閉]
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

net use 的使用
“開始菜單”->“運行”-> 輸入 cmd 進入 MS-DOS 窗口，輸入以下命令并回車：（“用戶名”和“密碼”之間有一空格，注意大小寫！）

net use //COMPUTER /user:用戶名 密碼

這個操作可以在本機以 /user: 后指定的身份訪問 //COMPUTER [遠程計算機]
*如果指定的身份是[遠程計算機]上的超級用戶，則可以通過 //COMPUTER/C$ 直接訪問對方的 C: 盤
**這個異名登記注冊的過程可在本機開機后執行一次，并長期有效，直到重啟為止。（在 MS-DOS 和 SQL 中運行的效果是一樣。）

[生成日志]
在控制臺配置 SQL 計劃任務時，在“步驟”“高級”選項，選擇并指定“輸出文件”，方式為“追加”。可記錄整個操作過程。

[定時運行]
下面的腳本定義了8個異地備份路徑，所以，可每3小時運行機制一次，在控制臺配置 SQL 計劃任務時，運行頻率為“每3小時”，
開始時間在 '00:00:00' 和 '03:00:00' 之間的任意一個時間點，只要不與其他任務相重合就行了。開始日期為當天。

/*
SQL2005 環境下的數據庫備份腳本

[說明]
業務數據庫采用的是“簡單”模式，無法實現“日志差異”備份。
但是，如果合理地安排和利用現有的磁盤資源，還是可以通過提高備份頻率來彌補的。

[注意]
1、因為 SQL2005 安全的原因，需要開啟 xp_cmdshell 調用外部命令來處理備份文件。
2、數據庫備份前的[一致性檢查]需要使用 SQL2000 的 isql.exe ，請注意修改相應的[登錄用戶名]和[密碼]。還有 [isql.exe 的路徑]
   ************ SQL2005 不再提供 isql.exe , 所以，isql.exe 可以與【壓縮程序】放在一起 ************
3、所有“★”標記處要根據實際情況修改
4、所有 PRint '  ' 僅參與格式輸出，以方便日志查看，沒有實際意義。

[安排]
根據備份操作時對數據庫的影響（每3小時運行一次）和備份速度，以及存放時間，具體安排如下：

時間h  磁盤  空間 保留時間 備注
-----------------------------------------------------------------------------------------------------------------
0-3 //10.20.17.249/D$ 100G   7 天 100M銅纜
3-6 //202.202.202.26/Z$ 327G   10 天 *每月1號和15號存放 00:00 時的備份(30天后壓縮)(手工處理)
6-9 //202.202.202.22/D$ 104G   6 天
9-12 //202.202.202.23/D$ 100G   6 天
12-15 //202.202.202.32/D$ 20G   1 天
15-18 //202.202.202.33/D$ 134G   9 天
18-21 //202.202.202.10/D$ 50G   2 天
21-24 //202.202.202.36/Z$ 20G   2 天

[SQL參考]
BACKUP DATABASE [master] TO  DISK = N'd:/master.bak' WITH  RETAINDAYS = 6, NOFORMAT, NOINIT,  NAME = N'master-備份test', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

SQL 2005 下開啟 xp_cmdshell的辦法[EXEC sp_configure 'xp_cmdshell', 0 ----關閉]
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

*/
CREATE Proc usp_job_backup_this4
(@TEST int = 0)   ----如果 @test=1 ，則所有 EXEC 都不執行，改為 Print 輸出.
AS
----
SET NOCOUNT ON
----參數設置
Declare @備份路徑名稱 VarChar(99)
Declare @保留時間 VarChar(3)

Declare @數據庫名 VarChar(20)
 Select @數據庫名='SQLSERVER' -----★如有必要，請注意大小寫！★

----檢查參數
IF not exists (Select 1 from master.dbo.sysdatabases where name =@數據庫名)
 Begin
 Select '錯誤提示：'='當前系統數據庫中沒有找到用戶指定的數據庫: '+@數據庫名+ ' ,備份無法進行 ,在此結束任務！'
 Print '             '
 Print '             '
 Return
 End

----根據前面說明中的要求，配置 ★@備份路徑名稱★
 ----設置 @CHECKDB = 0 (并不是每次都有條件執行 DBCC CHECKD )
  Declare @CHECKDB int
   Select @CHECKDB = 0
 ----
IF Convert(VarChar,GetDate(),24) between '00:00:00' and '03:00:00'
 Begin
 Select @備份路徑名稱='//10.20.17.249/D$' , @保留時間='7'
 ----每月1號和15號修改 @備份路徑名稱 和 @保留時間 （999表示無期限或手工處理備份）
 IF DATEPART(dd,GetDate()) = 1  Select @備份路徑名稱='//202.202.202.26/Z$' , @保留時間='999'
 IF DATEPART(dd,GetDate()) = 15 Select @備份路徑名稱='//202.202.202.26/Z$' , @保留時間='999'
 ----★每月的備份啟用 DBCC CHECKD★
 Select @CHECKDB = 1
 End
 ----
IF Convert(VarChar,GetDate(),24) between '03:00:00' and '06:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.26/Z$' , @保留時間='10'
 ----★在服務器負荷較少的時候運行 DBCC CHECKD★
 Select @CHECKDB = 1
 End
IF Convert(VarChar,GetDate(),24) between '06:00:00' and '09:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.22/D$' , @保留時間='5'
 End
IF Convert(VarChar,GetDate(),24) between '09:00:00' and '12:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.23/D$' , @保留時間='5'
 End
IF Convert(VarChar,GetDate(),24) between '12:00:00' and '15:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.32/D$' , @保留時間='1'
 End
IF Convert(VarChar,GetDate(),24) between '15:00:00' and '18:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.33/D$' , @保留時間='7'
 End
IF Convert(VarChar,GetDate(),24) between '18:00:00' and '21:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.10/D$' , @保留時間='2'
 End
IF Convert(VarChar,GetDate(),24) between '21:00:00' and '24:00:00'
 Begin
 Select @備份路徑名稱='//202.202.202.36/Z$' , @保留時間='2'
 End

/*
----登記遠程用戶名和密碼，這個操作在 MS-DOS 中為每個遠程地址運行一次就行了，不用每次執行。
net use //COMPUTER /user:用戶名 密碼

----刪除所有登記信息
net use * /delete
*/

----組合 @備份路徑名稱
Select @備份路徑名稱= @備份路徑名稱 + '/This_Backup/' + ltrim(rtrim(@數據庫名))
Select @備份路徑名稱= ltrim(rtrim(@備份路徑名稱))

----建立 @備份路徑名稱 文件夾
Declare @RUN VarChar(1000)
 Select @RUN='EXECUTE xp_create_subdir N''' + @備份路徑名稱 + ''''
     IF @TEST = 1 Print @RUN ELSE EXEC(@RUN)

-----組合 @備份文件名稱
Declare @備份文件名稱 VarChar(60)
 Select @備份文件名稱=Convert(char(8),GetDate(),112) + replace(convert(char(6),GetDate(),108),':','')
 Select @備份文件名稱= @數據庫名 + '_BackUp_' + @備份文件名稱 + '.bak'
 Select @備份文件名稱=ltrim(rtrim(Convert(VarChar,@備份文件名稱)))

----組合 備份語句
 Select @RUN=''
 Select @RUN= 'BACKUP DATABASE '+ @數據庫名 + ' TO DISK = N''' + @備份路徑名稱+'/'+@備份文件名稱 +
  ''' WITH  RETAINDAYS = ' + @保留時間 + ' , NOFORMAT, NOINIT, NAME = N''' + @備份文件名稱 +
  ''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
----顯示備份任務
Print @RUN

----打開 xp_cmdshell [ 說明: 當 value_in_use = 1 時可以使用 xp_cmdshell ]
IF (Select value_in_use from  sys.configurations where name = 'xp_cmdshell') <> 1
 Begin
 EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
 End

----清理過期的備份,以保證有足夠的磁盤空間可用。
 Begin
 ----為游標的輸出值準備變量
 Declare @DOS_Command VarChar(200) , @backup_set_id int
 ----定義游標
 Declare my_cursor cursor
 for
 ----按 backup_start_date 清理過期的備份
 Select top 7 'pushd ' + @備份路徑名稱 + ' & dir ' +Replace(name,'.bak','.*')+ ' & del /q ' +Replace(name,'.bak','.*') , backup_set_id
    from msdb.dbo.backupset
   where database_name= @數據庫名  
     and backup_start_date <   dateadd(dd,Convert(int,@保留時間-1)*-1,Convert(varchar,GetDate(),23)) ---- 過了 00:00:00 就算是過期了
     and description = @備份路徑名稱 ---- 相同路徑下
   order by backup_start_date desc
 ----按 expiration_date 清理過期的備份還沒有寫出來 :-)
 ----打開游標
 open my_cursor
 ----提第一行數據,并賦值.S
 fetch next from my_cursor into @DOS_Command , @backup_set_id
 ----檢查游標集是否還有數據(0表示已成功找到數據)
 while @@fetch_status = 0
      Begin
      /*在這里處理找到的數據*/     
  ----刪除備份     
  Select @DOS_Command ,@backup_set_id , expiration_date from msdb.dbo.backupset where backup_set_id = @backup_set_id
  IF @TEST = 0 exec xp_cmdshell @DOS_Command
      /*為循環內部的變量再次賦值*/
      fetch next from my_cursor into @DOS_Command , @backup_set_id
      end
 ----關閉游標
 close my_cursor
 ----刪除游標
 deallocate my_cursor
 ----
 END

/*
在數據庫備份之前，應該進行數據的一致性檢查：  
1。運行檢查點進程 checkpoint  [強制將當前數據庫的所有臟頁寫到磁盤上。]
2。檢查數據庫     dbcc checkdb  
3。檢查頁面   dbcc checkalloc  
4。檢查系統表   dbcc checkcatalog  
然后再進行數據庫備份。

【DBCC CHECKDB】 是大量占用 CPU 和磁盤的操作。每一個需要檢查的數據頁都必須首先從磁盤讀入內存。另外，DBCC CHECKDB 使用 tempdb 排序。
建議在服務器負荷較少的時候運行 DBCC CHECKDB。如果在負荷高峰期運行 DBCC CHECKDB，那么事務吞吐量性能和 DBCC CHECKDB 完成時間性能都會受到影響。

【DBCC CHECKALLOC】 對數據庫中的分配和頁使用（包括索引視圖）情況進行檢查。
只用于向后兼容性的 NOINDEX 選項也適用于索引視圖。
如果已經執行 DBCC CHECKDB，則不必執行 DBCC CHECKALLOC。
DBCC CHECKDB 是 DBCC CHECKALLOC 的超集，除了對索引結構和數據完整性進行檢查之外，它還包括對分配進行檢查。

*/
----★一致性檢查[只保留檢查的最終結果到日志文件，注意修改 isql.exe 使用的(用戶名)和(密碼)還有 isql.exe 所在的路徑★
Declare @CHECK VarChar(1000)
Print '1.運行檢查點進程            ' + Convert(varchar,GetDate(),21)
 CHECKPOINT
Print '2.檢查數據庫                  ' + Convert(varchar,GetDate(),21)
     IF @CHECKDB = 1
 Begin
  Select @CHECK='C:/Progra~1/7-Zip/isql.exe -S '+@@SERVERNAME+' -d '+@數據庫名+' -U sa -P ★密碼★ R -Q "dbcc checkdb ('+@數據庫名+') " -o "%temp%/check.log" -w 9999 & find "CHECKALLOC" "%temp%/check.log" '
 IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK
 End
Print '3.檢查頁面             ' + Convert(varchar,GetDate(),21)
     IF @CHECKDB = 0
 Begin
  Select @CHECK='C:/Progra~1/7-Zip/isql.exe -S '+@@SERVERNAME+' -d '+@數據庫名+' -U sa -P ★密碼★ -Q "dbcc checkalloc ('+@數據庫名+') " -o "%temp%/check.log" -w 9999 & find "CHECKALLOC" "%temp%/check.log" '
 IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK
 End
Print '4.檢查系統表                  ' + Convert(varchar,GetDate(),21)
 Select @CHECK='C:/Progra~1/7-Zip/isql.exe -S '+@@SERVERNAME+' -d '+@數據庫名+' -U sa -P ★密碼★ -Q "dbcc checkcatalog ('+@數據庫名+')" '
 IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK

----執行備份操作
Print '5.執行備份操作                  ' + Convert(varchar,GetDate(),21)
IF @TEST = 0 EXEC (@RUN)

----獲取當前備份的 @backupSetId，并更新備份日志,將 @備份路徑名稱 記錄到 description
Declare @backupSetId as int
 Select @backupSetId = backup_set_id from msdb..backupset where database_name=@數據庫名
        and backup_set_id=(Select max(backup_set_id) from msdb..backupset where database_name=@數據庫名 )
     IF @backupSetId is null Begin select '驗證失敗。找不到數據庫“'+@數據庫名+'”的備份信息。' end
 Update msdb.dbo.backupset set description = @備份路徑名稱  where backup_set_id = @backupSetId

----當備份文件小于 500M 時壓縮數據庫（太大了就沒有壓縮的必要了，太費時間了?。?BR>Declare @backup_size VarChar(20)
 Select @backup_size = Convert(VarChar,backup_size/(1024*1000))   ----將 backup_size 換成兆
   from msdb.dbo.backupset where backup_set_id = @backupSetId
 Select @backup_size = LEFT(@backup_size, PATINDEX ('%.%',@backup_size)-1 ) ----提取小數點間的數值，計算單位為兆（M）
     IF Convert(int,@backup_size) < 500      ----★這個大小可以自己定★
 Begin
 Select '數據庫大小為 '+ Convert(VarChar,@backup_size) +' M , 啟用【壓縮程序】壓縮數據庫備份文件: '+ Convert(varchar,GetDate(),21)
 Declare @壓縮程序 VarChar(50)      ----★必須包含絕對路徑★
  Select @壓縮程序='C:/Progra~1/7-Zip/7z.exe'
 ----進入備份文件所在文件夾以后，將備份文件壓縮成 .zip 文件，如果存在同名文件，則直接覆蓋。最后刪除原文件。
 /* 這下面的是語法說明，詳見相應的HELP文檔資料。
 -mx9 x=[0 | 1 | 3 | 5 | 7 | 9 ] Sets level of compression. (Default valus = 5)
 -aoa Overwrite All existing files without prompt.
 -aos Skip extracting of existing files.
 -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt).
 -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt).
 
 */
 Declare @DOS_RUN VarChar(1000)
  Select @DOS_RUN = 'PUSHD "' + @備份路徑名稱 + '" & "' + @壓縮程序 + '" a ' + Replace(@備份文件名稱,'.bak','') + '.zip ' + @備份文件名稱 + ' -mx9 -aot & del "'+ @備份文件名稱 +'" & POPD '
   Print @DOS_RUN
   IF @TEST = 0 EXEC xp_cmdshell @DOS_RUN
 End

----如有必要，可以在完成操作之后關閉 xp_cmdshell
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;

----記錄所有工作的完成時間
declare @RunTime int
declare @DW VarChar(6)
 Select @RunTime = datedIFf(mi,backup_start_date,backup_finish_date) from msdb.dbo.backupset where backup_set_id=@backupSetId
 Select @DW = ' 分鐘'
    IF  @RunTime <= 0
 Begin
  Select @RunTime = datedIFf(ss,backup_start_date,GetDate()) from msdb.dbo.backupset where backup_set_id=@backupSetId
  Select @DW = ' 秒鐘'
 End
 Select '以上所有工作花費了 ' + Convert(VarChar,@RunTime) + @DW +' ,現在時間是: '+ Convert(VarChar,GetDate(),21) + ' [ Select * from msdb.dbo.backupset where backup_set_id = ' + Convert(VarChar,@backupSetId) + ' ] '
  Print '                  '
  Print '                  '
  Print '                  '

/*
 ----清理日志表記錄
 Select * from msdb.dbo.backupset
 Select * from msdb.dbo.restorehistory
 ----
 刪除 backupset 和 restorehistory 記錄表中所有早于指定日期的備份集的條目。
 由于執行備份或還原操作后會在備份和還原歷史記錄表中添加一些行，
 因此使用 sp_delete_backuphistory 可以減小 msdb 數據庫中歷史記錄表的大小。
 ----
 Declare @dt datetime
  Select @dt = cast(N'01/01/2009 00:00:00' as datetime)
    exec msdb.dbo.sp_delete_backuphistory @dt

 ----刪除作業的歷史記錄
 EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='01/01/2009 00:00:00'
 EXEC msdb..sp_maintplan_delete_log null,null,'01/01/2009 00:00:00'

*/