Summary: This blog explains the step-by-step process to ‘backup and restore Access database’. It describes different ways to back up an Access database and restoring it from the backup that you have created. Also, it suggests having an Access database repair software at your disposal to repair corrupt Access database in the absence of an updated backup.
Data is crucial for any organization, and it must be protected. Data protection is possible by keeping a backup copy of the database with the help of effective backup software. But, if there is a disaster, then backup software alone does not help restore the database. It should be accompanied by a complete backup plan. So, what does the data backup plan states? The detailed data protection plan states that data must be backed up as per the ‘3-2-1’ rule.
As per the 3-2-1 rule, database administrators must maintain three copies of important data, store these copies on two different storage types, and store at least one copy of the data offsite. Once the backup plan and the backup software is in place, it is easy to restore the database, be it in any form including Access database.
Let’s create a step-by-step plan to backup and restore Access Database:
- Create a regular backup plan
- Perform Access database backup
- Perform backup for the split – backend and frontend database
- Restore database with the backup
Step 1: Create a regular backup plan
In Microsoft Access database, some changes or mistakes are irreversible. For example, using an action query to change an entry or delete a record. Such entries cannot be reversed by using the undo function. You need to restore Access database from the backup or you may suffer the data loss.
A backup plan must consist of:
- Schedule for data backup (Time for regular backup and name of backup software)
- Access Repair software (An alternative solution to repair Access database in the absence of the latest and healthy backup)
Note: Multiple users use the same database at a given point in time. Make sure that the users have logged out of Access database before starting the scheduled backup.
You can try the demo version of Stellar Repair for Access from here:
Step 2: Perform Access Database Backup
Note: Access database closes all objects open in the design view and saves them at the specified location with the specified name.
To perform a backup, open the Access database for which back up is to be performed and execute the following steps:
- Go to File and select Save As
- Under the File Types, select the option Save Database As
- Under Advanced, click Backup database followed by Save As
- The Save As dialog box opens. Check the name of the database in the Filename box
Note: The default filename is a name of the database suffixed with the date of database backup. It can be changed as per convenience.
- From the Save as type list, select the format to save the database followed by clicking the Save button.
Step 3: Perform backup for split database
As the name suggests, a Split database consists of two databases:
- Backend database – data in Tables only
- Frontend database – contains links to the Tables in the backend Access database along with queries, forms, reports and other database objects
In short, the database is in the backend, and the user interface is in the frontend database. Hence, back up for the frontend database can be done periodically, whenever there is design or interface change.
Because the backend database contains data, administrators should schedule regular backups of the backend database. And there is no need to perform the lengthy split database.
a. Perform backup of backend database
The first step is to notify the users to log out of the Access Database. This is because users may not be able to access the database during the backup process.
The Administrator should perform the following steps:
- Start Microsoft Access and go to Open Other Files followed by Computer and then Browse
- Select the backend database file to backup
- Click the arrow next to open and from the drop-down menu, click Open Exclusive
- Perform Step 1 to 5 as mentioned in “Perform Access Database Backup.”
b. Perform backup of frontend database
The front-end database backup is performed when there is a change in the design or user interface.
- Once the design is changed, leave the database open
- Follow the steps mentioned in the backend database starting step 1 to step 4
Save the database with the default name, having date and filename, to search with ease whenever there is a need for database restoration.
For the best results, automate the backup process by using a product that can facilitate automated backups of the file systems. These may include file server backup software or external device backup software, etc.
Step 4: Restore database with backup
You need to restore the database in case the database file is damaged, has accessibility/connectivity problems, or it is missing altogether.
Note: You can restore a database from a ‘good backup copy’ of the database file supposed to have database integrity and design.
Perform the following steps to restore Access database from backup:
- Open file Explorer. Browse and select the known good copy of Access database
- Copy the file to the location of the damaged or missing database
- Replace the existing file, when you are prompted
You can also restore database objects in a database, but care must be taken in selecting the objects to be restored. If there is a choice, then it is recommended to restore the whole database.
If there is an issue with the Access database, then Admin must have a healthy and restorable copy of the data backup to restore the database. While most organizations back up their database including their Access database, these are not audited regularly, which leads to unhealthy and non-restorable copies. This may further lead to prolonged inaccessibility of important Access database. It’s better to act fast in such situations and repair the corrupt Access database with the help of effective Access repair software.