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

database_name

is the name of the database from which you are copying data. The name can be given as a literal, a local variable, or a parameter to a stored procedure.

configuration_name

is a unique dump configuration name. The parameter values specified in the dump configuration are used to perform the dump operation.

Any other parameters are explicitly specified in the command override the values specified by the dump configuration.

You cannot specify a stripe directory as a parameter for the command if you use a dump configuration. Adaptive Server creates the dump files in the stripe directory specified by the dump configuration. Dump file names use this convention:

Database_Name.Dump_Type.Date-Timestamp.StripeID

compress::compression_level

is a number between 0 and 9, 100, or 101. For single-digit compression levels, 0 indicates no compression, and 9 provides the highest level of compression. Compression levels of 100 and 101 provide a faster, more efficient compression mode, with 100 providing faster compression and 101 providing better compression. If you do not specify compression_level, Adaptive Server does not compress the dump.

For more information about the compress option, see “Backing Up and Restoring User Databases” in the System Administration Guide.

NoteThe compression = compress_level option allows you to compress a dump file on both local and remote machines, and differs from the compress::compression_level option, which you can use only to compress a dump file on local machine. Beginning with Adaptive Server version 15.0, Sybase supports—and recommends—the native compression = compression_level syntax.

truncate_only

removes the inactive part of the log without making a backup copy. Use on databases without log segments on a separate device from data segments. Do not specify a dump device or Backup Server name.

no_log

removes the inactive part of the log without making a backup copy and without recording the procedure in the transaction log. Use no_log only when you are completely out of log space and cannot run the usual dump transaction command. Use no_log as a last resort and use it only once after dump transaction with truncate_only fails.

to stripe_device

is the device to which data is being dumped. See “Specifying dump devices” for information about what form to use when specifying a dump device.

at backup_server_name

is the name of the Backup Server. Do not specify this parameter if you are dumping to the default Backup Server. Specify this parameter only if you are dumping over the network to a remote Backup Server. You can specify as many as 32 different remote Backup Servers using this option. When dumping across the network, specify the network name of a remote Backup Server running on the machine to which the dump device is attached. For platforms that use interfaces files, backup_server_name must appear in the interfaces file.

density = density_value

overrides the default density for a tape device. Valid densities are 800, 1600, 6250, 6666, 10000, and 38000. Not all values are valid for every tape drive; use the correct density for your tape drive.

blocksize = number_bytes

overrides the default block size for a dump device. The block size must be at least one database page (2048 bytes for most systems) and must be an exact multiple of the database page size.

NoteWhenever possible, use the default block size; it is the best block size for your system.

capacity = number_kilobytes

is the maximum amount of data that the device can write to a single tape volume. The capacity must be at least five database pages, and should be slightly less than the recommended capacity for your device.

A general rule for calculating capacity is to use 70 percent of the manufacturer’s maximum capacity for the device, leaving 30 percent for overhead, such as record gaps and tape marks. This rule works in most cases, but may not work in all cases because of differences in overhead across vendors and devices.

On UNIX platforms that cannot reliably detect the end-of-tape marker, you must indicate how many kilobytes can be dumped to the tape. You must supply a capacity for dump devices specified as a physical path name. If a dump device is specified as a logical device name, the Backup Server uses the size parameter stored in the sysdevices system table, unless you specify a capacity.

compression = compress_level

is a number between 0 and 9, 100, or 101. For single-digit compression levels, 0 indicates no compression, and 9 provides the highest level of compression. Compression levels of 100 and 101 provide a faster, more efficient compression mode, with 100 providing faster compression and 101 providing better compression. If you do not specify compression_level, Adaptive Server does not compress the dump.

NoteSybase recommends the native "compression = compress_level" option as preferred over the older "compress::compression_level" option. The native option allows compression of both local and remote dumps, and the dumps that it creates will describe their own compression level during a load. The older option is retained for compatibility with older applications.

dumpvolume = volume_name

establishes the name that is assigned to the volume. The maximum length of volume_name is 6 characters. The Backup Server writes the volume_name in the ANSI tape label when overwriting an existing dump, dumping to a brand new tape, or dumping to a tape for which the contents are not recognizable. The load transaction command checks the label and generates an error message if the wrong volume is loaded.

stripe on stripe_device

is an additional dump device. You can use up to 32 devices, including the device named in the to stripe_device clause. The Backup Server splits the log into approximately equal portions and sends each portion to a different device. Dumps are made concurrently on all devices, reducing the time and the number of volume changes required. See “Specifying dump devices”.

dismount | nodismount

(on platforms that support logical dismount) determines whether tapes remain mounted. By default, all tapes used for a dump are dismounted when the dump completes. Use nodismount to keep tapes available for additional dumps or loads.

nounload | unload

determines whether tapes rewind after the dump completes. By default, tapes do not rewind, allowing you to make additional dumps to the same tape volume. Specify unload for the last dump file to be added to a multidump volume. This rewinds and unloads the tape when the dump completes.

retaindays = number_days

(on UNIX platforms) specifies the number of days that Backup Server protects you from overwriting a dump. If you try to overwrite a dump before it expires, Backup Server requests confirmation before overwriting the unexpired volume.

NoteThis option is meaningful for disk, 1/4-inch cartridge, and single-file media. On multifile media, this option is meaningful for all volumes except the first.

The number_days must be a positive integer or 0, for dumps you can overwrite immediately. If you do not specify a retaindays value, Backup Server uses the server-wide tape retention in days value, set by sp_configure.

noinit | init

determines whether to append the dump to existing dump files or reinitialize (overwrite) the tape volume. By default, Adaptive Server appends dumps following the last end-of-tape mark, allowing you to dump additional databases to the same volume. You can append new dumps only to the last volume of a multivolume dump. Use init for the first database you dump to a tape, to overwrite its contents.

Use init when you want Backup Server to store or update tape device characteristics in the tape configuration file. See the System Administration Guide.

file = file_name

is the name of the dump file. The name cannot exceed 17 characters and must conform to operating system conventions for file names. If you do not specify a file name, Backup Server creates a default file name. See “Dump files”.

no_truncate

dumps a transaction log, even if the disk containing the data segments for a database is inaccessible, using a pointer to the transaction log in the master database. The with no_truncate option provides up-to-the-minute log recovery when the transaction log resides on an undamaged device, and the master database and user databases reside on different physical devices.

If you use dump tran with no_truncate you must follow it with dump database, not with another dump tran. If you load a dump generated using the no_truncate option, Adaptive Server prevents you from loading any subsequent dump.

notify = {client | operator_console}

overrides the default message destination.

  • On operating systems that offer an operator terminal feature, volume change messages are always sent to the operator terminal on the machine on which the Backup Server is running. Use client to route other Backup Server messages to the terminal session that initiated the dump database.

  • On operating systems (such as UNIX) that do not offer an operator terminal feature, messages are sent to the client that initiated the dump database. Use operator_console to route messages to the terminal on which the Backup Server is running.

with standby_access

specifies that only completed transactions are to be dumped. The dump continues to the furthest point it can find at which a transaction has just completed and there are no other active transactions.

syb_tsm

is the keyword that invokes the libsyb_tsm.so module that enables communication between Backup Server and TSM.

object_name

is the name of the backup object on TSM server.

configuration_name

is a unique dump configuration name. The parameter values specified in the dump configuration are used to perform the dump operation.

If other parameters are explicitly specified in the command, they override the parameter values specified by the dump configuration.

You cannot specify a stripe directory as a parameter for the command if you use a dump configuration. Adaptive Server creates the dump files in the stripe directory specified by the dump configuration. The dump files are named using this convention:

Database Name.Dump Type.Date-Timestamp.StripeID

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


Restrictions


Restrictions on using the with no_truncate option

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

When you use the with no_truncate option in your dump transaction database_name to dump_file command, however, Adaptive Server does not perform a check of the database and thus does not return any of these error messages. Adaptive Server assumes that your database has some lost data (for example, from a failed disk) and is therefore inaccessible.

You do, however, get an error message when you then try to load your transaction. Your load transaction process may fail, with this error message:

Specified file 'dump device' is out of sequence. Current
timestamp is <X> while dump was from <Y>.

Copying the log after device failure


Dumping databases without separate log segments


Dumping only complete transactions


Dumping without the log

WARNING! Use dump transaction with no_log only as a last resort, after your usual method of dumping the transaction log (dump transaction or dump transaction with truncate_only) fails because of insufficient log space. dump transaction with no_log provides no means to recover your databases. Run dump database at the earliest opportunity to ensure recoverability.


Scheduling dumps


Using thresholds to automate dump transaction


Specifying dump devices


Determining tape device characteristics

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.


Backup servers


Dump files


Volume names


Changing dump volumes


Appending to or overwriting a volume


Dumping logs stored on mirrored devices


Fully recoverable DDL and dump transaction

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:

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:

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


dump transaction and the Tivoli Storage Manager

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.

Granular permissions enabled

With granular permissions enabled, you must be the database owner, or a user with dump database privilege or own database privilege on the database.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with any of these roles:

  • sa_role, or,

  • replication_role, or,

  • oper_role

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

35

dump

dump transaction

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Documents “Backing Up and Restoring User Databases” in the System Administration Guide.

Commands dump database, load database, load transaction, online database

System procedures sp_addumpdevice, sp_dboption, sp_dropdevice, sp_helpdevice, sp_hidetextsp_logdevice, sp_volchanged