Makes a copy of a transaction log and removes the inactive portion.
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,
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:
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,
dumpvolume = volume_name,
file = file_name,
[dismount | nodismount],
[nounload | unload],
retaindays = number_days,
[noinit | init],
no_truncate,
notify = {client | operator_console}}]
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.
is a number between 0 and 9, with 0 indicating no compression, and 9 providing the highest level of compression. If you do not specify compression_level, the default is 1. For more information about the compress option, see Chapter 27, “Backing Up and Restoring User Databases” in the System Administration Guide.
The compress option works only with
local archives; you cannot use the backup_server_name option.
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.
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. For additional information, see the System Administration Guide.
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.
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 up to 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, the backup_server_name must appear in the interfaces file.
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.
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.
Whenever possible, use the default block size; it is
the best block size for your system.
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 inter-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.
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 whose contents are not recognizable. The load transaction command checks the label and generates an error message if the wrong volume is loaded.
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” for information about how to specify a dump device.
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.
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.
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.
This 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.
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. New dumps can be appended 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. For more information, see the System Administration Guide.
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. For more information, see “Dump files”.
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.
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.
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.
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"
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
Dumps completed transactions from the inventory_db transaction log file to device dev1:
dump tran inventory_db to dev1 with standby_access
Table 7-23 describes the commands and system procedures used to back up databases and logs.
To do this |
Use this command |
|---|---|
Make routine dumps of the entire database, including the transaction log. |
dump database |
Make routine dumps of the transaction log, then truncate the inactive portion. |
dump transaction |
Dump the transaction log after failure of a database device. |
dump transaction with no_truncate |
Truncate the log without making a backup. Then copy the entire database. |
dump transaction with truncate_only dump database |
Truncate the log after your usual method fails due to insufficient log space. Then copy the entire database. |
dump transaction with no_log dump database |
Respond to the Backup Server’s volume change messages. |
sp_volchanged |
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 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! Never 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 dump from an 11.x Adaptive Server to a 10.x Backup Server.
You cannot have 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.
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 this option 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 will be loading 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, version 11.9.2 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.
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.
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 Adaptive Server’s 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 this option. 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.
Use dump database immediately after creating a database to make a copy of the entire database. You cannot run dump transaction on a new database until you have run dump database.
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 Adaptive Server’s 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. For more information, 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 Adaptive Server’s current working directory.
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 the underscore (_), 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. For more information, 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.
Dumping a log with the init option overwrites any existing files on the tape or disk.
Dump file names identify which database was dumped and when the dump was made. If you do not specify a file name, Backup Server creates a default file name by concatenating the following:
Last seven characters of the database name
Two-digit year number
Three-digit day of the year (1– 366)
Hexadecimal-encoded time at which the dump file was created
For example, the file cations930590E100 contains a copy of the publications database made on the 59th day of 1993:
Figure 7-4: File naming convention for transaction log dumps
The Backup Server sends the dump file name to the location specified by the with notify clause. Before storing a backup tape, the operator should label it with the database name, file name, date, and other pertinent information. When loading a tape without an identifying label, use the with headeronly and with listonly options to determine the contents.
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.
By default (noinit), Backup Server writes successive dumps to the same tape volume, making efficient use of high-capacity tape media. Data is added following the last end-of-tape mark. New dumps can be appended only to the last volume of a multivolume dump. Before writing to the tape, Backup Server verifies that the first file has not yet expired. If the tape contains non-Sybase data, Backup Server rejects it to avoid destroying potentially valuable information.
Use the init option to reinitialize a volume. If you specify init, Backup Server overwrites any existing contents, even if the tape contains non-Sybase data, the first file has not yet expired, or the tape has ANSI access restrictions.
Figure 7-5 illustrates how to dump three transaction logs to a single volume. Use:
init to initialize the tape for the first dump
noinit (the default) to append subsequent dumps
unload to rewind and unload the tape after the last dump
Figure 7-5: Dumping three transaction logs to a single volume
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.
SQL92 – Compliance level: Transact-SQL extension.
Only System Administrators, users who have been granted the Operator role, and the Database Owner can execute dump transaction.
Commands dump database, load database, load transaction, online database
System procedures sp_addumpdevice, sp_dboption, sp_dropdevice, sp_helpdevice, sp_logdevice, sp_volchanged
