oracle与sqlserver自动备份还原

1、实现功能：1、 同时支持oracle与sqlserver2、 定时自动备份并自动压缩备份文件3、 定时自动解压缩备份文件并还原主要文件组成：1、 一个VBS文件（主要执行文件）2、 一个TXT文件（配置文件oracle与sqlserver共用）3、 一个SQL文件（用于oracle的用户删除与重建）TXT文件格式如下：#所有路径配置中，注意最后不带符合#tablespace,用于还原时指定表空间os=win7evdir=C:oracleproduct10.2.0db_1BINsavedir=d:tempcachedir=d:temptmpdbtype=sqlserverip=

2、user=sapwd=sasysuser=syspwd=tablespace=tns=dbname=testdb其中oracle数据库用于备份时：evdir、savedir、dbtype、user、pwd、tns必须配置其中oracle数据库用于还原时：除备份所需配置外，还需要cachedir、sysuser、syspwd、tablespace其中sqlserver数据用于备份时：savedir、dbtype、ip、user、pwd、dbname必须配置其中sqlserver数据库用于还原时：除备份所需配置外，还需要cachedirVBS文件内容如下：其中用于备份时请调用BackupDataB

3、ase，用于还原时请调用RestoreDataBaseOption Explicit这里统一定义全局变量Dim os,dbtype,evdir,ip,user,pwd,tns,dbname,savedir,cachedir,sysuser,syspwd,tablespace这里开始执行Call RestoreDataBase()调用备份功能Sub BackupDataBase() On Error Resume Next Call ReadCfgFile()If UCase(dbtype)=UCase(oracle) ThenCall BackupOracle()Else Call Backu

4、pSqlServer()End IfEnd Sub调用还原功能Sub RestoreDataBase() On Error Resume Next Call ReadCfgFile()If UCase(dbtype)=UCase(oracle) ThenCall RestoreOracle()Else Call RestoreSqlServer()End IfEnd SubSub ReadCfgFile() Dim fs, ts, sFile,lTmp,SARR,strData,I On Error Resume Next Set fs = CreateObject(Scripting.Fil

5、eSystemObject) Set sFile= fs.GetFile(config.txt) If sFile.Size 0 Then Set ts = sFile.OpenAsTextStream(1, -2) lTmp = ts.read(sFile.Size) SARR = Split(lTmp, vbCrLf, -1) For I = 0 To UBound(SARR) - 1 If InStr(SARR(I),=)0 then strData=Split(SARR(I),=,-1) If UCase(Trim(strData(0)=UCase(os) Then os=Trim(s

6、trData(1) If UCase(Trim(strData(0)=UCase(evdir) Then evdir=Trim(strData(1) If UCase(Trim(strData(0)=UCase(dbtype) Then dbtype=Trim(strData(1) If UCase(Trim(strData(0)=UCase(ip) Then ip=Trim(strData(1) If UCase(Trim(strData(0)=UCase(user) Then user=Trim(strData(1) If UCase(Trim(strData(0)=UCase(pwd)

7、Then pwd=Trim(strData(1) If UCase(Trim(strData(0)=UCase(tns) Then tns=Trim(strData(1) If UCase(Trim(strData(0)=UCase(dbname) Then dbname=Trim(strData(1) If UCase(Trim(strData(0)=UCase(savedir) Then savedir=Trim(strData(1) If UCase(Trim(strData(0)=UCase(cachedir) Then cachedir=Trim(strData(1) If UCas

8、e(Trim(strData(0)=UCase(sysuser) Then sysuser=Trim(strData(1) If UCase(Trim(strData(0)=UCase(syspwd) Then syspwd=Trim(strData(1) If UCase(Trim(strData(0)=UCase(tablespace) Then tablespace=Trim(strData(1) End if Next End IfEnd Sub日期格式化函数Function Format(sdate) Dim y, m, d y = cstr(year(sdate) m = cstr

9、(month(sdate) d = cstr(day(sdate) If len(m) = 1 Then m = 0 & m If len(d) = 1 Then d = 0 & d Format = y & m & dEnd FunctionZip压缩文件Sub Zip(ByVal sSourceDir,ByVal sfile,ByVal sZipFile) Dim fso,f,objShell,objfileItem ,objTarget,objSource On Error Resume NextSet fso = CreateObject(Scripting.FileSystemObj

10、ect) Set f = fso.CreateTextFile(sZipFile) f.Write PK & Chr(5) & Chr(6) & String(18,0) f.Close sSourceDir=Replace(sSourceDir,/,) sZipFile=Replace(sZipFile,/,) Set objShell = CreateObject(Shell.Application) Set objSource= objShell.NameSpace(sSourceDir)Set objfileItem = objSource.ParseName(sfile)Set ob

11、jTarget = objShell.NameSpace(sZipFile)objTarget.CopyHere objfileItem,256 循环等待直到压缩完成Do WScript.Sleep 1000 Loop Until objTarget.Items.Count 0 fso.DeleteFile sSourceDir & & sfileSet fso=Nothing End Sub 解压缩zip文件Sub UnZip(ByVal sZipFile, ByVal sCacheDir) Dim fso,objShell,objSource,objFolderItem,objTarget

12、 On Error Resume Next Set fso = CreateObject(Scripting.FileSystemObject) If Not fso.FileExists(sZipFile) Then Exit Sub If fso.GetExtensionName(sZipFile) zip Then Exit Sub If fso.FolderExists(sCacheDir) Then Call fso.DeleteFolder(sCacheDir) End IfCall fso.CreateFolder(sCacheDir) Set fso=Nothing sCach

13、eDir=Replace(sCacheDir,/,) sZipFile=Replace(sZipFile,/,) Set objShell = CreateObject(Shell.Application) Set objSource = objShell.NameSpace(sZipFile) Set objFolderItem = objSource.Items() Set objTarget = objShell.NameSpace(sCacheDir) objTarget.CopyHere objFolderItem, 256循环等待直到解压缩完成Do WScript.Sleep 10

14、00 Loop Until objTarget.Items.Count 0End Sub备份oracle数据库Sub BackupOracle() Dim sdmpfile,slogfile,sCmd,szipfile,spath,fso,f,Wscript,WshShell,sfile On Error Resume Next If Trim(savedir) Then spath=Replace(savedir,/) & / & Format(date) sdmpfile=spath & / & user & .dmp sfile=user & .dmp slogfile=spath &

15、/ & user & .logszipfile=spath & / & user & Format(date) & .zipevdir=Replace(evdir,/) & / set fso = CreateObject(Scripting.FileSystemObject)If Not fso.FolderExists(spath) Then fso.CreateFolder spathEnd IfIf fso.FolderExists(spath) ThenSet WshShell = CreateObject(WScript.Shell)sCmd = evdir & exp.exe &

16、 user & / & pwd & & tns & FULL=N GRANTS=Y COMPRESS=Y ROWS=Y file= & sdmpfile & log= & slogfileWshShell.run sCmd,1,TrueWshShell.Quit Set WshShell = NothingCall Zip(spath,sfile,szipfile)End ifEnd IfEnd Sub备份sqlserver数据库Sub BackupSqlServer() Dim sdmpfile,sCmd,szipfile,spath,fso,f,Wscript,WshShell,sfile

17、 On Error Resume Next If Trim(savedir) Then spath=Replace(savedir,/) & / & Format(date) sdmpfile=spath & / & dbname & .ful sfile=dbname & .ful szipfile=spath & / & dbname & Format(date) & .zipevdir=Replace(evdir,/) & / set fso = CreateObject(Scripting.FileSystemObject)If Not fso.FolderExists(spath)

18、Then fso.CreateFolder spathEnd IfIf fso.FolderExists(spath) ThenSet WshShell = CreateObject(WScript.Shell)sCmd =sqlcmd.exe -S & ip & -U & user & -P & pwd & -d master -Q BACKUP DATABASE & dbname & to disk=& sdmpfile & WshShell.run sCmd,1,TrueWshShell.Quit Set WshShell = NothingCall Zip(spath,sfile,sz

19、ipfile)End ifEnd IfEnd Sub还原oracle数据库Sub RestoreOracle() Dim sdmpfile,slogfile,sCmd,szipfile,spath,fso,f,Wscript,WshShell,sfile On Error Resume Next If Trim(savedir) And Trim(cachedir) Then spath=Replace(savedir,/) & / & Format(date)sfile=user & .dmp szipfile=spath & / & user & Format(date) & .zipev

20、dir=Replace(evdir,/) & /cachedir=Replace(cachedir,/)sdmpfile=cachedir & / & user & .dmpslogfile=cachedir & / & user & .log Call UnZip(szipfile,cachedir) Set WshShell = CreateObject(WScript.Shell)sCmd = evdir & sqlplus.exe & sysuser & / & syspwd & as sysdba CreateUser.sql & user & & pwd & & tablespac

21、e WshShell.run sCmd,1,TruesCmd = evdir & imp.exe & user & / & pwd & & tns & IGNORE =Y ROWS=Y FROMUSER=& user _ & TOUSER=& user & file= & sdmpfile & log= & slogfileWshShell.run sCmd,1,TrueWshShell.Quit Set WshShell = NothingEnd IfEnd Sub还原sqlserver数据库Sub RestoreSqlServer() Dim sdmpfile,slogfile,sCmd,

22、szipfile,spath,fso,f,Wscript,WshShell,sfile On Error Resume Next If Trim(savedir) And Trim(cachedir) Then spath=Replace(savedir,/) & / & Format(date)sfile=dbname & .ful szipfile=spath & / & dbname & Format(date) & .zipevdir=Replace(evdir,/) & /cachedir=Replace(cachedir,/)sdmpfile=cachedir & / & dbname & .ful Call UnZip(szipfile,cachedir) Set WshShell = CreateObject(WScript.Shell)sCmd =sqlcmd.exe -S & ip & -U