load database  lock table

Chapter 1: Commands

load transaction

Description

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

Syntax

To make 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}
		}]]

To return 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}]]

Parameters

database_name

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.

compress::

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.

NoteSybase recommends the native "compression = compress_level" option as preferred over the older "compress::compression_level" option. If you use the native option for dump database, you do not need to use "compress::compression_level" when loading your database.

from stripe_device

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.

at backup_server_name

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.

density = density_value

overrides the default density for a tape device. This option is ignored.

blocksize = number_bytes

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.

dumpvolume = volume_name

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.

file = file_name

is the name of a particular database dump on the tape volume. If you did not record the dump file names when you made the dump, use listonly to display information about all the dump files.

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

compression

indicates that the log you are loading was compressed to a file on a remote server. You do not need to specify the compression level for load transaction.

The with compression option differs from the compress option, which you use to load a compressed log from a local file.

NoteSybase recommends the native "compression = compress_level" option as preferred over the older "compress::compression_level" option. If you use the native option for dump database, you do not need to use "compress::compression_level" when loading your database.

dismount | nodismount

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.

nounload | unload

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.

listonly [ = full]

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.

headeronly

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:

notify = {client | operator_console}

overrides the default message destination.

until_time

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.

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, 1997, at 10:51:43:866 a.m:

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

Usage


Restrictions


Restoring a database


Recovering a database to a specified time


Locking users out during loads


Upgrading database and transaction log dumps


Specifying dump devices


Backup Servers


Volume names


Changing dump volumes


Restoring the system databases


Disk mirroring

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

load transaction permission defaults to the Database Owner and operators. It is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

44

load

load 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 in effect

See also

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

Commands disk unmirror, dump database, dump transaction, load database, online database

System procedures sp_dboption, sp_helpdb, sp_helpdevice, sp_hidetext, sp_volchanged





Copyright © 2005. Sybase Inc. All rights reserved. lock table

View this book as PDF