The following goes to support my previous article MS Access – Automatic Backup of an Access Database, specifically the section regarding using a VBScript in conjunction with Windows Scheduled Task. I had a very basic VBScript, VBScript – Backup a File and add a Date Time Stamp, which illustrated how to add a Date/Time stamp to a file when you copy it and this made a great starting point for a backup script.
Several people have mentioned that they need more of a helping hand to transform that basic script into a more complete backup solution.
So I decided to try and help everyone out some more and below is a pretty comprehensive backup script that you can use.
The Code
'---------------------------------------------------------------------------------------
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Backup an Access Database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-02-22 Initial Release
'---------------------------------------------------------------------------------------
Dim sSourceFolder
Dim sBackupFolder
Dim sDBFile
Dim sDBFileExt
'Set our required variables
sSourceFolder = "C:\Users\Daniel\Desktop"
sBackupFolder = "C:\Users\Daniel\Desktop\DBBackup"
sDBFile = "WebBrowser_Image_Demo"
sDBFileExt = "accdb"
'Run the backup procedure
Call BackupDb(sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, True, True, True)
'---------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------
' **************************************************************************************
' **************************************************************************************
' You shouldn't need to edit anything below this point normally, so be careful!
' **************************************************************************************
' **************************************************************************************
'---------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------
'---------------------------------------------------------------------------------------
' Procedure : BackupDb
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Database Backup Routine
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSourceFolder : Folder in which the database resides -> "C:\Temp\Databases"
' sBackupFolder : Folder where the backups should be saved -> "C:\Temp\Databases\Backups"
' sDBFile : Filename of the database to backup -> "TestDatabase"
' sDBFileExt : Extension of the database to backup -> "accdb"
' bForceCopy : Should the backup be performed if a lock file is present -> True/False
' bRunSilent : Should Messages be displayed when the procedure is run -> True/False
' bCompact : Should the backup datbase be automatically compacted -> True/False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-02-22 Initial Release
'---------------------------------------------------------------------------------------
Function BackupDb(sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, bForceCopy, bRunSilent, bCompact)
Dim oFSO
dim sDBLockFileExt
Select Case sDBFileExt
case "mdb", "mde"
sDBLockFileExt = "ldb"
case "accdb", "accde", "accdr"
sDBLockFileExt = "laccdb"
End select
Set oFSO = CreateObject("Scripting.FileSystemObject")
'Validate that the file actually exists
If oFSO.FileExists(sSourceFolder & "\" & sDBFile & "." & sDBFileExt) = False Then
If bRunSilent=False Then
Msgbox "The specified database file '" & sSourceFolder & "\" & sDBFile & "." & sDBFileExt & "' cannot be loacted.", _
vbCritical or vbOKOnly, "Operation Aborted"
End If
Else
If bForceCopy = True Then
'Backup the file
Call CopyFile(oFSO, sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, bCompact)
Else
'Check for the presence of a lock file
If oFSO.FileExists(sSourceFolder & "\" & sDBFile & "." & sDBLockFileExt) = True Then
Msgbox "There is currently a lock file present. Cannot perform the backup at this time", _
vbCritical or vbOKOnly, "Operation Aborted"
Else
'Backup the file
Call CopyFile(oFSO, sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, bCompact)
End If
End If
End if
'Cleanup
Set oFSO = Nothing
End Function
'---------------------------------------------------------------------------------------
' Procedure : CopyFile
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Backup the file while appending a date/time stamp to the filename
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oFSO : File System Object
' sSourceFolder : Folder in which the database resides -> "C:\Temp\Databases"
' sBackupFolder : Folder where the backups should be saved -> "C:\Temp\Databases\Backups"
' sDBFile : Filename of the database to backup -> "TestDatabase"
' sDBFileExt : Extension of the database to backup -> "accdb"
' bCompact : Should the backup datbase be automatically compacted -> True/False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-02-22 Initial Release
'---------------------------------------------------------------------------------------
Function CopyFile(oFSO, sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, bCompact)
Dim sDateTimeStamp
Const OVER_WRITE_FILES = True
'Build the date/time stamp to append to the filename
sDateTimeStamp = cStr(Year(now())) & _
Pad(cStr(Month(now())),2) & _
Pad(cStr(Day(now())),2) & _
Pad(cStr(Hour(now())),2) & _
Pad(cStr(Minute(now())),2) & _
Pad(cStr(Second(now())),2)
'If the backup folder doesn't exist, create it.
If Not oFSO.FolderExists(sBackupFolder) Then
oFSO.CreateFolder(sBackupFolder)
End If
'Copy the file as long as the file can be found
oFSO.CopyFile sSourceFolder & "\" & sDBFile & "." & sDBFileExt,_
sBackupFolder & "\" & sDBFile & "_" & sDateTimeStamp & "." & sDBFileExt,_
OVER_WRITE_FILES
If bCompact = True Then
Call CompactDB(sBackupFolder & "\" & sDBFile & "_" & sDateTimeStamp & "." & sDBFileExt)
End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : CompactDB
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Compact the specified database file
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Database file (path & full filename with extension) to compact
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-02-22 Initial Release
'---------------------------------------------------------------------------------------
Function CompactDB(sFile)
Dim oWshShell
Dim sAppEXE
Dim sRegKey
Dim sAccessPath
sAppEXE = "MSACCESS.EXE"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\" & sAppEXE & "\Path"
Set oWshShell = CreateObject("WScript.Shell")
sAccessPath = oWshShell.RegRead(sRegKey)
oWshShell.Run chr(34) & sAccessPath & sAppEXE & chr(34) & " " & _
chr(34) & sFile & chr(34) & " /compact", 0, True
Set oWshShell = Nothing
End Function
'Source: http://saltwetbytes.wordpress.com/2012/10/16/vbscript-adding-datetime-stamp-to-log-file-name/
Function Pad(CStr2Pad, ReqStrLen)
Dim Num2Pad
Pad = CStr2Pad
If len(CStr2Pad) < ReqStrLen Then
Num2Pad = String((ReqStrlen - Len(CStr2Pad)), "0")
Pad = Num2Pad & CStr2Pad
End If
End Function
Instructions
- Copy/Paste the code into a new blank text file
- give it any name you'd like
- change the 4 input variable so they are configured for your setup (path, filename, ...)
- Modify the Call Backup(...) to apply the settings you desire (force copy, silent, compact)
- Save
- Change the text file's extension from txt to vbs
- run the VBScript by double-cliking on it
Obviously, normally you would now go and create a scheduled task to run on a desired frequency and assign your vbscript to be run.
I hope that helps some of you out there.
Daniel –
It’s obvious that the code doesn’t specify variable data types, so everything is a variant. What is the reason for that choice?
Because VBScript doesn’t have data types like VBA, and other languages, does/do.
https://msdn.microsoft.com/en-us/library/9e7a57cf(v=vs.85).aspx
Daniel,
Nicely done. Thank you for sharing.
Additionally I changed to variables settinga section to:
sSourceFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, “”)
sBackupFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, “”) & “\DBBackup”
sDBFile = Left(WScript.ScriptName, InStrRev(WScript.ScriptName,”.”) – 1)
and now only by copy you script file to database folder and changing the name to database name works without editing.
Many thanks,
Pawel
Pawel,
Yes, I use similar code in my own database launcher. However, in this instance I thought it would be helpful for other to make it more versatile the way I coded it, but you comment now offers them the ability to make it simple to backup a database in the same folder with only 1 variable to update. Thank you for sharing.
Daniel, thank you ever so for you post.Much thanks again.
Hello, I have the script on my desktop and do not receive any errors when I click it….doesn’t seem to be backing up though. Not sure where to troubleshoot if no errors….any ideas? I have it saved on desktop, database closed, renamed variables to the following:
sSourceFolder = “O:\RETAIL INVENTORY\Inventory Scheduling\Inventory Scheduling and Reporting Database”
sBackupFolder = “O:\RETAIL INVENTORY\Inventory Scheduling\Inventory Scheduling and Reporting Database\Backups”
sDBFile = “Inventory Scheduling and Reporting Database_backup”
sDBFileExt = “accdb”
I’m not seeing anything glaringly wrong with your changes.
I’d double check your path, filename, but I’m assuming you’ve done so.
Then I’d get into adding some msgbox within the code to see exactly what is going on through the code to figure out where things are going awry.
I will double-check everything. Thanks!
Aw, very cool. It is now working. I had accidentally included “_backup” at the end of the regular database name. Thanks for this!
Glad you have everything sorted out and working now. You had me worrying about my code and testing on my end, but I couldn’t replicate any issues. I’m glad it turned out to be just a typo.
I have followed all comments and am really impressed but if I may ask is there any script to restored after the backup
Thank looking forward
No. A restore would not be something I would normally automate. It would require a GUI to allow the user to select which backup to restore, which goes beyond vbscripting capabilities and then you get into the file locking issues, … No, this is something I always handle myself.
Hi Daniel,
Thank you for the script, works well! However, if the database file is password protected, I’m getting asked the password. Is there a way to pass it as an argument to avoid this user interaction?
Regards
I suspect it is due to the Compact operation. Sadly, I do not believe there is a solution with 2007+. In prior versions there was a /pwd command line switch but it no longer exists (you could always try it though just to be 100% sure).
Indeed, it works when putting the bCompact parameter to False. I’ll stick to this workaround.
Thank you.
Mathieu, I created my own little password signon form in the database file and the backup routine works fine.
Thank a lot Daniel. Works well for me too. Apart the password issue, but I put bCompat to False and it s ok now. Have a nice day.
Since Access creates a lock file when you open a database and this process only backs up DBs when there is no lock file, is it correct to conclude this can’t be used on an open MDB / ACCDB file? The reason I ask is that someone linked to this as a solution to do just that.
Thanks in advance! And thanks for sharing the code!
Yes, you can use it against a open database, but this is not recommended. It is never a good idea to copy an Access database which is in use as you don’t know the state at the moment in time when you are performing the copy. You could copy the file in the middle of a write process and end up with corruption.
Great script thanks for posting!
I made a slight revision. In your script you compact the backup here:
Call CompactDB(sBackupFolder & “\” & sDBFile & “_” & sDateTimeStamp & “.” & sDBFileExt)
I changed it to compact the source database as so:
Call CompactDB(sSourceFolder & “\” & sDBFile & “.” & sDBFileExt)
This makes much more sense to me, since you want to keep your working file compacted and in good condition. It also works very well with your script since you backup the database before you compact the original this way.
For anyone interested, I SSH to the main computer and call this script from powershell. You can also use the commandline tool openfiles to make sure no one has yoru database open.
I had considered that option, but because Compaction can (in very rare cases) corrupt a database, I thought it best to only do so on the backup and not on the production database, just in case. I leave Compaction of the production to a manual operation that is validated. Since this shouldn’t need to be performed very often, this should be much of an impact.
Is there a solution to make it so if a lock file exists (DB is open) it doesn’t back up?
Whoops never mind. I see there is a variable to check for a lock file.
****THANK YOU Daniel!!!!!!****
This is pure awesomeness!!
Daniel, you are a blessing.
Thank you.
God Bless.
Would there be an easy way to limit the number of back-ups taken from within the script?
Perhaps count the number of files in the folder and perform a deletion if it exceed your quota.
First of all Thanks a lot for providing this script. It is really helpful.
I didn’t change anything in given code EXCEPT variables below
Dim sSourceFolder
Dim sBackupFolder
Dim sDBFile
Dim sDBFileExt
‘Set our required variables
sSourceFolder = “I:\DDS\Infrastructure\WebFocus\REPORTING\ID0173_IDList Test”
sBackupFolder = “I:\DDS\Infrastructure\WebFocus\REPORTING\ID0172_ITS EXtract_nfAccount”
sDBFile = “IDListsGUI”
sDBFileExt = “accdb”
‘Run the backup procedure
Call BackupDb(sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, True, True, True)
I am getting error below.It would be appreciated if you can help me on this.
Line:1
Char :36
Error : Expected end of statement
Code: 800A0401
Source :Microsoft VBScript Compilation error
No further action needed !! I figured out the issue and resolved it.
I have .mdb file which is password protected so every time when I run this script it asks for the password. I would like to automate this and need to figure out how can I skip password.
Hi Daniel,
Thank you for the post, I have not tried it yet, but the code looks straightforward. I am looking to run this code automatically , can you elaborate on ‘creating a scheduled task to run on a desired frequency and assign your vbscript to be run’? I am not familiar with using tasks and VBA, but this would greatly improve my backup situation.
Thanks,
Thanks!
Thanks Daniel; this saved me a lot of time! I did add one thing to create a log using the FSO object.
‘##### my code #########
Const fsoForWriting = 2
Dim objTextStream
‘#### my code ##########
Dim oFSO
.
.
Call CopyFile(oFSO, sSourceFolder, sBackupFolder, sDBFile, sDBFileExt, bCompact)
‘##### my code #########
Set objTextStream = oFSO.OpenTextFile(“C:\DBBackup\BE_Logfile_OperationSuccessful.txt”, fsoForWriting, True)
objTextStream.WriteLine “Backup was successful; The specified database file ‘” & sSourceFolder & “\” & sDBFile & “.” & sDBFileExt & “‘ was backed up.”
objTextStream.Close
Set objTextStream = Nothing
‘##### my code #########
Note: Put similar log entries (one for File not Found, and one for Lock on Database – wording a little different of course) after the message boxes (which I commented out), as I need this to be a Scheduled Task to run unattended.
pls the above vbscript will any be place inside the vb module in ms access
or everything will be on NOTEPAD, save as vbs?
Is there a way to open another Access database when closing the current one?
I already have a database that backs up a database and compacts it after the user selects the DB to be backed up and the destination.
Hi thanks a lot!
It works perfectly with english folder/file names.
But I get error “Path not found” if the folder name consists of non english characters.
Is there a solution to this?
Thanks again. Great code.
Hi,
Thanks a lot. Working fine.