dump transaction

Description

Makes a copy of a transaction log, and removes the inactive portion of the log, if the dump transaction command is not running concurrently with another dump database.

See the Tivoli Storage Manager (TSM) syntax for dump tranaction syntax when Tivoli is licensed at your site.

Syntax

To make a routine log dump:

dump tran[saction] database_name 
	to [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]
	[[stripe on [compress::[compression_level::]]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level,
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}, 
		standby_access}]

To truncate the log without making a backup copy:

dump tran[saction] database_name 
	with truncate_only

To truncate a log that is filled to capacity. Use only as a last resort, as you will lose the contents of your log:

dump tran[saction] database_name 
	with no_log 

To back up the log after a database device fails:

dump tran[saction] database_name 
	to [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::[compression_level::]]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]
	[[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		no_truncate, 
		notify = {client | operator_console}}]

To copy the transaction log when the Tivoli Storage Manager provides backup services:

dump transaction database_name 
	to "syb_tsm::object_name"
		[blocksize = number_bytes]
	[stripe on "[syb_tsm::]object_name"
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		compression = compress_level,
		passwd = password,
		[noinit | init],
		notify = {client | operator_console},
		verify[ = header | full]
		} ]

To dump a transaction based on the settings specified in a configuration file:

dump transaction database_name
	using config = configuration_name
	[with {
		verify [= header | full]
		}] 

Parameters

Examples

Example 1

Dumps the transaction log to a tape, appending it to the files on the tape, since the init option is not specified:

dump transaction pubs2 
    to "/dev/nrmt0"

Example 2

Dumps the transaction log for the mydb database, using the Backup Server REMOTE_BKP_SERVER. The Backup Server dumps approximately half the log to each of the two devices. The init option overwrites any existing files on the tape. The retaindays option specifies that the tapes cannot be overwritten for 14 days:

dump transaction mydb
    to "/dev/nrmt4" at REMOTE_BKP_SERVER
    stripe on "/dev/nrmt5" at REMOTE_BKP_SERVER
with init, retaindays = 14

Example 3

Dumps completed transactions from the inventory_db transaction log file to device dev1:

dump tran inventory_db to dev1 with standby_access

Example 4

Dumps the transaction log for the pubs2 database to the TSM backup object "demo2.2" with 100-level compression.

dump transaction pubs2 to "syb_tsm::demo2.2" 
    with compression = 100

Example 5

Dumps the database using the “dmp_cfg2” configuration. The archive files created during the dump are password-protected:

dump transaction testdb using config = 'dmp_cfg2'
   with passwd = 'my_pass01'
go

Example 6

Dumps the database using the “dmp_cfg2” configuration using compression level 6 and (that is, overriding the compression level specified in “dmp_cfg2)”:

dump transaction testdb using config = 'dmp_cfg2'
   with compression = 6
go

Usage

• The maximum file path/name size for a physical device is 127 characters.

• You cannot dump to the null device (on UNIX, /dev/null).

• You cannot use the dump transaction command in a transaction.

• When using 1/4-inch cartridge tape, you can dump only one database or transaction log per tape.

• You cannot run dump transaction database_name to before fully dumping a newly created database.

• You cannot use dump transaction database_name to once an unlogged operation has been performed in the database.

• You cannot issue dump the transaction log while the trunc log on chkpt database option is enabled or after enabling select into/bulk copy/pllsort and making minimally logged changes to the database with select into, fast bulk copy operations, default unlogged writetext operations, or a parallel sort. Use dump database instead.

  WARNING! Do not modify the log table syslogs with a delete, update, or insert command.

• If a database does not have a log segment on a separate device from data segments, you cannot use dump transaction to copy the log and truncate it.

• If a user or threshold procedure issues a dump transaction command on a database where a dump database or another dump transaction is in progress, the second command sleeps until the first completes.

• To restore a database, use load database to load the most recent database dump; then use load transaction to load each subsequent transaction log dump in the order in which it was made.

• Each time you add or remove a cross-database constraint, or drop a table that contains a cross-database constraint, dump both of the affected databases.

  WARNING! Loading earlier dumps of these databases can cause database corruption.

• You cannot mix Sybase dumps and non-Sybase data (for example, UNIX archives) on the same tape.

• You cannot dump a transaction with no_log or with truncate_only if the database has offline pages.

Under normal circumstances, Adaptive Server returns an error message when:

• After device failure, use dump transaction with no_truncate to copy the log without truncating it. You can use this option only if your log is on a separate segment and your master database is accessible.

• The backup created by dump transaction with no_truncate is the most recent dump for your log. When restoring the database, load this dump last.

• When a database does not have a log segment on a separate device from data segments, use dump transaction with truncate_only to remove committed transactions from the log without making a backup copy.

  WARNING! dump transaction with truncate_only provides no means to recover your databases. Run dump database at the earliest opportunity to ensure recoverability.

• Use with truncate_only on the master, model, and sybsystemprocs databases, which do not have log segments on a separate device from data segments.

• You can also use with truncate_only on very small databases that store the transaction log and data on the same device.

• Mission-critical user databases should have log segments on a separate device from data segments. Use the log on clause of create database to create a database with a separate log segment, or alter database and sp_logdevice to transfer the log to a separate device.

• Use the with standby_access option to dump transaction logs for loading into a server that acts as a warm standby server for the database.

• When you use with standby_access to dump the transaction log, the dump proceeds to the furthest point in the log at which all earlier transactions have completed and there are no records belonging to open transactions.

• You must use dump tran[saction]...with standby_access in all situations where you load two or more transaction logs in sequence and you want the database to be online between loads.

• After loading a dump made with the with standby_access option, use the online database command with the for standby_access option to make the database accessible.

  WARNING! If a transaction log contains open transactions and you dump it without the with standby_access option, Adaptive Server does not allow you to load the log, bring the database online, then load a subsequent transaction dump. If you are going to load a series of transaction dumps, you can bring the database online only after a load that was originally dumped with standby_access or after loading the entire series.

• dump transaction...with no_log truncates the log without logging the dump transaction event. Because it copies no data, it requires only the name of the database.

• Every use of dump transaction...with no_log is considered an error and is recorded in the Adaptive Server error log.

• If you have created your databases with log segments on a separate device from data segments, written a last-chance threshold procedure that dumps your transaction log often enough, and allocated enough space to your log and database, you should not have to use with no_log. If you must use with no_log, increase the frequency of your dumps and the amount of log space.

• Transaction log dumps are dynamic—they can take place while the database is active. They may slow the system slightly, so run dumps when the database is not being heavily updated.

• Develop a regular schedule for backing up user databases and their transaction logs.

• dump transaction uses less storage space and takes less time than dump database. Typically, transaction log dumps are made more frequently than database dumps.

• Use thresholds to automate backup procedures. To take advantage of the Adaptive Server last-chance threshold, create user databases with log segments on a separate device from data segments.

• When space on the log segment falls below the last-chance threshold, Adaptive Server executes the last-chance threshold procedure. Including a dump transaction command in your last-chance threshold procedure helps protect you from running out of log space. See sp_thresholdaction.

• You can use sp_addthreshold to add a second threshold to monitor log space. For more information about thresholds, see the System Administration Guide.

• You can specify the dump device as a literal, a local variable, or a parameter to a stored procedure.

• You can specify a local dump device as:

  • A logical device name from the sysdevices system table

  • An absolute path name

  • A relative path name

  The Backup Server resolves relative path names using the current working directory in Adaptive Server.

• Dumping to multiple stripes is supported for tape and disk devices. Placing multiple dumps on a device is supported only for tape devices.

• When dumping across the network, specify the absolute path name of the dump device. The path name must be valid on the machine on which the Backup Server is running. If the name includes any characters except letters, numbers, or underscores (_), enclose it in quotes.

• Ownership and permissions problems on the dump device may interfere with use of dump commands. sp_addumpdevice adds the device to the system tables, but does not guarantee that you can dump to that device or create a file as a dump device.

• You can run more than one dump (or load) at the same time, as long as they use different dump devices.

If you issue a dump transaction command without the init qualifier and Backup Server cannot determine the device type, the dump transaction command fails. See the System Administration Guide.

• You must have a Backup Server running on the same machine as your Adaptive Server. The Backup Server must be listed in the master..sysservers table. This entry is created during installation or upgrade and should not be deleted.

• If your backup devices are located on another machine so that you dump across a network, you must also have a Backup Server installed on the remote machine.

• Dump volumes are labeled according to the ANSI tape-labeling standard. The label includes the logical volume number and the position of the device within the stripe set.

• During loads, Backup Server uses the tape label to verify that volumes are mounted in the correct order. This allows you to load from a smaller number of devices than you used at dump time.

  When dumping and loading across the network, you must specify the same number of stripe devices for each operation.

• (On UNIX systems) the Backup Server requests a volume change when the tape capacity has been reached. After mounting another volume, the operator notifies the Backup Server by executing the sp_volchanged system procedure on any Adaptive Server that can communicate with the Backup Server.

• If the Backup Server detects a problem with the currently mounted volume (for example, if the wrong volume is mounted), it requests a volume change by sending messages to either the client or its operator console. The operator responds to these messages with the sp_volchanged system procedure.

• At the beginning of a dump transaction, Adaptive Server passes the primary device name of each logical log device to the Backup Server. If the primary device has been unmirrored, Adaptive Server passes the name of the secondary device instead. If the named device fails before Backup Server completes its data transfer, Adaptive Server aborts the dump.

• If you attempt to unmirror a named log device while a dump transaction is in progress, Adaptive Server displays a message. The user executing the disk unmirror command can abort the dump or defer the disk unmirror until after the dump completes.

• dump transaction with truncate_only and dump transaction with no_log do not use the Backup Server. These commands are not affected when a log device is unmirrored, either by a device failure or by a disk unmirror command.

• dump transaction copies only the log segment. It is not affected when a data-only device is unmirrored, either by a device failure or by a disk unmirror command.

In versions of Adaptive Server earlier than 15.7, some operations are minimally logged. Since dump transaction is not allowed after a minimally logged operation, this restriction impacts:

• Recoverability and operational scalability for very large database (VLDB) installations, where dump database may be very time consuming.

• Up-to-the-minute recoverability of the database. Even though minimally logged operations are fully recoverable from a server failure, changes after the last successful transaction dump may be lost when data devices are broken, or if the database is corrupted. You cannot, after a minimally logged operation, use dump tran with no_truncate to dump the log, then recover the database using the dumped transaction log.

• You cannot restore the database to a particular time using dump transaction, and then load tran with until_time.

Beginning with Adaptive Server 15.7, you can use dump transaction to fully recover the following operations that in earlier versions of Adaptive Server were minimally logged:

• select into including select into a proxy table

• alter table commands that require data movement

• reorg rebuild

Use sp_dboption in in the master database to fully log commands that are, by default, minimally logged.

See user documentation for Tivoli Storage Manager for more information about creating back-ups when TSM is supported at your site.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for dump transaction differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also