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.
Database Backups
Situations may arise where your database is
deleted, corrupted beyond repair, or modified in a way
that requires
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
plan.
Have an Off-Site Backup
It's also important to have your backups
available away
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.
If
the database isn't too big, you can zip it up and email it to a
free Hotmail,
Google or Yahoo account. You can also create a
Microsoft hosted
Office Live /
SkyDrive account where you can store up to 25GB for free.
You can even synchronize your desktop to the web site. There
are other paid storage services but they seem unnecessary
given SkyDrive.
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
As
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
demo of
Total Visual Agent is available.
Microsoft Access Database System Documentation
On a related note, make sure you have good technical
documentation of our 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
improve performance.
Conclusions
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.
Hope this helps. Good luck!
