How to Backup & Restore of MS Access Database

Microsoft Access is a relational database application used for creating, managing, and formatting data. It helps you to store information for future reference, reporting, and analysis. The reason for the immense popularity of MS Access lies in its simplicity – even a novice developer can create simple databases in a short span of time. It is the most widely used desktop database system in the world. 

As data is extremely crucial for any organization, it becomes imperative for all Access users to maintain a proper plan to backup the MS Access database. In case a data loss situation arises due to corruption, unintended deletion, or abrupt design changes in the Access database, you have the option to recover the lost data.

How to Backup and Restore MS Access Database 

In this section, we will focus on creating a plan to backup the MS Access database and steps to restore the database. 

Maintain Regular Access Database Backups

Maintaining regular backups is the best strategy to counter data loss arising due to system failure. Also, certain changes or mistakes are irreversible, so if you backup the MS Access database, it will save you from a lot of inconveniences. For example, when you perform an action query to delete records or change data, the updated values cannot be restored by the Undo function.

Keep the following guidelines in mind, while deciding how often you should backup your MS Access database:

• If the database is an archive, or it is mostly used for reference purposes and rarely changes, it is sufficient to create backups only when the design or data is changed. 
• If the database is active and prone to frequent data changes, create a schedule for regular backup of the database. 
• If the database has multiple users, create a backup copy of the database after a design change. 

Note: If the database has several users before you perform a backup make sure that all users have logged out so that all changes to the data are saved.

Backup MS Access Database 

When you create a backup for the MS Access database, Access saves and closes objects that are open in the Design view and saves a copy of the database file with a name and location as specified by you. Open the MS Access database for which you wish to create a backup and follow the instructions given below :

1. Click File>Save As.
2. Under File Types, click Save Database As.
3. Under Advanced, click Back Up Database, and then click Save As.
4. In the Save As dialog box, review the name of your database backup in the File name box.
5. From the Save As Type list, select the file type in which you want the backup database to be saved as and then click Save.

Note: When you restore data or objects from a backup, you want to know the database from which the backup came and when it was created. So it’s a good practice to use the default file name.

Perform Backup for Split Database 

As the name implies, a Split database consists of two databases :

1. Backend Database: which contains data in tables only. 
2. Frontend Database: which contains links to the tables in the backend database along with queries, forms, reports, and other database objects.

All the data is stored in the backend database while the user interface is kept in the frontend database. Since the backend database contains all the data, it is important to schedule regular backups for it. On the other hand, you can perform a backup of the frontend database whenever there is a change in design or interface. This way you can save yourself from the inconvenience of performing a lengthy split database. 

Create a Backup of Backend Database

Before you start the backup process, notify the users to log out as they will not be able to access the database during the backup process. To back up the backend database, follow the instructions given below:

1. To open the backend database only, start MS Access on your computer. 
2. Go to Open Other Files > Computer > Browse and then select the backend database file you want to backup. 
3. Click the arrow next to Open, and then click Open Exclusive.
4. Click File > Save As.
5. Under File Types, click Save Database As.
6. Under Advanced, click Backup Database As and then click save as.
7. In the Save As dialog box, check the name of the database in the File name box. 
8. Select a location in the Save As dialog box to save the backup copy of your backend database and then click Save.

Create a Backup of Frontend Database 

Backup for a Frontend database is performed when there is a change in design or user interface. Follow the instructions given below to perform backup for frontend database:

1. Leave the database open immediately after a design change. 
2. Follow the steps mentioned in the section Backup MS Access Database starting at step 2.

Save the database with a default name, having date and filename, so that you can easily search it whenever there is any requirement for database restoration. 

Restore MS Access Database

A backup is considered to be a “known good copy “– a copy of which you are sure of the data integrity and design. You need to restore the MS Access database in case a database file is damaged, has accessibility or connectivity issues, or is missing altogether. You can restore a database if you have a good backup copy. To restore the MS Access database from a backup, follow the steps given below:

1. Open File Explorer and browse and select the known good copy of the database.
2. Copy the file to the location where the damaged or missing database should be replaced. 
3. Replace the existing file, when prompted to do so. 

Precautions to keep in mind during the  Backup and Restore MS Access Database Process

1. After the backup MS Access Database process is complete, cross-check if your database file is properly backed up or not.
2. If the backup copy contains sensitive information, it is better to keep it encrypted. 
3. Make a copy of the backup database items in some off-site location on a regular basis. 

Conclusion 

Maintaining a proper system to backup and restore the MS Access database goes a long way in ensuring the efficiency and growth of a business as data is indispensable for the functioning of any business. It is important to regularly update backup copies of the database so that they are readily available when the need arises. To automate the process of creating backups, consider using products that perform automated backups of file systems, such as file server backup software or a USB backup external device.

Related Post