远程备份还原SQL数据库
来源:互联网 发布:单片机 液晶屏与电机 编辑:程序博客网 时间:2024/05/19 13:58
很早以前的一个项目里写的,现在放上来备忘。
要求:
具有管理员权限,一般sa用户就可以。
备份原理如下:
首先导出数据库到本地硬盘上,然后压缩成cab文件,之后用ftp.exe将压缩包上传到ftp服务器上
恢复原理如下:
首先在ftp服务器上下载压缩包,然后解压缩里面的文件,最后将数据库备份恢复。
这是备份的源码
create procedure sp_BackupDB
@DBName varchar(50),--数据库名
@Host varchar(255),--主机名
@User varchar(255),--用户
@Password varchar(255),--密码
-- @Port int,
@WorkingFolder varchar(255), --文件夹
@FileName varchar(255)--文件名
as
begin
declare @sTempFileName varchar(255)
declare @sCommandText varchar(255)
declare @sCabFileName varchar(255)
declare @sTempFolder varchar(255)
set @sTempFolder = '~dbback'
--创建临时文件夹
set @sCommandText = 'md c:' + @sTempFolder
exec xp_cmdshell @sCommandText
set @sCabFileName = 'c:' + @sTempFolder + '' + @FileName
set @sTempFileName = REPLACE(@sCabFileName,'.zpck','.dbak')
--删除可能存在的的临时文件
set @sCommandText = 'del "' + @sTempFileName +'"'
exec xp_cmdshell @sCommandText
set @sCommandText = 'del "' + @sCabFileName +'"'
exec xp_cmdshell @sCommandText
--备份数据库
BACKUP DATABASE @DBName TO DISK = @sTempFileName
--set @sCommandText = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @sTempFileName + ''''
--execute(@sCommandText)
--将数据库压缩成数据包
exec xp_makecab @sCabFileName, 'mszip', 1 , @sTempFileName
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 + '>>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
set @sCommandText = 'echo put "' + @sCabFileName + '">>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
set @sCommandText = 'echo bye >>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
--上传数据包
set @sCommandText = 'ftp -s:"' + @ftpLogScriptFileName + '"'
exec xp_cmdshell @sCommandText
--删除数据库压缩包
set @sCommandText = 'del "' + @sCabFileName + '"'
exec xp_cmdshell @sCommandText
--删除ftp上传脚本
set @sCommandText = 'del "' + @ftpLogScriptFileName + '"'
exec xp_cmdshell @sCommandText
--删除数据库备份
set @sCommandText = 'del "' + @sTempFileName + '"'
exec xp_cmdshell @sCommandText
--删除临时文件夹
set @sCommandText = 'rd c:' + @sTempFolder
exec xp_cmdshell @sCommandText
return
end
要求:
具有管理员权限,一般sa用户就可以。
备份原理如下:
首先导出数据库到本地硬盘上,然后压缩成cab文件,之后用ftp.exe将压缩包上传到ftp服务器上
恢复原理如下:
首先在ftp服务器上下载压缩包,然后解压缩里面的文件,最后将数据库备份恢复。
这是备份的源码
create procedure sp_BackupDB
@DBName varchar(50),--数据库名
@Host varchar(255),--主机名
@User varchar(255),--用户
@Password varchar(255),--密码
-- @Port int,
@WorkingFolder varchar(255), --文件夹
@FileName varchar(255)--文件名
as
begin
declare @sTempFileName varchar(255)
declare @sCommandText varchar(255)
declare @sCabFileName varchar(255)
declare @sTempFolder varchar(255)
set @sTempFolder = '~dbback'
--创建临时文件夹
set @sCommandText = 'md c:' + @sTempFolder
exec xp_cmdshell @sCommandText
set @sCabFileName = 'c:' + @sTempFolder + '' + @FileName
set @sTempFileName = REPLACE(@sCabFileName,'.zpck','.dbak')
--删除可能存在的的临时文件
set @sCommandText = 'del "' + @sTempFileName +'"'
exec xp_cmdshell @sCommandText
set @sCommandText = 'del "' + @sCabFileName +'"'
exec xp_cmdshell @sCommandText
--备份数据库
BACKUP DATABASE @DBName TO DISK = @sTempFileName
--set @sCommandText = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @sTempFileName + ''''
--execute(@sCommandText)
--将数据库压缩成数据包
exec xp_makecab @sCabFileName, 'mszip', 1 , @sTempFileName
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 + '>>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
set @sCommandText = 'echo put "' + @sCabFileName + '">>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
set @sCommandText = 'echo bye >>' + @ftpLogScriptFileName
exec xp_cmdshell @sCommandText
--上传数据包
set @sCommandText = 'ftp -s:"' + @ftpLogScriptFileName + '"'
exec xp_cmdshell @sCommandText
--删除数据库压缩包
set @sCommandText = 'del "' + @sCabFileName + '"'
exec xp_cmdshell @sCommandText
--删除ftp上传脚本
set @sCommandText = 'del "' + @ftpLogScriptFileName + '"'
exec xp_cmdshell @sCommandText
--删除数据库备份
set @sCommandText = 'del "' + @sTempFileName + '"'
exec xp_cmdshell @sCommandText
--删除临时文件夹
set @sCommandText = 'rd c:' + @sTempFolder
exec xp_cmdshell @sCommandText
return
end
这是恢复备份的源码
createprocedure sp_RestoreDB
@DBNamevarchar(50),
@Hostvarchar(255),
@Uservarchar(255),
@Passwordvarchar(255),
@WorkingFoldervarchar(255),
@FileNamevarchar(255),
@MDFPhyFileNamevarchar(255),
@MDFLogFileNamevarchar(255),
@LDFPhyFileNamevarchar(255),
@LDFLogFileNamevarchar(255),
@ReturnValueint output
as
begin
set@ReturnValue=-1
declare@sTempFileNamevarchar(255)
declare@sCommandTextvarchar(255)
declare@sCabFileNamevarchar(255)
declare@sUnpackFoldervarchar(255)
declare@sTempFoldervarchar(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@ftpLogScriptFileNamevarchar(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
--还原数据库
RESTOREDATABASE@DBName
FROMdisk=@sTempFileName
WITH RECOVERY,
REPLACE,
MOVE @MDFLogFileNameto@MDFPhyFileName,
MOVE @LDFLogFileNameto@LDFPhyFileName
--删除数据库备份
set@sCommandText='del "'+@sTempFileName+'"'
exec xp_cmdshell@sCommandText
--删除临时目录
set@sCommandText='rd'+'c:'+@sTempFolder
exec xp_cmdshell@sCommandText
set@ReturnValue=1
return
end
@DBNamevarchar(50),
@Hostvarchar(255),
@Uservarchar(255),
@Passwordvarchar(255),
@WorkingFoldervarchar(255),
@FileNamevarchar(255),
@MDFPhyFileNamevarchar(255),
@MDFLogFileNamevarchar(255),
@LDFPhyFileNamevarchar(255),
@LDFLogFileNamevarchar(255),
@ReturnValueint output
as
begin
set@ReturnValue=-1
declare@sTempFileNamevarchar(255)
declare@sCommandTextvarchar(255)
declare@sCabFileNamevarchar(255)
declare@sUnpackFoldervarchar(255)
declare@sTempFoldervarchar(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@ftpLogScriptFileNamevarchar(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
--还原数据库
RESTOREDATABASE@DBName
FROMdisk=@sTempFileName
WITH RECOVERY,
REPLACE,
MOVE @MDFLogFileNameto@MDFPhyFileName,
MOVE @LDFLogFileNameto@LDFPhyFileName
--删除数据库备份
set@sCommandText='del "'+@sTempFileName+'"'
exec xp_cmdshell@sCommandText
--删除临时目录
set@sCommandText='rd'+'c:'+@sTempFolder
exec xp_cmdshell@sCommandText
set@ReturnValue=1
return
end
0 0
- 远程备份还原SQL数据库
- SQL Server 远程备份还原数据库
- 远程数据库备份还原
- sql 数据库备份还原
- sql 备份还原数据库
- sql 备份还原数据库
- SQL数据库备份还原
- C#远程数据库备份还原
- 数据库远程还原,本地备份。
- 备份和还原SQL数据库
- 备份和还原SQL数据库
- NET备份还原SQL数据库
- SQL数据库备份与还原
- Sql Server 备份/还原数据库
- VC+SQL数据库备份、还原
- SQL数据库备份与还原
- c# 备份还原sql数据库
- VC+SQL数据库备份、还原
- storyboard的问题
- Linux中查看CPU信息
- last day on windows programming
- python核心编程第九章(9-10)
- highcharts特殊定制:x轴文字竖直显示(兼容各浏览器)
- 远程备份还原SQL数据库
- RabbitMQ学习之集群部署
- UVA - 757 Gone Fishing 贪心+枚举
- web服务层不需要外键
- 程序员也文艺:海燕——高尔基
- 第九章 内联函数
- zoj 3430 Detect the Virus(AC自动机)
- Java Comparator (比较器接口 Comparator)
- 优酷真实视频地址解析