sqlserver2000中，通过ftp自动远程备份和恢复数据库的例子

最新推荐文章于 2021-02-22 11:22:53 发布

formiss

最新推荐文章于 2021-02-22 11:22:53 发布

阅读量2.4k

点赞数

文章标签：数据库 sqlserver ftp服务器 sql server 脚本 user

本文链接：https://blog.csdn.net/formiss/article/details/6255239

版权

要求：
具有管理员权限，一般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 + ' >>" ' & lt;; / span & gt; + @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上调试通过