Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

load transaction

Usage

Usage

• The listonly and headeronly options display information about the dump files without loading them.

• Dumps and loads are performed through Backup Server.

• Table 1-30 describes the commands and system procedures used to restore databases from backups:

  Table 1-30: Commands used to restore databases

  Use this command	To do this
  create database for load	Create a database for the purpose of loading a dump.
  load database	Restore a database from a dump.
  load transaction	Apply recent transactions to a restored database.
  online database	Make a database available for public use after a normal load sequence or after upgrading the database to the current version of Adaptive Server.
  load { database | transaction } with {headeronly | listonly}	Identify the dump files on a tape.
  sp_volchanged	Respond to the Backup Server's volume change messages.

• You cannot load a dump that was made on a different platform.

• You cannot load a dump that was generated on a version before 10.0 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 does 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. It 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 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.

• While you are loading a database, it cannot be in use. load transaction, unlike load database, does not change the offline/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 10.0 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 Adaptive Server's current working directory.

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