Backing Up and Restoring Databases

Unlike Jet databases, which you back up by a manual copying process, ADP have a built-in snapshot copy process. The best feature of the ADP backup process is that you don't need exclusive access to the database; you can generate a backup while you and other users are connected to the database.

Tip

Use SQL Server Enterprise Manager to back up large databases to tape manually or automatically. Access's backup and restore features are limited to creating conventional disk files on fixed or removable media. If you intend to back up to a CD-R or CD-RW drive, make a fixed-disk copy and then burn the CD-ROM from the disk copy.

To create a snapshot backup of your project's current database (NorthwindCS for this example), do the following:

1. Choose Tools, Database Utilities, Backup SQL Database to open the Backup dialog.

2. Accept or change the proposed name of the backup file. By default, SQL Server backup files use the name of the database followed by a sequential number and a .dat extension NorthwindCS1.dat for this example (see Figure 20.34).

   Figure 20.34. Use the backup feature to quickly create live snapshot backups of your project's database.

   

3. Navigate to a backup folder, preferably on a network server or a second local physical drive. When you click OK, you receive the message shown in Figure 20.35.

   Figure 20.35. This message confirms creation of the snapshot backup and suggests backing up your .adp file by making a conventional copy.

   

You restore a backup .dat file when, for instance, you accidentally delete a table or other database object. You can't restore a database while you or users are connected to it.

To perform a restore, do the following:

1. Choose Tools, Database Utilities, Restore SQL Database. You receive the message shown in Figure 20.36.

   Figure 20.36. This message indicates that you can't perform a live restore of a database. All connections to the database must be closed before proceeding.

   

2. Click Yes to dismiss the message, navigate to the backup file, select it, and click OK. Your project disconnects from the database, and the message box shown in Figure 20.37 appears after a delay that depends on the size of the database and network performance.

   Figure 20.37. Restoring a database from a .dat backup copy requires temporarily disconnecting the project from the database.

   

3. Click OK to acknowledge the message, and automatically reconnect to the restored database.

Tip

If you receive the message shown in Figure 20.38, other users have open connections to the database or there's a spurious lock on the database. If no other users are connected, you might be able to remove the lock by stopping and starting SQL Server with SQL Server Service Manager. In most cases, however, you must reboot the machine for the restore operation to succeed.

Figure 20.38. This message indicates that there are open connections or a residual lock on the database.

If you need to restore the .adp and .dat files as the result of a catastrophic failure, such as a crashed fixed disk, do this:

1. Copy the .adp file from the backup and open it in the disconnected state.

2. Restored the database, as described in the preceding three steps. Your project remains disconnected from the database.

3. Choose File, Connection to open the Data Link Properties dialog.

4. Accept the default (local) server name, and select the restored database in the Select the Database on the Server list (see Figure 20.39).

   Figure 20.39. If you restore the .adp and .dat files, you must reconnect manually to the project's database.

   

5. Click Test Connection to verify your settings, and click OK to reconnect your project to the restored database.