Table Backup and Restore

With SQL Server 6.5, you can back up and restore individual tables. In SQL Enterprise Manager, a table restore can only be performed from a table backup or from a full database backup.

Restoring a table is useful in a disaster recovery situation where restoring the entire database may be a poor solution.

For example, suppose one table is corrupt and has no foreign keys and no columns that are based on other tables. Restoring only this table would correct your database with the least amount of work.

Full database and transaction log backups remain the basis of a proper backup strategy.

Important Only a full database or transaction log backup ensures that all PRIMARY KEY to FOREIGN KEY constraints are correct.

Table backup and restore operations have the following characteristics:

Table restores can be performed from full database backups or from individual table backups.
Table restores cannot be performed for tables that have text or image columns or that are published for replication.
Triggers, rules, defaults, and declarative referential integrity (DRI) are not enforced for table restores.
Source and target table schema must be identical in their nullability, length, and base-system type. User-defined types can be different, but the base types must be the same.
Table restores can be performed only when the Select Into/Bulk Copy database option is set.
A table restore disables the DUMP TRANSACTION statement until a full database backup is performed.

To back up a table

From SQL Enterprise Manager, in the Server Manager window choose a server.
From the Tools menu, choose Database Backup/Restore.
The Database Backup/Restore dialog box appears.

In the Backup tab, select the database to back up.
Select Table, expand the list, and select a table to back up.
Under Options, select backup media options.



Backup media option	Description
Initialize Device	Writes over the existing data and initializes the device.
No expiration date	Specifies that the backup has no expiration date and can be overwritten at any time.
Expires after	Specifies that the backup will expire after a set elapsed number of days. If you select this option, then you must also enter the number of days in the adjacent box.
Expires on	Specifies that the backup will expire on a specific date. If you select this option, then you must also enter the number of days in the adjacent box.
Eject Tape at End of Backup	Specifies that the tape is automatically rewound and unloaded when the backup completes. If you will be performing multiple backups to a single tape, clear this option. If you will be performing a single backup or the last backup of several to a tape, select this option.
Skip Tape Header	Specifies that the server should not read the ANSI header on a tape.

Under Backup Devices, select a backup device. You can back up to one or more devices. By choosing the buttons at the bottom of the Backup Devices list box, you can add a device, drop a device, or view header information about a device.



Backup device option	Description
New	Adds a backup device or specifies a file.
Delete	Removes a backup device.
Info	Displays information for a selected backup device.

Schedule the backup.



Schedule option	Description
Backup Now	Performs the backup immediately.
Schedule	Schedules the backup for immediate, one time, or recurring execution.

For more information about backup options, see the online Help for Backup Tab for Databases in SQL Enterprise Manager.

To restore a table

From SQL Enterprise Manager, in the Server Manager window choose a server.
From the Manage menu, choose Databases.
The Manage Databases dialog box appears.
Choose Edit Database and in the Edit Databases dialog box, choose the Options tab.
Verify that the Select Into/Bulk Copy database option is selected. For details, in the Options tab click Help.
From the Tools menu, choose Database Backup/Restore.
The Database Backup/Restore dialog box appears.

In the Restore tab, select a database to restore. The Backup History list will be populated with backups for the selected database. Also, the Single Table list will be populated with tables for the selected database.
Under Restore, select the Single Table option. A single table will be restored. A table restore can be performed from a table backup or from a full database backup. Selecting this option populates the list of tables and the Backup History list with available database and table backups. Select a table to restore.
Under Backup History from the Restore column, which displays backups that are available depending upon the Restore option and the database selected, select a backup.
Choose Restore Now.

For more information about backup options, see the online Help for Backup Tab for Databases in SQL Enterprise Manager.

Table backups and restores can also be accomplished using the Transact-SQL DUMP and LOAD statements. For more information, see What's New for Transact-SQL.