load transaction

Description

Loads a backup copy of the transaction log that was created with dump transaction.

Syntax

Makes a routine log load:

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		notify = {client | operator_console}
		}]]

Returns header or file information without loading the backup log:

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		headeronly,
		notify = {client | operator_console}
		until_time = datetime}]]

Loads a transaction log into an archive database:

load tran[saction] database_name
	from dump_device
	[[stripe on stripe_device] ... ]

(Tivoli Storage Manager only) Loads a copy of the transaction log when the Tivoli Storage Manager is licensed at your site.

load transaction database_name 
	from syb_tsm::[[-S source_sever_name][-D source_database_name]
		::]object_name [blocksize = number_bytes]
	[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]
	[[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		passwd = password,
		listonly [= full],
		headeronly,
		notify = {client | operator_console},
		until_time = datetime
		} ]

Parameters

Examples

Example 1

Loads the transaction log for the database pubs2 tape:

load transaction pubs2 
    from "/dev/nrmt0"

Example 2

Loads the transaction log for the pubs2 database, using the Backup Server REMOTE_BKP_SERVER:

load transaction pubs2
        from "/dev/nrmt4" at REMOTE_BKP_SERVER
    stripe on "/dev/nrmt5" at REMOTE_BKP_SERVER
    stripe on "/dev/nrmt0" at REMOTE_BKP_SERVER

Example 3

Loads the transaction log for pubs2, up to March 20, 2008, at 10:51:43:866 a.m:

load transaction pubs2
    from "/dev/ntmt0"
    with until_time = "mar 20, 2008 10:51:43:866am"

Example 4

• You cannot load a dump that was generated on a version earlier than 11.9 server.

• The database and transaction logs must be at the same release level.

• Load transaction logs in chronological order.

• You cannot load from the null device (on UNIX, /dev/null).

• You cannot use load transaction after an online database command that performs an upgrade. The correct sequence for upgrading a database is load database, load transaction, online database.

• Do not issue online database until all transaction logs are loaded. The command sequence is:

  1. Load database

  2. Load transaction (repeat as needed)

  3. Online database

  However, to load additional transaction logs while retaining read-only access to the database (a typical “warm backup” situation), use the dump tran for standby_access option to generate the transaction dumps. You can then issue online database for standby_access for read-only access.

• You cannot use the load transaction command in a user-defined transaction.

• To restore a database:

  • Load the most recent database dump

  • Load, in order, all transaction log dumps made since the last database dump

  • Issue online database to make the database available for public use

• 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.

• For more information on backup and recovery of Adaptive Server databases, see the System Administration Guide.

• You can use the until_time option for most databases that can be loaded or dumped. until_time does not apply to databases such as master, in which the data and logs are on the same device. Also, you cannot use it on any database that has had a truncated log since the last dump database, such as tempdb.

• The until_time option is useful for the following reasons:

  • It enables you to have a database consistent to a particular time. For example, in an environment with a decision-support system (DSS) database and an online transaction processing (OLTP) database, the system administrator can roll the DSS database to an earlier specified time to compare data between the earlier version and the current version.

  • If a user inadvertently destroys data, such as dropping an important table, you can use the until_time option to back out the errant command by rolling forward the database to a point just before the data was destroyed.

• To effectively use the until_time option after data has been destroyed, you must know the exact time the error took place. You can find out by executing a select getdate () command immediately after the error. For a more precise time using milliseconds, use the convert function, for example:

  select convert (char (26), getdate (), 109)
  

  --------------------------
  Feb 26 1997 12:45:59:650PM
  

• After you load a transaction log using until_time, Adaptive Server restarts the database’s log sequence. This means that until you dump the database again, you cannot load subsequent transaction logs after the load transaction using until_time. Dump the database before you dump another transaction log.

• Only transactions that committed before the specified time are saved to the database. However, in some cases, transactions committed shortly after the until_time specification are applied to the database data. This may occur when several transactions are committing at the same time. The ordering of transactions may not be written to the transaction log in time-ordered sequence. In this case, the transactions that are out of time sequence are reflected in the data that has been recovered. The time should be less than a second.

• For more information on recovering a database to a specified time, see the System Administration Guide.

• A database cannot be in use when you load it. you are loading a database, it cannot be in use. load transaction, unlike load database, does not change the offline or online status of the database. load transaction leaves the status of the database the way it found it. load database sets the status of the database to “offline.” No one can use the database while it is “offline.” The “offline” status prevents users from accessing and changing the database during a load sequence.

• A database loaded by load database remains inaccessible until online database is issued.

To restore and upgrade a user database dump from a version 11.9 or later server to the current version of Adaptive Server:

1. Load the most recent database dump.

2. Load, in order, all transaction logs generated after the last database dump.

3. Use online database to do the upgrade.

4. Dump the newly upgraded database immediately after the upgrade, to create a dump that is consistent with the current version of Adaptive Server.

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

• When loading from a local device, you can specify the dump device as:

  • An absolute path name

  • A relative path name

  • A logical device name from the sysdevices system table

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

• When loading across the network, specify the absolute path name of the dump device. (You cannot use a relative path name or a logical device name from the sysdevices system table.) The path name must be valid on the machine on which the Backup Server is running. If the name includes any characters other than letters, numbers or the underscore (_), you must enclose it in quotes.

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

• You can run more than one load (or dump) at the same time, as long as each one uses a different physical device.

• 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 load 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 a network, you must specify the same number of stripe devices for each operation.

If Backup Server detects a problem with the currently mounted volume, it requests a volume change by sending messages to either the client or its operator console. After mounting another volume, the operator notifies Backup Server by executing sp_volchanged on any Adaptive Server that can communicate with Backup Server.

For step-by-step instructions for restoring the system databases from dumps, see the System Administration Guide.

• At the beginning of a load, Adaptive Server passes the primary device name of each logical database device and 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 any named device fails before the Backup Server completes its data transfer, Adaptive Server aborts the load.

• If you attempt to unmirror any of the named devices while a load transaction is in progress, Adaptive Server displays a message. The user executing disk unmirror can abort the load, or defer disk unmirror until after the load completes.

• Backup Server loads the data onto the primary device, then load transaction copies it to the secondary device. load transaction takes longer to complete if any database device is mirrored.

When you load a transaction log into an archive database, load tran runs the recovery redo pass. Modified and new database pages are written to the permanent changes segment. You must have enough space in the modified pages section to accommodate these changes. If necessary, increase space for the modified pages section by using alter database to increase the normal database storage allocated to the archive database.

Unlike a traditional database, an archive database can be brought online in the middle of a load sequence without breaking the load sequence. When a traditional database is loaded and then brought online without using the for standby_access clause, it is no longer possible to load the next transaction log in the load sequence. An archive database however, can be brought online without the for standby_access clause and later, loaded with the next transaction log in the load sequence. This allows read-only operations like running consistency checks, at any time during the load sequence. This is possible because when loading a transaction log into the archive database, Adaptive Server automatically removes the disposable changes segment from the modified pages section. This effectively reverts the archive database to its state after the previous load was done, thereby allowing the next transaction log in the sequence to be loaded.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are:

See also