DUMP Statement (version 6.5)

Allows an individual table to be backed up.

For additional syntax information for the DUMP statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

Dumping a table:

DUMP TABLE [[database.]owner.]table_name
    TO dump_device [, dump_device2 [..., dump_device32]]
[WITH options]

where

dump_device =

{dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
{'temp_dump_device' | @temp_dump_device_var}
[VOLUME = {volid | @volid_var}]

options =

[[, ] {UNLOAD | NOUNLOAD}]
[[, ] {INIT | NOINIT}]
[[, ] {SKIP | NOSKIP}]
[[, ] {EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}}]
[[, ] STATS [= percentage]]

TABLE

Specifies that a table is to be backed up.

database

Specifies the database that contains the table being dumped.

owner

Specifies the owner of the table to dump.

table_name

Specifies the table to dump.

STATS = percentage

Specifies the percentage of pages backed up in increments specified at the time of the dump. If the percentage is not specified, statistics are shown for each 10 percent of the dump. This option is supported for DUMP DATABASE and DUMP TABLE. If you do not specify the STATS option then no statistics are shown.

Remarks

This statement backs up a single database table. By backing up specific tables, you can conserve system resources, save time by backing up only tables that have changed, and target high-volume tables for regular backups.

When you anticipate loading individual tables, use DUMP TABLE. DUMP TABLE locks the table and provides an exact snapshot of the table data. When tables have FOREIGN KEY constraints, you can cause the database to be logically inconsistent if conflicting transactions have occurred since the time of the backup.

Important If data manipulation transactions were occurring on the table when the dump was performed, the data in the table may be logically inconsistent because outstanding transactions would not be reapplied.

Load a full table from a database dump only in disaster recovery situations.

A striped dump is created when more than one dump device is specified. Dumping to multiple devices can improve the performance of the dump because pages can be written to multiple dump devices at the same time. For DISK and FLOPPY devices, you must load from the same number of devices as you dumped to . For TAPE and PIPE devices, you can load from fewer devices than were dumped to. For example, if a database is dumped to four tape drives, you can load from one tape drive. The tapes can be loaded in any order, and a message will prompt you to change tapes.

For compatibility, database dumps from SQL Server 6.0 can be loaded into SQL Server 6.5 when the same sort order is running on both SQL Servers. The dump must be a full database dump and must occur when there is no update activity in the database. For this reason, it is recommended that the dump occur when SQL Server is started in single-user or read-only mode. After the database is successfully loaded, the database is upgraded.

The trace flag 3002 causes a SQL Server 6.5 to dump databases that can be loaded into SQL Server 6.0, Service Pack 3. It is recommended that the SQL Server 6.5 database be started in single-user or read-only mode before making the dump. A SQL Server 6.5 master database cannot be restored to a SQL Server 6.0 server.

A disk backup uses WITH INIT. A tape backup uses WITH NOSKIP and WITH NOUNLOAD, but not WITH INIT.

For more information about the DUMP statement, see the Microsoft SQL Server Transact-SQL Reference.

Example

This example creates the disk_dev1 dump device and then dumps the employees table to the disk_dev1 disk.

EXECUTE sp_addumpdevice 'disk','disk_dev1','c:\MSsql\dumps\diskdev1.dmp'
DUMP TABLE employees TO disk_dev1 WITH STATS
  

Permission

SELECT permission is required to dump a table.