很早以前的一个项目里写的，现在放上来备忘。
要求：
具有管理员权限，一般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上调试通过