MS Access – Backup a Database Using a VBScript

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.

36 responses on “MS Access – Backup a Database Using a VBScript”

peter roth February 25, 2018 at 5:17 pm

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?
1. Daniel Pineault Post authorFebruary 25, 2018 at 6:09 pm
  
  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
Pawel Braty February 28, 2018 at 12:31 pm

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
1. Daniel Pineault Post authorFebruary 28, 2018 at 1:37 pm
  
  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.
djannag March 6, 2018 at 4:42 pm

Daniel, thank you ever so for you post.Much thanks again.
Erika March 8, 2018 at 11:58 am

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”
1. Daniel Pineault Post authorMarch 8, 2018 at 12:44 pm
  
  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.
  1. Erika March 8, 2018 at 2:50 pm
    
    I will double-check everything. Thanks!
    1. Erika March 8, 2018 at 2:55 pm
      
      Aw, very cool. It is now working. I had accidentally included “_backup” at the end of the regular database name. Thanks for this!
      1. Daniel Pineault Post authorMarch 8, 2018 at 3:11 pm
        
        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.
Michael Addo January 5, 2019 at 1:45 am

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
1. Daniel Pineault Post authorJanuary 5, 2019 at 9:26 am
  
  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.
Mathieu Denotte January 17, 2019 at 9:00 am

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
1. Daniel Pineault Post authorJanuary 17, 2019 at 9:26 am
  
  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).
  1. Mathieu Denotte January 17, 2019 at 11:00 am
    
    Indeed, it works when putting the bCompact parameter to False. I’ll stick to this workaround.
    
    Thank you.
2. James Hill May 1, 2019 at 3:09 pm
  
  Mathieu, I created my own little password signon form in the database file and the backup routine works fine.
Bruce Maiga February 10, 2019 at 1:06 pm

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.
Anthony February 13, 2019 at 10:31 pm

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!
1. Daniel Pineault Post authorFebruary 15, 2019 at 8:47 am
  
  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.
meaghs March 11, 2019 at 10:37 pm

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.
1. Daniel Pineault Post authorMarch 12, 2019 at 7:14 pm
  
  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.
Jacob May 7, 2019 at 4:35 pm

Is there a solution to make it so if a lock file exists (DB is open) it doesn’t back up?
1. Jacob May 7, 2019 at 6:13 pm
  
  Whoops never mind. I see there is a variable to check for a lock file.
Jose O. May 29, 2019 at 4:26 pm

****THANK YOU Daniel!!!!!!****

This is pure awesomeness!!
Philip Jacob August 1, 2019 at 8:10 am

Daniel, you are a blessing.
Thank you.
God Bless.
Anon December 5, 2019 at 12:00 pm

Would there be an easy way to limit the number of back-ups taken from within the script?
1. Daniel Pineault Post authorDecember 9, 2019 at 5:57 am
  
  Perhaps count the number of files in the folder and perform a deletion if it exceed your quota.
Pritam January 15, 2020 at 2:18 pm

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
1. Pritam January 15, 2020 at 2:38 pm
  
  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.
Justin February 25, 2020 at 11:56 am

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,
Satnam Tathgur November 26, 2020 at 11:17 am

Thanks!
Stephen Barber June 11, 2021 at 7:07 pm

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.
Akokoh Joel May 28, 2022 at 11:48 am

pls the above vbscript will any be place inside the vb module in ms access
or everything will be on NOTEPAD, save as vbs?
Garry Smith June 23, 2022 at 8:57 am

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.
Kostas July 13, 2022 at 2:32 am

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.
Vikas Patil February 9, 2023 at 4:16 am

Hi,
Thanks a lot. Working fine.