MS Access – Automatic Backup of an Access Database

Another common question in Access forums is

I would like to know if there is a way I can automatically backup my database

There are various approaches that can be implemented depending on your needs and/or setup.

Manual Backup

The goto technique, simply use Windows Explorer to make copies yourself as you see fit. (yes, I know, this isn’t an automatic system)

PROs

  • You don’t need to rely on another department (You’re in control)
  • You don’t need to get any permissions/approvals ..

CONs

  • Labor intensive
  • Easy to forget
  • Frequency can lead to issues for restoring unless you are very meticulous

 

File History

Another solution can be to turn on Windows built-in File History. This isn’t something I’ve used, so I can’t give much insight here, but Googling will provide a number of tutorials on the subject such as:

How to Use Windows' File History to Back Up Your Data
File History is Windows 10's main backup tool, originally introduced in Windows 8 ...

 

System Backup

Add your database to be included as part of your PC backup routine (like you might do for your Documents, Pictures, …)

PROs

  • You don’t need to rely on another department (You’re in control)
  • You don’t need to get any permissions/approvals …
  • Easy to setup if you already have backup software that you use
  • Part of a scheduled routine so you don’t need to worry about it

CONs

  • Doesn’t ensure no one is actively using the database when backups are performed

 

Windows Scheduled Task

Create a scheduled task in Windows to run a VBScript that would perform a backup.

PROs

  • You don’t need to rely on another department (You’re in control)
  • You don’t need to get any permissions/approvals …
  • Part of a scheduled routine so you don’t need to worry about it

CONs

  • One more thing to setup and administer
VBScript Sample
You may like to review MS Access – Backup a Database Using a VBScript for a great VBScript to backup an Access database.

 

IT Department

The easiest solution, in a corporate environment, is to simply ask the IT department to include your database’s folder as part of their automated backups.

PROs

  • One less thing for you to setup and administer
  • Is done using enterprise level backup software
  • Part of a scheduled routine so you don’t need to worry about it

CONs

  • Makes you reliant on the IT Department.
    • If you have a responsive IT department this isn’t an issue, but if your IT department is unresponsive, this can be very problematic when you need to test the backup system, or god forbid, when you actually need to perform a recovery.

 

Database Startup

You could setup your database, at startup, to check if a backup was done, and if not do so before continuing to load up.

PROs

  • You don’t need to rely on another department (You’re in control)
  • You don’t need to get any permissions/approvals …
  • You don’t need to even learn about setting up schedule tasks
  • You can easily add logging and e-mail notifications as part of the backup process and create a graphical interface to reviewing the backups

CONs

  • One more thing to setup and administer (more work for you)

One reason I like this approach is that by using Access, you can display a form to let the user know a backup is being performed.  You can even give them a form with a history of backup …

 

What Should be Backed Up

Obviously, everything should be backed up initially, at least once, but after the initial backup, there is no need to backup the database Front-End, and/or other static files (unless they change – new version of the front-end for instance).  Furthermore, as you release updates to your front-end or other files, you are going to have them backed up again so you always have a copy of the every version.

What is critical to backup on a regular basis in the database Back-End (the data)

Lock Files
An important aspect of ensuring that your backups are not corrupted is to ensure no one is actively using the database when the backup is made.  As such, the easiest way to do this is to first check for the existence of a lock file (ldb, laccdb).  If a lock file exists, then someone is using the database, so it is not a good time to perform the backup.  This is also why it is best to perform your backup when no one is in the office (at night) or at the startup of the database, thus the first user triggers the backup before loading any form, reports, …

 

Backup Validation

It is critical to regularly validate that the backup process executed, but also, to actually validate the backup files themselves.  I mean you need to actually take a backup, perform a recovery and open the database files to ensure the file/data is sound.

Don’t laugh, I was brought into a company who had years of backups, but they turned out to all be corrupted, so in fact they had no valid backups and no one had ever checked prior to actually needing them.  At that point it was too late and they lost their entire database!

 

Why Back Up Your Database

I’ve seen people over the years say they don’t need to worry about performing backups.  This is simply ludicrous!  There are a multitude of reasons why backing up your files, little alone your database (your data).  Things like needing to recover from

  • Accidental development errors
  • Accidental file and/or record deletion
  • Corruption
  • Computer/hard drive crashes

Backups are simply critical!  After spending typically hundreds of hours, thousands of dollars, developing a database, does it not make sense to protect it and the data it warehouses!