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 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 UNREGISTERED EVALUATION VERSION 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 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 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. See Figure 3.
Figure 3 - 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 making sure that the folder is protected by the operating system. I discuss this in detail in UNREGISTERED EVALUATION VERSION.
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 UNREGISTERED EVALUATION VERSION.
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 2002/2003 http://support.microsoft.com/kb/303528/EN-US/#8
Other Access Workbench Topics
Home Page | Starting Up | MRU Select | Stop New Users | UNREGISTERED EVALUATION VERSION | Internal Logging | FAQ Page | Orders
This help file was created with an unregistered evaluation copy of Help & Manual. © EC Software. All rights reserved. This message will not appear if you compile this help file with the registered version of Help & Manual.