DMTman wrote:
Who can help me to create a backup strategy that will copy the
backend.mdb into separate folders representing either the time, or
just folders that are sequentially numbered.
I need this for rollback purposes.
What follows is an ugly little vscript that I run on the task manager
to backup. It zips up the current file and uploads to the server. I
think it keeps five versions. It won't backup if there is a lock
file(ldb). It also compacts the database and has rudimentary logging.
Do with it what you will. Enjoy!
'This is Dan's latest and greatest semi reusable access database backup
tool
'You need to declare the name of the log file, the name of the database
you
'want to back up, the path to the database you ant to backup, the path
to
'the local folder where the work wil take place. You will also need
the zip
'and unzip folders in the local folder. finally you will need the
workgroupfile.mdw
'in the local working folder. The zip and unzip can be found here
'http://www.info-zip.org/pub/infozip/
On Error Resume next
dim dbEngine
dim fso
dim wshShell
dim txtStr
dim strLog
dim strPathToBackup
dim strLocalWorkingDirectory
dim strDatabase
dim strCompactDatabase
dim I
strLog = "NameOfLog.txt"
strDatabase = "DataBaseToBeBackedUp.mdb"
strPathToBackup = "DirectoryWhere Database lives\"
strLocalWorkingDirectory = "DirectoryOnLocalHardDrive\"
set fso = wscript.createobject("scripting.filesystemobject")
set txtStr = fso.opentextfile(strLocalWorkingDirectory &
strLog,8,true,0)
txtStr.writeline(cstr(now))
if fso.fileexists(strPathToBackup & strDatabase) then
txtStr.writeline("Reached network and found file")
else
txtStr.writeline("Could not reach network ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
wscript.quit 0
end if
if fso.fileexists(strPathToBackup &
left(strDatabase,len(strDatabase)-3) & "ldb") then
txtStr.writeline("Lock file found ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
wscript.quit 0
end if
fso.copyfile strPathToBackup &
strDatabase,strLocalWorkingDirectory,true
If err.number = 0 then
txtStr.writeline(strDatabase & " copied to local directory")
else
txtStr.writeline("An error occuried copying " & strDatabase & " to
local directory ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
wscript.quit 0
end if
set wshShell = wscript.createobject("wscript.shell")
wshShell.run chr(34) & strLocalWorkingDirectory & "Zip\zip.exe" &
chr(34) & space(1) & "-j" & space(1) _
& chr(34) & strLocalWorkingDirectory &
left(strDatabase,len(strDatabase)-3) & "zip" & chr(34) & space(1) _
& chr(34) & strLocalWorkingDirectory & strDatabase &
chr(34),2,true
If err.number = 0 then
txtStr.writeline(strDatabase & " successfully zipped")
else
txtStr.writeline("An error occuried zipping " & strDatabase & "
ABORTING ")
txtStr.close
set txtStr = nothing
set fso = nothing
set wshShell = nothing
wscript.quit 0
end if
wshShell.run chr(34) & strLocalWorkingDirectory & "UnZIp\unzip.exe" &
chr(34) & space(1) & "-t" & space(1) _
& chr(34) & strLocalWorkingDirectory &
left(strDatabase,len(strDatabase)-3) & "zip" & chr(34),2,true
If err.number = 0 then
txtStr.writeline(strDatabase & " archive tested OK")
set wshShell = nothing
else
txtStr.writeline("Test of Archive FAILED " & strDatabase & " ABORTING
")
txtStr.close
set txtStr = nothing
set fso = nothing
set wshShell = nothing
wscript.quit 0
end if
for I = 5 to 2 step -1
if fso.fileexists(strPathToBackup & cstr(I - 1) &
left(strDatabase,len(strDatabase)-3) & "zip") then
fso.copyfile strPathToBackup & cstr(I - 1) &
left(strDatabase,len(strDatabase)-3) & "zip", _
strPathToBackup & cstr(I) &
left(strDatabase,len(strDatabase)-3) & "zip",true
end if
next
if fso.fileexists(strPathToBackup &
left(strDatabase,len(strDatabase)-3) & "zip") then
fso.copyfile strPathToBackup & left(strDatabase,len(strDatabase)-3) &
"zip",strPathToBackup & "2" & left(strDatabase,len(strDatabase)-3) &
"zip"
end if
if err.number = 0 then
txtStr.writeline("Backups rotated on server")
else
txtStr.writeline("Error rotating backups on server ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
wscript.quit 0
end if
fso.copyfile strLocalWorkingDirectory &
left(strDatabase,len(strDatabase)-3) & "zip",strPathToBackup,true
if err.number = 0 then
txtStr.writeline("Current archive successfully uploaded")
else
txtStr.writeline("Error uploading current archive ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
set wshShell = nothing
wscript.quit 0
end if
set dbEngine = wscript.createobject("dao.dbengine.36")
dbEngine.systemdb = strLocalWorkingDirectory & "workgroupfile.mdw"
dbEngine.defaultuser = "user"
dbEngine.defaultpassword = "password"
dbEngine.compactdatabase strLocalWorkingDirectory &
strDatabase,strLocalWorkingDirectory & "compact" & strDatabase
if err.number = 0 then
txtStr.writeline("Database compacted successfully")
set dbEngine = nothing
else
txtStr.writeline("Error compacting database ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
set wshShell = nothing
set dbEngine = nothing
wscript.quit 0
end if
fso.copyfile strLocalWorkingDirectory & "compact" &
strDatabase,strPathToBackup & strDatabase,true
if err.number = 0 then
txtStr.writeline("Compacted database uploaded to server")
else
txtStr.writeline("Error uploading database to the server ABORTING")
txtStr.close
set txtStr = nothing
set fso = nothing
set wshShell = nothing
wscript.quit 0
end if
if fso.fileexists(strLocalWorkingDirectory & strDatabase) then
fso.deletefile strLocalWorkingDirectory & strDatabase
end if
if fso.fileexists(strLocalWorkingDirectory & "compact" & strDatabase)
then
fso.deletefile strLocalWorkingDirectory & "compact" & strDatabase
end if
if fso.fileexists(strLocalWorkingDirectory &
left(strDatabase,len(strDatabase)-3) & "zip") then
fso.deletefile strLocalWorkingDirectory &
left(strDatabase,len(strDatabase)-3) & "zip"
end if
txtStr.writeline("Local TempFiles deleted. Uploading log file")
fso.copyfile strLocalWorkingDirectory & strLog,strPathToBackup,true
txtStr.writeline("Log FIle Uploaded")
txtStr.close
set txtStr = nothing
set fso = nothing