Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications

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.

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.

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.

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

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.

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:

• Manually Compact and Repair from the Access menu
• Automatically Compact the Database when You Close it

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 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.

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 use Microsoft OneDrive (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 subscription. There are other paid storage services but they seem unnecessary given OneDrive.

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.

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.

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.

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 improve performance.

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 Applications.

Hope this helps. Good luck!