Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications
Provided by Luke Chung, FMS President
Are You Personally Responsible?
If you create or take over a Microsoft Access application from someone else, you become responsible for the data in addition to the application and its queries, forms, reports, macros, and module code.
If you're responsible for an application, avoid any chance of being blamed if a hardware failure, user mistake, or database corruption wipes out data or copies of the application.
Often, it's too late before people realize their data is corrupted or lost. Don't let this happen to you. With a little planning you can make sure your database remains healthy and can survive when the unexpected occurs.
What is Disaster Recovery or Business Process Continuation?
For information workers not trained on system support, disaster recovery or business process continuation (BPC) is often an after-thought. Most of the time, if you lose your work in a Word document or Excel spreadsheet,
it only affects you. However, database applications, especially multi-user ones, have other people depending on it. If something goes wrong, a lot of users may be impacted and significant work could be permanently lost.
Unanticipated disasters can occur, so it's important to prepare before they happen. The amount of effort and investment to spend for disaster recovery should vary with the value of the application, but some basics apply
to all applications:
- Microsoft Access database storage corruption or loss
- Front end (application) database corruption or loss (if you have a front-end/back-end split database design)
- Hardware failure for servers, network, and desktops
- Temporary inability to get to the network or office
- Complete loss of facilities (e.g. fire)
Visit Wikipedia for more details on disaster recovery.
Business Process Continuation
Business Process Continuation is a slightly different topic. BPC addresses assumes you have your application but there is a disruption to your business. For instance, your network fails, your internet connection or phone system
dies, your site burns down or is condemned. What would you do and where would you go to get back to business? Since it's outside the realm of Microsoft Access, this paper won't go into business continuity. We'll focus on making sure you
have the information on the platform required for your application and the backups to successfully redeploy.
Document What You Have: Application Inventory
The first part of a disaster recovery plan is understanding what you have. Microsoft Access applications are not enterprise systems and touch fewer systems, so it should be relatively easy to do this.
Basically, it's an inventory of the objects that make up your application and how they are configured. Be sure to include:
- Names and locations of the database(s)
- Names, locations, and versions of linked databases such as SQL Server
- Any dependencies on ActiveX controls or DLLs and how they are installed on the user's machine
- Other files that may be related to the applications (e.g. graphics, Word or Excel documents it may use, etc.)
- Security information such as workgroup security files, user and group definitions, login names, passwords, rights, etc.
- Windows Operating systems and the Access/Office versions
- Location of master and backup files of your application
- Location of the application setup programs you need to install (e.g. Windows, Office, SQL Server, etc.)
Once you have all this information, you should be able to deploy your application on a new system. You may not need to worry about configuring a server or network (that's probably someone else's
responsibility) but you will need to successfully deploy your application assuming that infrastructure is in place.
Implementing a Disaster Recovery Plan
There are several levels of disasters that you need to anticipate in increasing severity:
- Compact your Access database to prevent database corruption and reduce bloat
- Make backups to recover from hardware failure and user mistakes
- Maintain off-site and online backups
- Automate and audit the maintenance tasks
- Test to verify the backups actually exist and the recovery plan works
General Maintenance: Database Compact and Repair
The first level is making sure your Access databases remain healthy. Unlike server based databases with their built-in administration, file server databases like Access/JET MDB/ACCDB files need to be
periodically compacted and repaired for optimal performance. This also eliminates database bloat that can occur as data is added and deleted in the database. Here's a Microsoft Knowledgebase
article that discusses some of the issues.
VBA Module Corruption
If your database has VBA code that is modified (not common for the back-end database that just contains the data), you need to decompile the database, then
compact it to eliminate the unused VBA compile states. Read our paper on Decompile Your Microsoft Access Database to Improve Performance, Fix Corruption, and Avoid Strange Errors
for more information.
Manually Compacting Your Database
There are a few ways to compact your database from Access:
Keep in mind however, that in a multi-user environment where you have a back-end
database containing the shared data, compacting the
front-end database does not compact the back-end database. A
reliable disaster recovery plan is not generally
based on someone remembering to do something manually.
How Often Should You Compact and Repair?
How often the database is compacted depends on how
often it's used, how often the data changes, and how
valuable/difficult it would be to recover the data if it
became corrupted. Compacting the database requires an exclusive lock on
the database which means there can be no other users in
the database at that time. We generally compact the
database containing the data every night with our
automated program Total
Visual Agent which can manage all the databases
across a network.
Databases in Continuous Use
Databases used in 24/7 environments make it
impossible to compact on a daily basis. These high use
databases are probably very important so proper maintenance
is imperative. Situations like these often warrant migrating
the database to a server like SQL
Server. The front-end can remain in Access, but a more
robust server based storage system will provide more
administrative system support. That
way the server can perform its maintenance tasks while
the database is being used by others.
Situations may arise where your database is
deleted, corrupted beyond repair, or modified in a way
recovering data from the past (e.g. a user runs a delete
query by mistake). For all these types of
situations, it's critical to have backups (copies) of
the data in your database over time.
How often you should create a backup depends on how
critical the data is. If all the data exists elsewhere and
you're simply importing it, it may not need to be backed
up at all. If, however, it's the only copy of the data and would
be difficult or impossible to recreate, you need to
make sure it's backed up often enough to avoid the cost
of recreating the data since the last backup.
Backups are as simple as copying the database from
Windows. However, if people are in the database and
changing data, you may not be able to get a clean copy.
We also use Total Visual Agent to handle backups
nightly when the database compact is performed. Total
Visual Agent has a rolling backup feature that lets you
keep multiple backups of every database and zip them up
to take less space.
Database Backup Locations
Where you store your database backup may be more critical
as creating the backup.
A database backup on the same
machine (hard disk) as the original database does not count
If the machine or hard disk fails, the backup may be lost
too. Store the backup somewhere else such as another network
drive or a separate hard disk. Having it only on the same
machine is definitely an unacceptable disaster recovery
Have an Off-Site Backup
It's also important to have your backups
from your production site. In case you can't get
into your building, or if there's a fire that
destroys everything, you need to recover and run your database elsewhere. This may
be as simple as taking a copy of your database,
putting it on a thumb drive or hard disk, and storing
it at home (assuming that doesn't violate security
protocols). Make sure you have all the pieces that
you've listed in your application inventory offsite.
Consider an Online Backup
You can also take advantage of online storage for
backups. This can make it much easier than manually
moving disks off-site and may let you keep more
current copies offsite. There may be security
concerns about another entity hosting your files, so
make sure you don't violate any privacy and security
rules by doing this.
the database isn't too big, you can zip it up and email it to a
Google or Yahoo account. You can also use Microsoft
(formerly SkyDrive) to synchronize a local folder with its
cloud storage. Microsoft offers several gigabytes of free
storage with more for purchase or combined with an Office365
are other paid storage services but they seem unnecessary
Document the Disaster Recovery Plan
Your Disaster Recovery Plan should include the
Application Inventory along with the processes of
how, when, and where your backups are stored both on
and off site. Be sure to include workgroup security
files, admin and user logins and passwords. Any
linked databases and external file dependencies
should also be documented.
The plan should also include options for the hardware and
where you'd recreate the database if your current platform
is not available. Finally, it should specify who is
responsible for what before and after disaster strikes.
Test the Plan
There are countless stories of disaster recovery plans that were
never actually tested. So when a disaster actually struck,
people discovered that pieces were missing or backups weren't being made
after all. Recovery instructions can easily get out
of date, so it's important to avoid an unrecoverable
failure by periodically testing the plan.
Automating Compacts and Backups with an Audit Trail
discussed earlier, our
Total Visual Agent
program executes the repetitive database chores that
are necessary to keep your Access applications healthy.
It lets you compact, zip and create backups of
databases, run data extracts if users are still in it,
collect database statistics, and launch any macro on a
schedule you specify. Events can be scheduled hourly,
daily, weekly, monthly, or at a time you specify.
A critical part of a good disaster recovery plan is that
the tasks performed are recorded and that if something goes
wrong, you are notified. Total Visual Agent logs its tasks
to an Access table and sends an email if something
unexpected occurs. It can run as a standalone program or an
NT Service which provides higher security and automatic
restart if the machine is rebooted.
Maintenance Chores Evolve Over Time
Over time, databases grow and tasks that may have taken
seconds or minutes could take much longer. Total Visual
Agent has a feature to let you specify when a scheduled
event must end. For instance, you can ask to compact and
backup all the databases on a network drive but want it to
be done by 4:00 AM so it doesn't interfere with morning
workers. Today, a midnight task may finish in an hour but
that could grow over time, so it's important to be notified when your constraints are violated.
A fully functional
Total Visual Agent is available.
Microsoft Access Database System Documentation
On a related note, make sure you have good technical
documentation of your Microsoft Access database. This is not
only helpful for disaster recovery, it's a fundamental part
of being able to maintain a software application over time.
Whether you or someone else is the next developer,
comprehensive documentation will significantly reduce the
effort needed to address problems that occur. Our
Total Access Analyzer
program generates both high level and detailed documentation
of your database, and performs analysis to pinpoint errors,
suggest design enhancements, and finds opportunities to
Make sure you take the time to review and consider what
would happen if disaster struck. It's good service to your
users and good for your career. If you can't get the time to
do this properly, make sure someone else takes
responsibility for it...preferably in writing. If you find
yourself suddenly responsible for an application you didn't
create, read our whitepaper: Taking
Over (Inheriting) Legacy Microsoft Access Database
Hope this helps. Good luck!