DUMP Statement

For SQL Server 6.5 information, see DUMP Statement in What's New for SQL Server 6.5.

Makes a backup copy of a database and its transaction log (DUMP DATABASE) or makes a copy of only the transaction log (DUMP TRANSACTION) in a form that can be read into SQL Server using the LOAD statement.

Syntax

Dumping a database:

    DUMP DATABASE {dbname | @dbname_var}
        TO dump_device [, dump_device2 [..., dump_device32]]
    [WITH options
        [[,] STATS [ = percentage]]]

Dumping a transaction log:

    DUMP TRANSACTION {dbname | @dbname_var}
        [TO dump_device [, dump_device2 [..., dump_device32]]]
    [WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE}
        {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}}]

Note The UNLOAD and NOUNLOAD options are supported only by TAPE dump devices.

When you specify a PIPE dump device, multivolume dumps and dump device options (UNLOAD, NOUNLOAD, INIT, NOINIT, SKIP, NOSKIP, EXPIREDATE, and RETAINDAYS) are not supported.

DATABASE

Specifies that the complete database and transaction log are to be backed up.

If the transaction log is on the same device as the database, using DUMP DATABASE is the only way you can create a backup copy of both the database and its transaction log. However, because DUMP DATABASE does not remove the inactive portion of the log, you must use DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log of completed transactions. Generally, databases and transaction logs should be stored on different devices; this improves performance and enables unrestricted use of both the DUMP DATABASE and DUMP TRANSACTION statements.

TRANSACTION

Specifies that only the transaction log is to be backed up. DUMP TRANSACTION removes the inactive part of the transaction log and makes a backup copy of it in a form that can be read in with the LOAD statement. To retain a backup of only the transaction log (using DUMP TRANSACTION without the TRUNCATE_ONLY or NO_LOG options), the transaction log must be stored on a separate database device from the data portion of the database.

Note If dumping the transaction log doesn't appear to truncate the majority of your transaction log, you may have an open transaction in the log. To determine whether a transaction has been left open, use DBCC OPENTRAN.

For databases that store the data portion of the database on a separate device from the log portion, log space can be monitored with various DBCC commands and the Performance Monitor. For details, see the DBCC statement.

TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it. This optional clause frees disk space used by the transaction log. Specifying a dump_device is unnecessary because the transaction log dump will not be saved.

Prior to backing up a database, it is useful to dump a transaction log using the TRUNCATE_ONLY option to clear out the log and decrease the total time required to back up the database.

Note After dumping a transaction log using the TRUNCATE_ONLY option, you should immediately make a backup of the database using DUMP DATABASE. Dumping of the transaction log (using DUMP TRANSACTION) will not be allowed until a full database dump (using DUMP DATABASE) has been performed.

NO_LOG

Is used only when you have run out of space in the database and cannot use DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log. The NO_LOG option removes the inactive part of the log without making a backup copy of it, and it saves space by not logging the operation.

If the transaction log of a published (replicated) database becomes full and absolutely must be truncated, unsubscribe all subscriptions to the publications of that database. This allows you to truncate past the oldest distributed transaction.

After the transaction log has been dumped using NO_LOG, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute the DUMP DATABASE statement.

Because both options clear the log without making a backup of it, it is unnecessary to use both the TRUNCATE_ONLY and NO_LOG option in the same statement.

NO_TRUNCATE

Makes it possible to dump a transaction log even if the database is inaccessible by using a pointer to the transaction log in the master database.

The NO_TRUNCATE option provides up-to-the-minute media recovery when the master database and the log portion of the user database reside on undamaged database devices and when only the data portion of the user database is damaged.

dbname | @dbname_var

Specifies the database from which the transaction log or complete database is being dumped. If supplied as a variable (@dbname_var), this name can be specified as a string, or as a char or varchar variable.

dump_device_name | @dump_device_namevar

Specifies the logical name of the dump device or devices (as created by the sp_addumpdevice system stored procedure) to which the database will be backed up. If supplied as a variable (@dump_device_namevar), the dump device name can be specified as a string or as a char or varchar variable.

DISK | TAPE | FLOPPY | PIPE =

'temp_dump_device' | @temp_dump_device_var

Allows backups directly to temporary dump devices. These devices are created (or accessed, if they already exist) when the dump occurs; they do not have entries in the sysdevices system table. With DISK, TAPE, and FLOPPY specify the complete path and filename of the device. With PIPE, specify the name of the named pipe that will be used by the client application. If supplied as a variable (@temp_dump_device_var), the temporary dump device name can be specified as a string or as a char or varchar variable. If you are using a network server with a UNC name or using a redirected drive letter, specify a device type of DISK.

Important When specifying a striped dump, logical device names (or variables) and temporary device names (or variables) can be mixed. PIPE devices have been added to allow third-party vendors a flexible and powerful way to connect their own software. For typical Transact-SQL use, the PIPE device will not be used.

VOLUME = volid | @volid_var

Specifies the volume ID, volid, a 6-byte character string. If dumping to a new tape, this value is the name of the ANSI VOL1 label. For SQL Server, the default is SQ0001. For subsequent dumps, this value is used to validate the volume name of the dump device. If supplied as a variable (@volid_var), the volume name can be specified as a string or as a char or varchar variable. It is not necessary, or recommended, to explicitly specify the volume ID.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the dump is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.

NOUNLOAD

Specifies that the tape will not be unloaded automatically from the tape drive after a dump. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

INIT

Specifies that the dump should be the first file on the DISK or TAPE device. If INIT is specified, any existing data on that device will be overwritten. If the first file on the device has an ANSI-standard label, the label will be evaluated to determine whether the previous dump(s) can be overwritten. For details, see the EXPIREDATE and RETAINDAYS options, later in this section.

NOINIT

Specifies that the dump should be appended to the current DISK or TAPE device. This is the default for both single dump devices and striped dumps. Dump devices used in a stripe set must always be used in a stripe set (unless reinitialized at some point with INIT). Once a device is defined as part of a stripe set, it cannot be used for a single device dump unless INIT is specified. Similarly, a device that contains nonstriped dumps cannot be used in a stripe set unless INIT is specified.

SKIP

Instructs the server not to read the ANSI TAPE device headers.

NOSKIP

Specifies that the server will read ANSI TAPE device headers. This is the default.

Note When specified, SKIP and NOSKIP override the setting as defined with the dump device (when the sp_addumpdevice system stored procedure was executed).

EXPIREDATE = date | @date_var

Specifies the date when the dump media expires and can be overwritten. If supplied as a variable (@date_var), this date can be specified as a string or as a char or varchar variable, and it must follow the configured datetime format for the system. This option is used for DISK and TAPE devices only and is effective only when specified with INIT.

RETAINDAYS = days | @days_var

Specifies the number of days that must elapse between the existing dump and when the dump media can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer. This option is used for DISK and TAPE devices only and is effective only when specified with INIT.

Important EXPIREDATE and RETAINDAYS override the configuration value for media retention when specified with the DUMP DATABASE or DUMP TRANSACTION statements. These options are used with DISK and TAPE devices only. If an expiration date is needed for diskette devices, configure media retention with the sp_configure system stored procedure.

If EXPIREDATE or RETAINDAYS is specified, subsequent dumps can be appended to that device; however, EXPIREDATE and RETAINDAYS are valid only for dumps specified with INIT.

These options only prevent SQL Server from overwriting a device. Tapes can still be erased using other methods, and disk-device files can still be deleted through the operating system.

STATS = percentage

Returns the percentage of pages dumped in increments optionally set at the time of the dump. If the percentage is not specified, statistics will be shown for each 10 percent of the dump. This option is available only with DUMP DATABASE.

Remarks

In earlier releases of SQL Server, dumping a database or a transaction log to a single device always overwrote the previous dump unless (for tape devices only) NOINIT was specified. With SQL Server 6.0, database or transaction log dumps can be appended to any DISK or TAPE device, allowing you to keep a database and its transaction logs all within one physical location.

The SQL Server dump is a dynamic dump ¾ it can take place while the database is active. The dump captures the state of the data as it is at the moment the statement is executed; no partial transactions are reflected. Any data changes made after the dump begins are not reflected in the dumped database. Performing a dump can slow the system down somewhat, so you may want to run it when the database or table is not being heavily updated.

Ownership and permissions problems on the physical file can interfere with a DUMP statement. The sp_addumpdevice system stored procedure adds the device to the system tables, but it does not check file access permission.

You cannot execute the DUMP TRANSACTION statement while the trunc. log on chkpt. database option is enabled. Nor can you execute it after enabling select into/bulkcopy and making unlogged changes to the database with SELECT INTO or a bulk-copy operation. Use DUMP DATABASE instead.