Using The Workbench to Backup A Database              

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

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.

Compacting your database

The best guaranteed way of having an accurate Access backup is open Access without opening any databases and to 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.

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 2).  (Press the Options Button to see these options).

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

How to Zip Backup your database

Select the Backup Tab and press the Zip Backup button. This will compress the database to the backup folder using a YYYY-MM-DD DbaseName.Zip filename. The database will not be compressed and if it is in use, you will probably get a Zip warning that the database was in use.

Where Do Your Backups Go

All databases are backed up to the one folder irrespective of which version of Access that you are using. This can be any folder on your network and ideally it should be on a different computer and in a different physical location than the computer that the database is on. You should also consider making sure that the folder is protected by the operating system. I discuss this in detail in my book.

The backup process has been made deliberately easy and safe for the databases. To achieve this, each backup is given a date and time stamp to make it unique. As you can see in the example above, the Northwind database has a prefix of YYYY-MM-DD HH_MM followed by the actual database name.

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 database button in the Backup Tab. When the database is opened, it will do so using the currently selected workgroup file and user name.

Figure 2 - Select a backup database in the backup folder to open (and delete)

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 button and then confirm that the database is to be deleted. Please note that only databases whose names start with 20* will be displayed in this list and only the backup folder will be selected. 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.

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 2000 http://support.microsoft.com/Default.aspx?KBID=300216#8
Access 2002/2003 http://support.microsoft.com/kb/303528/EN-US/#8
 

Other Access Workbench Topics
Home Page | Starting Up | MRU Select | Stop New Users | Install | Internal Logging | FAQ Page  | Orders

The Access Workbench Help File  - Backing Up A Database