The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
April 2010

Backing Up Your Database: The Good, the Bad,
and the Ugly

I've the pleasure of working with a variety of people during the course of my varied careers. Some folks seem to be a constant state of anxiety. They know that stuff is going to go wrong and they are sure that the hassle factor will be immense.

Then there are those folks who don't worry about problems at all. They too know that things will go wrong, but they don't worry about it - not because they don't care or they refuse to let it get to them. Rather, they don't worry because they have a contingency plan. And, if the contingency plan doesn't work, they just go to the next step in their chain of contingency plans. They can relax because they know they have a method for dealing with things when they go wrong.

Ask yourself this question: If your database became corrupt right now what would you do? Are you one of those folks who would say "This is horrible!" or are you one of the folks who would say, "Okay time to go to the back up"?

No doubt, you want to be in the second group, not the first. This month we'll talk about the good, the bad, and the ugly of database backups.

In this Issue
  • Tip of the Month: Offsite Backups - Wear Both a Belt and Suspenders
  • No Back Up at All: The Ugly
  • Backing Up a Database: The Bad
  • Backing Up a Database: The Good
  • Conclusion: How to Sleep Soundly

  • No Back Up at All: The Ugly

    There are some folks who don't back up their database files -- no backup whatsoever. The reason they don't is because they've never lost a file due to corruption, or turned on their computer and have the hard drive not spin. In other words, they haven't yet been burned. If this sounds like you, stop what you're doing, and back up your database now!

    You do not want to be in the position of losing all the work that you have stored in your database. Every single disc in every single computer will fail. It's not a question of if; it's a question of when.

    Backing Up a Database: The Bad

    There are three main problems that most people run into when backing up their databases.

    1. They don't have a regular backup schedule. The problem with this is obvious. If you haven't backed up for two weeks, and your database becomes corrupt, you've lost two weeks worth of work. You want to backup your database on a daily basis at least.

      The primary reason people don't back up on a regular basis is because it is a manual process. Any process that takes human intervention is subject to our imperfections. As a result, although we may mean to do a daily backup, the hubble-bubble of life gets in the way and it just doesn't happen.

    2. The second problem is that, although they might be backing up their databases on a regular basis, they never test them. In this situation, there may be a flaw with the backup procedure. Or maybe you think you're backing up your database, but you're really not.

      You don't want to discover this flaw on the day you need your backup. The only way that you can know that your procedures are working properly is to actually test the results. It's a fairly simple process. You take your existing database, rename it something innocuous, install your backup database, and see if it works properly. If it does, you're all set. If not, you take a very close look at your backup procedures to find out where it's breaking down.

    3. The third problem is much more subtle. There is an effective procedure, but the backup file has a name and location that might cause it to be accidentally used in place of the live file. If you work on a machine that is not part of a network, and you're the only person who uses the database, this is probably not a very big issue. However, if you work on the network with a database that is shared among people, then it's important to have a backup that can't be accidentally mistaken for the live file.

      My recommendation is that when you're doing a backup, have a clear naming convention that makes it difficult to mistake the production database with the backup copy.

    Backing Up a Database: The Good

    The ideal way to back up a database is to take it out of your hands. Some systems create a backup every single day with no human intervention required. Your database gets backed up automatically. There are many software packages out there that can do this for you. I use a program called Second Copy. Every day, it backs up all my files with no thought or action from me.

    I periodically test to make sure that things are working the way they should. Because of the nature of my work, I actually have redundant copies, so that if something does go wrong with the media in my backup machine, I just go to a copy of the backup, which I keep on a different machine.

    I don't typically work on a network shared with others, so I don't worry about a strict naming convention. But when I work with my customers, I help them set up a system that makes it difficult to mistake a backup database with the live file. In some cases, I help them by making it impossible to pick up a backup file accidentally.

    Conclusion: How to Sleep Soundly

    Yes, I know that life is more than just databases. However, if you back up your databases on a regular basis, and test the backups periodically, you won't have to worry about what happens when good data goes bad. You just go to your backup plan.

    Tip of the Month: Offsite Backups - Wear Both a Belt and Suspenders

    If your database is mission critical, to either your life or your business, then you want to take an extra step to be comfortable that you can recover from corruption. Consider the case where, God forbid, a fire breaks out in your house/office and your computer and everything else is damaged beyond recovery. How would you be able to cope with this as far as a database is concerned?

    The solution is fairly straightforward: Off-site data backup. There are two approaches to this. The first is a physical backup that is stored someplace other than your home or business.

    If you're a business, have someone take home a backup on a regular basis. If it's personal information, stash a backup copy with a friend, neighbor, or family member. Both of those work just fine.

    The other potential way to handle the problem is to backup to the cloud. If you Google data backup, you'll find a host of services that will do this.

    A couple of things to note: Make sure that the company that you are backing up to is reliable. It will do you no good to have a backup in the cloud with a company that no longer exists. If a company has any potential of going out of business, steer clear. The same is true of the relatively new companies. Who knows how good a job they do?

    Whichever company you choose, it would be wise to make an assessment of its financial health. If you don't have the tools to do that, ask a friend to take a look. Since you're backing up your data, you want to make sure that you can get it back when you need it.

    And if you're backing up to the cloud, its vitally important that you periodically test the backup to make sure that things are working as planned.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!


    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886