Backing Up A Database - Workbench Help File

Why Backup A Database

Though Microsoft Access stability has improved a lot since the early days, things still can go wrong and users and even developers can accidentally delete important database objects and data. As I explain in great detail in my book on Protecting Access databases, understanding how to backup is an essential task for a Database Administrator. More important than this is testing to make sure that your recovery systems actually work on a regular basis. If you have Access 2003, there is a backup wizard that undertakes a similar process to this tool.

Caution: Whilst these utilities are state of the art from Microsoft, you still should maintain regular backups of your computer's hard drive just in case of the unthinkable ...

Compacting your database

The best guaranteed way of having an accurate Access backup is open Access without opening any databases and to then compact the Access database to another filename using the menu { Tools ... Database ... Compact Database }. This will only work if you are the only one using the database. If you undertake a normal backup using a tape drive or a cd-rom, you will not be guaranteed to recover the database properly if someone already has the database open. The backup system in the Workbench is important because it doesn't fall into this trap. Read on...

Tip: If you have a front end and backend configuration for your Access application, do not forget to backup the backend database at regular intervals as well.

The Workbench Provides A Backup Process That Is Not Frustrating To Use

The biggest issue that you face when trying to backup a busy database regularly, is trying to get everyone out of the database before you run the process. To help you with this, the Access workbench has two options. Firstly there is the Lock checkbox on the main screen that will stop any new person from logging into the database (whilst the lock is on).

The second option is the Backup checkbox (as shown in Figure 1). If you select this checkbox, this tells the workbench to backup your database the next time everyone is out the database. To work out when to do this, the workbench checks a list of users currently in the database using the Who's On button. If this resolves that no one is using the database, the workbench will immediately backup your database to the Backup Folder specified on the Backup tab as shown in Figure 1. It does this using a well tested Microsoft facility that is installed with all MS Access installations.

backup1

Figure 1 - The Backup checkbox and the Backup tab.

How To Backup Your Database

Select the Backup checkbox and press the Who's On button.

If you know that there are users in your database, select the backup checkbox and wait till they all log out (at your request). Then click the Who's On button. Alternatively wait until the Who's On list is refreshed at the next regular interval as defined in the Options form (see Figure 3). (Press the Options Button to see these options).

How To Backup Your Database to a Second Folder (on say a portable drive)

Select the Backup Tab and choose the 2 option box as shown in Figure 3.

How to Zip Backup all the databases in a folder

Select the Backup Tab and press the Zip Backup button (see yellow arrow in figure 2). This will add all the databases in the database folder into a zip file in the backup folder. The convention used is FOLDERNAME YYYY-MM-DD HH_MM Databases.zip filename.

How to Zip Backup all the files in a folder and all its subfolders

Select the Backup Tab, select the all files in the folder checkbox (see red tick) and press the Zip Backup button (see red arrow in figure 2). This will add all the files in the database folder into a zip file in the backup folder. All the files in every subfolder under the database folder will also be added to the zip file. The convention used is FOLDERNAME YYYY-MM-DD HH_MM FOLDER.zip filename.

Note: For both database zip options, the databases will not be compressed when they are placed in the zip file. If the databases are in use, you will probably get a Zip warning that the database was in use.

backup2

Figure 2 - The Backup tab

Where Do Your Backups Go

All databases are backed up to to either Folder 1 or Folder 2 irrespective of which version of Access that you are using. This can be any folder on your network and ideally one of the folders should be on a different computer or on a protable hard drive that is not stored with the computer. You should also consider using a folder that is backed up securely online with a tool such as Norton's online backup or Dropbox.com

NOTE: Occasionally you will need to ensure that the database backup files are working and you will need to cleanup or compress the backups in the backup folder.

How Garry Uses Workbench Backups

Whenever I am developing a database, I will open the database using the Workbench as this makes opening different versions of Access databases easier. Before I start work on the database, I will simply select the backup checkbox and then press the Open button. When I am going out for a cup of coffee or lunch. I will close down the database. Then the Workbench will automatically make a backup for me whilst I am on a break. If I also select the Compact checkbox, the Workbench also will compact the database for me. On my return, these easy to forget but necessary chores will be handled for me.

Open A Backup Database

Occasionally it is necessary to return to a backup copy to check on something that you may have altered in the live version of the database. To do this you can select the database that you want to open in the folder list (as shown in Figure 2) and then click on the Open Backup File link in the Backup Tab.

Delete A Backup Database

If you use this backup option often, you will probably need to clear up your old databases from the backup folder. To do this select the database according to the date and time in the backup database name (YYYY-MM-DD HH_MM DB Name). Now click on the Delete Backup File link and then confirm that the database is to be deleted. Naturally you would need to verify that you would have a full hard drive backup of the backup folder because the Delete Backup button does NOT send the file to the recycle bin.

Folder Filter

The folder will allow you to show files with the same file names. Try
*FOLDER* for all folder backups
*peril* for all backups of databases with peril in the name
*.mdb for all mdb uncompressed backups

Find out how to setup a backup period in your Workbench favorites

in the Workbench Favorites Tab

Notes On Zip Backups

Creating Compressed (.ZIP) Archives of an Access database

Find Out More About Keeping An Access Database In Tip Top Condition

Access 2002/2003 http://support.microsoft.com/kb/303528/EN-US/#8

comp3

Figure 3 - The Options form lets you set the number of minutes between refreshing the Who's On list.