Back up a database

You should regularly back up any active database to guard against data loss and to protect your investment in your database design. Using a backup, you can easily restore an entire database or selected database objects.

If the number of records in your database grows regularly, you can also consider archiving the old data. Archiving is the process by which you periodically move older records from a table in an active database to a table in an archive database. This article does not explain how to archive old data.

If you want to automate creating backups of database files, consider using a product that performs automated backups of a file system, such as file server backup software or a USB external backup device. This article does not discuss ways to automate the creation of Microsoft Office Access backups.

If you already have a database backup and you want to restore data or objects, see the article Restore a database.

In this article


Overview

Backups help protect a database from system failures and help protect against mistakes that the Undo command cannot fix.

In Microsoft Office Access 2007, you can use the Undo command up to 20 times to reverse changes that you made since the last time that you saved your database file.

However, 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, you cannot undo those changes by using the Undo command. For example, if you run an update query, any old values that were updated by the query cannot be restored by using Undo.

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

Not all changes in a database can be undone

Although Access offers you the option to reverse changes that you decide you did not want to make, the process is not foolproof. To prepare for recovery from unwanted changes that you cannot undo, you should make backups of your database.

Regular backups are essential

To decide how often to make backups, consider how often your database changes:

 Note   You should back up the data in linked tables by using any backup features that are available 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 Back up a split database to back up that database.

Top of Page Top of Page

Back up a database

When you back up a database, Access saves and closes any objects that are open in Design view, compacts and repairs the database, and then saves a copy of the database file by using a name and location that you can specify. Access then reopens any objects that it closed.

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

  1. Open the database that you want to back up.
  2. Click the Microsoft Office Button Button image, point to Manage, and then, under Manage this database, click Back Up Database.
  1. 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 made. Therefore, it is usually a good idea to use the default file name.

  1. In the Save As dialog box, select a location in which to save your database backup, and then click Save.

Top of Page Top of Page

Back up 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, in addition to 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.

When you back up a split database, you back up the front-end and back-end databases independently of each other. It is more important to make regular backups of the back-end database, because it contains your data. You can make a backup of the front-end database whenever you change its design. However, because each user has an individual copy of the front-end database and can make arbitrary design changes, you should also consider requiring those users to make their own backups of the front-end database.

Back up the back-end database

Because running the backup requires exclusive access to the database file, users may be momentarily unable to use the back-end database. You should consider notifying users before you start the backup process.

  1. Open the back-end database in exclusive mode.

ShowHow do I open the database in exclusive mode?

  1. Start Access, but do not open a database.
  2. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select the back-end database file. You can use the Look in list if you need to browse to find the database file.
  2. Click the arrow on the Open button, and then click Open Exclusive.

  1. Click the Microsoft Office Button Button image, point to Manage, and then, under Manage this database, click Back Up Database.
  1. In the Save As dialog box, in the File Name box, review the default name for your database backup. You can change the name if you want, but note 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 need to know which database the backup came from and when the backup was made, so using the default file name is usually a good idea.

  1. In the Save As dialog box, select a location in which to save your database backup, and then click Save.

Back up the front-end database

To protect your investment in the design of your front-end database, you should back up the front-end database whenever you change its design. If you allow users to customize the design of their own copies of the front-end database, you should consider requiring them to back up their own customized copies.

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

After you finish the backup operation for the front-end database, you can then make the new design available to users.

Top of Page Top of Page

 
 
Applies to:
Access 2007