Loads a backup copy of the transaction log that was created with dump transaction.
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,
dumpvolume = volume_name,
file = file_name,
[dismount | nodismount],
[nounload | unload],
listonly [= full],
headeronly,
notify = {client | operator_console}
until_time = datetime}]]
is the name of the database to receive data from a dumped backup copy of the transaction log. The log segment of the receiving database must be at least as large as the log segment of the dumped database. The database name can be specified as a literal, a local variable, or a parameter of a stored procedure.
invokes the decompression of the archived transaction log. See Chapter 27, “Backing Up and Restoring User Databases” in the System Administration Guide for more information about the compress option.
is the name of the dump device from which you are loading the transaction log. For information about the form to use when specifying a dump device, see “Specifying dump devices”. For a list of supported dump devices, see the Adaptive Server installation and configuration guides.
is the 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. This option is ignored.
overrides the default block size for a dump device. If you specify a block size on UNIX systems, it should be identical to that used to make the dump.
is the volume name field of the ANSI tape label. load transaction checks this label when the tape is opened 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 loads data from all devices concurrently, 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 load are dismounted when the load completes. Use nodismount to keep tapes available for additional loads or dumps.
determines whether tapes rewind after the load completes. By default, tapes do not rewind, allowing you to make additional loads from the same tape volume. Specify unload for the last dump file to be loaded from a multidump volume. This rewinds and unloads the tape when the load completes.
is the name of a particular database dump on the tape volume. If you did not record the dump file names at the time you made the dump, use listonly to display information about all the dump files.
displays information about all the dump files on a tape volume, but does not load the transaction log. listonly identifies the database and device, the date and time the dump was made, and the date and time it can be overwritten. listonly = full provides additional details about the dump. Both reports are sorted by ANSI tape label.
After listing the files on a volume, the Backup Server sends a volume change request. The operator can either mount another tape volume or terminate the list operation for all dump devices.
In the current implementation, listonly overrides headeronly.
WARNING! Do not use load transaction with listonly on 1/4-inch cartridge tape.
displays header information for a single dump file, but does not load the database. headeronly displays information about the first file on the tape unless you use the file = file_name option to specify another file name. The dump header indicates:
Type of dump (database or transaction log)
Database ID
File name
Date the dump was made
Character set
Sort order
Page count
Next object ID
Checkpoint location in the log
Location of the oldest begin transaction record
Old and new sequence dates
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.
loads the transaction log up to a specified time in the transaction log. Only transactions committed before the specified time are saved to the database.
Loads the transaction log for the database pubs2 tape:
load transaction pubs2 from "/dev/nrmt0"
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
Loads the transaction log for pubs2, up to March 20, 1997, at 10:51:43:866 a.m:
load transaction pubs2 from "/dev/ntmt0" with until_time = "mar 20, 1997 10:51:43:866am"
The listonly and headeronly options display information about the dump files without loading them.
Dumps and loads are performed through Backup Server.
Table 7-28 describes the commands and system procedures used to restore databases from backups:
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:
Load database
Load transaction (repeat as needed)
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:
Load the most recent database dump.
Load, in order, all transaction logs generated after the last database dump.
Use online database to do the upgrade.
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.
SQL92 – Compliance level: Transact-SQL extension.
load transaction permission defaults to the Database Owner and operators. It is not transferable.
Commands disk unmirror, dump database, dump transaction, load database, online database
System procedures sp_dboption, sp_helpdb, sp_helpdevice, sp_volchanged
