很早以前的一個項目里寫的，現在放上來備忘。
要求：
具有管理員權限，一般sa用戶就可以。
備份原理如下：
首先導出數據庫到本地硬盤上，然后壓縮成cab文件，之后用ftp.exe將壓縮包上傳到ftp服務器上
恢復原理如下：
首先在ftp服務器上下載壓縮包，然后解壓縮里面的文件，最后將數據庫備份恢復。
這是備份的源碼
?1?create?procedure?sp_BackupDB
?2???????@DBName?varchar(50),
?3???????@Host?varchar(255),
?4???????@User?varchar(255),
?5???????@Password?varchar(255),
?6?--??????@Port?int,
?7???????@WorkingFolder?varchar(255),
?8???????@FileName?varchar(255)
?9?as
10?begin
11???declare?@sTempFileName?varchar(255)
12???declare?@sCommandText?varchar(255)
13???declare?@sCabFileName?varchar(255)
14???declare?@sTempFolder?varchar(255)
15?
16???set?@sTempFolder?=?'~dbback'
17???--創建臨時文件夾
18???set?@sCommandText?=?'md?c:'?+?@sTempFolder
19???exec?xp_cmdshell?@sCommandText
20?
21???set?@sCabFileName?=?'c:'?+?@sTempFolder?+?''?+?@FileName
22???set?@sTempFileName?=?REPLACE(@sCabFileName,'.zpck','.dbak')
23?
24???--刪除可能存在的的臨時文件
25???set?@sCommandText?=?'del?"'?+?@sTempFileName?+'"'
26???exec?xp_cmdshell?@sCommandText
27?
28???set?@sCommandText?=?'del?"'?+?@sCabFileName?+'"'
29???exec?xp_cmdshell?@sCommandText
30?
31???--備份數據庫
32???BACKUP?DATABASE?@DBName??TO?DISK?=?@sTempFileName
33???--set?@sCommandText?=?'BACKUP?DATABASE?'?+?@DBName?+?'?TO?DISK?=?'''?+?@sTempFileName?+?''''
34???--execute(@sCommandText)
35???
36???--將數據庫壓縮成數據包
37???exec?xp_makecab?@sCabFileName,?'mszip',?1?,?@sTempFileName
38???
39???declare?@ftpLogScriptFileName?varchar(255)
40???set?@ftpLogScriptFileName?=?'"'?+?REPLACE(@sCabFileName,'.zpck','.log')?+?'"'
41???--產生ftp上傳腳本
42???set?@sCommandText?=?'echo?ftp>'?+?@ftpLogScriptFileName
43???exec?xp_cmdshell?@sCommandText
44???set?@sCommandText?=?'echo?open?'?+?@Host?+?'>>'?+?@ftpLogScriptFileName
45???exec?xp_cmdshell?@sCommandText
46???set?@sCommandText?=?'echo?'?+?@User?+?'>>'?+?@ftpLogScriptFileName
47???exec?xp_cmdshell?@sCommandText
48???set?@sCommandText?=?'echo?'?+?@Password?+?'>>'?+?@ftpLogScriptFileName
49???exec?xp_cmdshell?@sCommandText
50???set?@sCommandText?=?'echo?cd?'?+?@WorkingFolder?+?'>>'?+?@ftpLogScriptFileName
51???exec?xp_cmdshell?@sCommandText
52???set?@sCommandText?=?'echo?put?"'?+?@sCabFileName?+?'">>'?+?@ftpLogScriptFileName
53???exec?xp_cmdshell?@sCommandText
54???set?@sCommandText?=?'echo?bye?>>'?+?@ftpLogScriptFileName
55???exec?xp_cmdshell?@sCommandText
56?
57???--上傳數據包
58???set?@sCommandText?=?'ftp?-s:"'?+?@ftpLogScriptFileName?+?'"'
59???exec?xp_cmdshell?@sCommandText
60?
61???--刪除數據庫壓縮包
62???set?@sCommandText?=?'del?"'?+?@sCabFileName?+?'"'
63???exec?xp_cmdshell?@sCommandText
64?
65???--刪除ftp上傳腳本
66???set?@sCommandText?=?'del?"'?+?@ftpLogScriptFileName?+?'"'
67???exec?xp_cmdshell?@sCommandText
68?
69??
70???--刪除數據庫備份
71???set?@sCommandText?=?'del?"'?+?@sTempFileName?+?'"'
72???exec?xp_cmdshell?@sCommandText
73?
74???--刪除臨時文件夾
75???set?@sCommandText?=?'rd?c:'?+?@sTempFolder
76???exec?xp_cmdshell?@sCommandText
77?
78???return
79?end
這是恢復備份的源碼
create??procedure?sp_RestoreDB
??????@DBName?varchar(50),
??????@Host?varchar(255),
??????@User?varchar(255),
??????@Password?varchar(255),
??????@WorkingFolder?varchar(255),
??????@FileName?varchar(255),
??????@MDFPhyFileName?varchar(255),
??????@MDFLogFileName?varchar(255),
??????@LDFPhyFileName?varchar(255),
??????@LDFLogFileName?varchar(255),
??????@ReturnValue?int?output
as
begin
??set?@ReturnValue?=?-1
??declare?@sTempFileName?varchar(255)
??declare?@sCommandText?varchar(255)
??declare?@sCabFileName?varchar(255)
??declare?@sUnpackFolder?varchar(255)
??declare?@sTempFolder?varchar(255)
--??set?@MDFPhyFileName?=?'C:Program?FilesMicrosoft?SQL?ServerMSSQLDataSuStorageMgr.mdf'
--??set?@MDFLogFileName?=?'SuStorageMgr_Data'
--??set?@LDFPhyFileName?=?'C:Program?FilesMicrosoft?SQL?ServerMSSQLDataSuStorageMgr_log.ldf'
--??set?@LDFLogFileName?=?'SuStorageMgr_Log'
??set?@sTempFolder?=?'~dbback'
??set?@sCabFileName?=?'c:'?+?@sTempFolder?+?''?+?@FileName
??set?@sTempFileName?=?REPLACE(@sCabFileName,'.zpck','.dbak')
??set?@sCommandText?=?'md?'?+?'c:'?+?@sTempFolder
??exec?xp_cmdshell?@sCommandText
??--刪除可能存在的的臨時文件
??set?@sCommandText?=?'del?"'?+?@sTempFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??declare?@ftpLogScriptFileName?varchar(255)
??set?@ftpLogScriptFileName?=?REPLACE(@sCabFileName,'.zpck','.log')
??--產生ftp下載腳本
??set?@sCommandText?=?'echo?ftp>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?open?'?+?@Host?+?' >>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?'?+?@User?+?'>>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?'?+?@Password?+?'>>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?cd?'?+?@WorkingFolder?+?'>>"'?<;/span>+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?get?'?+?@FileName?+?'?'?+?@sCabFileName?+?'>>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??set?@sCommandText?=?'echo?bye >>"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??--下載數據包
??set?@sCommandText?=?'ftp?-s:'?+?@ftpLogScriptFileName
??exec?xp_cmdshell?@sCommandText
??--刪除ftp下載腳本
??set?@sCommandText?=?'del?"'?+?@ftpLogScriptFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??--壓縮包解壓縮
??set?@sUnpackFolder?=?'c:'?+?@sTempFolder
??exec?xp_unpackcab?@sCabFileName,?@sUnpackFolder,?1
??--刪除數據庫壓縮包
??set?@sCommandText?=?'del?"'?+?@sCabFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??--還原數據庫
??RESTORE?DATABASE??@DBName
???FROM?disk?=?@sTempFileName
???????WITH?RECOVERY,
???????REPLACE,
????MOVE?@MDFLogFileName?to?@MDFPhyFileName,
????MOVE?@LDFLogFileName?to?@LDFPhyFileName
??--刪除數據庫備份
??set?@sCommandText?=?'del?"'?+?@sTempFileName?+?'"'
??exec?xp_cmdshell?@sCommandText
??--刪除臨時目錄
??set?@sCommandText?=?'rd?'?+?'c:'?+?@sTempFolder
??exec?xp_cmdshell?@sCommandText
??set?@ReturnValue?=?1
??return
end
在windows 2000+sql server 2000上調試通過