Protect your data with backup and restore processes

You will need a backup copy of your database to either restore the whole database if there is a system failure or to restore an object when the Undo command merely isn't enough to fix a mistake.

If a backup copy of your database seems a wasted use of storage space, consider the time that you might save by avoiding data and design loss. Creating backups on a regular basis is important when you have several users updating your database. Without a backup copy, you cannot restore corrupted or missing objects or any changes to the database design.

In this article


Plan regular backups

Since some changes or mistakes cannot be reversed, you don't want to wait for data loss to occur before you realize that you should have created a backup copy of the database. For example, when you use an action query (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.) to delete records or to change data, any values that were updated by the query cannot be restored by using Undo.

 Tip   Consider making a backup before you run any action query, especially if the query will change or delete much data.

If your database has several users, before you perform a backup, you must ensure that all users close their databases so that all changes in the data is saved.

If you wonder about how often you must create backup copies of your database, it generally depends on how often your database has major changes. Here are some general guidelines to help you decide on the frequency:

  • If the database is an archive, or if it is used only for reference and rarely changes, it is sufficient to create backups only when the design or data is changed.
  • If the database is active and the data frequently changes, create a schedule to regularly backup the database.
  • If the database has multiple users, create a backup copy of the database whenever there is a design change.

 Note   For data in linked table (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.) create backups by using any available backup features in the program that contains the linked tables. If the database that contains the linked tables is an Access database, use the procedure in the section Backup a split database.

Top of Page Top of Page

Backup a database

When you backup a database, Access saves and closes objects that are open in Design view and saves a copy of the database file by using a name and location that you specify.

 Note   Access will reopen objects as specified by the value of the objects' Default View property.

  1. Open the database for which you want to create a backup copy.
  2. Click the File tab.
  1. Click Save & Publish.
  2. In the Save Database As area, under Advanced click Back Up Database.
  3. In the Save As dialog box, in the File Name box, review the name for your database backup.

You can change the name if you want, but the default name captures both the name of the original database file and the date that you make the backup.

 Tip   

When you restore data or objects from a backup, you want to know which database the backup came from and when the backup was created. Therefore, it is usually a good idea to use the default file name.
  1. Select which file type you would like the backup database to be saved as from the Save As type list, and then click Save.

Top of Page Top of Page

Backup a split database

A split database typically consists of two database files: a back-end database, which contains only data in tables, and a front-end database, which contains links to the tables in the back-end database, queries, forms, reports, and other database objects. All the data is stored in the back-end database. All the user interface objects, such as queries, forms, and reports, are kept in the front-end database.

Since you must back up the front-end and back-end databases independent of one another when you backup a split database, it can be time consuming to do this frequently. Since the back-end database contains the data, it is more important to make regular backups of the back-end database. Make a backup of the front-end database when you change its design. Since each user has an individual copy of the front-end database and can make arbitrary design changes, consider requiring the users to make their own backup copies of the front-end database.

Backup a back-end database

Notify users before you start the backup process because running the backup process requires exclusive access to the database file, and users may be unable to use the back-end database while the backup process is running.

  1. To open only the back-end database, start Access but do not open a database.
  2. Click Open, and then select the back-end database file that you want to backup.
  3. Click the arrow next to Open, and then click Open Exclusive.

option to exclusively open a database

  1. Click the File tab.
  1. Click Save & Publish.
  2. In the Save Database As area, under Advanced click Back Up Database.
  3. In the Save Database As dialog box, in the File Name box, review the name for your database backup.

Review the default name for your database backup. You can change the name if you want, but be aware that the default name captures both the name of the original database file and the date that you make the backup.

 Tip   

When you restore data or objects from a backup, you must know which database the backup came from and when the backup was made. Therefore, using the default file name is usually a good idea.
  1. In the Save As dialog box, select a location in which to save the backup copy of your back-end database, and then click Save.

Backup a front-end database

To backup a front-end database after a design change, leave the database open immediately after you change its design. Then follow the steps in the section Backup a database, starting at step 2.

Top of Page Top of Page

Restore a database

 Note   You can only restore a database if you have a backup copy of the database.

A backup is considered as a "known good copy" of a database file — a copy in which you are confident of its data integrity and design. You should use the Back Up Database command in Access to make backups, but you can use any known good copy to restore a database. For example, you can restore a database from a copy that is stored on a USB external backup device.

When you restore a whole database, you replace a database file that is damaged, has data problems, or is missing completely, with a backup copy of the database.

  1. Open Windows Explorer, and browse to the known good copy of the database.
  2. Copy the known good copy to the location where the damaged or missing database should be replaced. If you are prompted to replace an existing file, do so.

Top of Page Top of Page

Restore objects in a database

If you have to only restore one or more objects in a database, import the objects from the backup copy of the database into the database that contains (or is missing) the object that you want restored.

 Important   If other databases or programs have links to objects in the database that you are restoring, it is critical that you restore the database to the correct location. If you do not, links to the database objects will not work and will have to be updated.

  1. Open the database to which you want to restore an object.
  2. To restore a missing object, skip to step 3. To replace an object that contains bad or missing data or if the object has stopped working correctly, do the following:
    1. If you want to preserve the current object, to compare it with the restored version after you restore, rename the object before you restore it. For example, if you want to restore a damaged form named Checkout, you can rename the damaged form Checkout_bad.
    2. Delete the object that you want to replace.

 Note   Always be careful when you delete database objects as they may be linked to other objects in the database.

  1. On the External Data tab, in the Import & Link group, click Access.
  2. In the Get External Data-Access Database dialog box, click Browse to locate the backup database, and then click Open.
  3. Select Import tables, queries, forms, reports, macros, and modules into the current database, and then click OK.
  4. In the Import Objects dialog box, click the tab that corresponds to the type of object that you want to restore. For example, if you want to restore a table, click the Tables tab.
  5. Click the object to select it.
  6. If you want to restore more objects, repeat steps 6 and 7 until you have selected all of the objects that you want to restore.
  7. To review the import options before importing the objects, in the Import Objects dialog box, click the Options button.
  8. After you select the objects and import setting options, click OK to restore the objects.

To automate creating backups, consider using a product that performs automated backups of a file system, such as file server backup software or a USB external backup device.

Top of Page Top of Page

 
 
Applies to:
Access 2010