load database

Description

Loads a backup copy of a user database, including its transaction log, that was created with dump database, as well as materialize archive databases that have been loaded with a database dump.

The target platform of a load database operation need not be the same platform as the source platform where the dump database operation occurred. dump database and load database are performed from either a big endian platform to a little endian platform, or from a little endian platform to a big endian platform.

See Tivoli Storage Manager only for the load database syntax when the Tivoli Storage Manager is licensed at your site.

Syntax

Makes a routine database load:

load database database_name 
	from [compression=]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name, 
		file = file_name] 
		with verify only [= header | full]
	[stripe on [compression=]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name, 
		file = file_name]
	[[stripe on [compression=]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],
		passwd = password, 
		notify = {client | operator_console},
		[override]}]]

Returns header or file information without loading the backup:

load database 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],
		passwd = password, 
		listonly [= full],
		headeronly, 
		notify = {client | operator_console}
		}]]

Materializes an archive database:

load database database_name
	from dump_device
	[ [stripe on stripe_device] ... ]
	[with [norecovery,][passwd=password]

Loads a copy of the database when the Tivoli Storage Manager is licensed at your site:

load database 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},
		[[verifyonly | verify] [= header | full]]
		} ]

Parameters

database_name

is the name of the database to receive the backup copy. It can be either a database created with the for load option, or an existing database. Loading dumped data to an existing database overwrites all existing data. The receiving database must be at least as large as the dumped database. The database name can be specified as a literal, a local variable, or a stored procedure parameter.

For archive databases, database_name is the name of the archive database into which you want to load.

compress::

invokes the decompression of the archived database. For more information about the compress option, see Chapter 27, “Backing Up and Restoring User Databases” in the System Administration Guide.

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 dump_device

specifies the name of the disk database dump from which you want to load the dump.

from stripe_device

is the device from which data is being loaded. See “Specifying dump devices” for information about what form to use when specifying a dump device. 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

is ignored. For more information, see the dump database command.

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. For more information, see the dump database command.

dumpvolume = volume_name

is the volume name field of the ANSI tape label. load database checks this label when the tape is opened and generates an error message if the wrong volume is loaded.

NoteWhen using load database, the dumpvolume option does not provide an error message if an incorrect file name is given for the file=filename option. The backup server searches the entire tape looking for that file, regardless of an incorrect tape mounted.

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

compression

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

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.

with [norecovery,]

indicates when materializing an archive database that the load database command will not run recovery, and that the database is brought online automatically after the load database command has completed.

passwd = password

is the password you provided to protect the dump file from unauthorized users. The password must be between 6 and 30 characters long. You cannot use variables for passwords. For rules on passwords, see Chapter 14, “Managing Adaptive Server Logins, Database Users, and Client Connections,” in the System Administration Guide, Volume 1.

listonly [= full]

displays information about all dump files on a tape volume, but does not load the database. 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.

Due to current implementation, the listonly option overrides the headeronly option.

WARNING! Do not use load database with listonly on 1/4-inch cartridge tape.

with verify[only][=header | full]

performs a minimal header or structural row check on the data pages as they are being copied to the archives, but does not load the database. There are no structural checks done at this time to gam, oam, allocation pages, indexes, text, or log pages. The only other check is done on pages where the page number matches to the page header.

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:

  • 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

notify = {client | operator_console}

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.

override

you must use with override to successfully load the database containing encryption keys that encrypt columns in other databases.

syb_tsm::object_name

is the keyword that invokes the libsyb_tsm.so module that enables communication between Backup Server and TSM.

-S source_server_name

specifies the name of the source Adaptive Server when it is not the same as the target Adaptive Server. This parameter is required when the target server for the load operation is different from the source server used for the dump operation.

-D source_database_name

specifies the name of the source database when it is not the same as the target database. This parameter is required when the target database for the load operation is different from the source database used for the dump operation.

Examples

Example 1

Reloads the database pubs2 from a tape device:

load database pubs2 
    from "/dev/nrmt0"

Example 2

Loads the pubs2 database, using the Backup Server REMOTE_BKP_SERVER. This command names three devices:

load database 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 pubs2 database from a compressed dump file called dmp090100.dmp located at /opt/bin/Sybase/dumps:

load database pubs2 from 
    "compress::/opt/bin/Sybase/dumps/dmp090100.dmp"

Example 4

Loads the key_db database, which contains encryption keys. You must use with override if the encryption keys in key_db were used to encrypt columns in other databases:

load database key_db from "/tmp/key_db.dat" with override

Example 5

Loads the testdb database from “syb_tsm::obj1.2”. See dump database for the associated dump command.

load database testdb from "syb_tsm::obj1.2"
stripe on "syb_tsm::obj1.2"
stripe on "syb_tsm::obj1.2"
stripe on "syb_tsm::obj1.2"
stripe on "syb_tsm::obj1.2"

Example 6

Loads the pubs2 database from the TSM backup object “obj1.1” when the source database (testdb) of the associated dump command is different than the target database (pubs2) of the load command.

load database pubs2 from "syb_tsm::-D testdb::obj1.1"

Usage


Restrictions


Locking out users during loads


Upgrading database and transaction log dumps


Specifying dump devices


Backup Servers


Volume names


Changing dump volumes


Restoring the system databases


Disk mirroring


Materializing an archive database

An archive database is a placeholder that is useful only once it has been loaded with a database dump. The load process does not actually copy pages, however, it materializes the database using page mapping.

NoteYou do not need to have Backup Server running when loading a database dump into an archive database.


Using load database with norecovery

The with norecovery option of the load database command allows a database dump to be loaded into an archive database without recovering anything, reducing the time required to load. Many database pages can be modified or allocated during recovery, causing them to be stored in the modified pages section. Therefore, skipping recovery consumes minimum space in the modified pages section. The with norecovery option allows a quick view into an archive database.

If you use with norecovery, the database is brought online automatically.

However, using load database with norecovery for a database that requires recovery may leave it transactionally and physically inconsistent. Running dbcc checks on a physically inconsistent database may produce many errors.

Once you have loaded an archive database with norecovery, you must have sa_role or database owner privileges to use it.


Using logical devices with an archive database

You can use sp_addumpdevice to create a logical device from which an archive database can be loaded:

sp_addumpdevice ‘archive database’, ‘logical_name’, 
	‘physical_name

After you have executed this command, use the logical_name instead of the physical_name as the dump_device or stripe_device in a load database command.

NoteYou cannot use an archive database logical device as a device specification for a load into a traditional database or when dumping a traditional database.


load database limitations with an archive database

load database has the following limitations when used with an archive database:


load database and encrypted columns

If you store keys in a database that is separate from the columns encrypted by those keys, you must load both databases from dumps that were made simultaneously, avoiding a problem where the encrypted column’s key is missing after the load.

After loading the databases for keys and data, bring both databases on line simultaneously.

You should not load your key database into a database with a different name because metadata dependencies exist between encrypted columns and their keys. If you must change the name of the key database:

  1. Before dumping the database containing the encrypted columns, use alter table to decrypt the data.

  2. Dump the databases containing keys and encrypted columns.

  3. After loading the databases, use alter table to re-encrypt the data with the keys in the newly-named database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only a System Administrator, Database Owner, or user with the Operator role can execute load database.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

43

load

load database

  • 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 alter database, dbcc, dump database, dump transaction, load transaction, online database

System procedures sp_helpdb, sp_helpdevice, sp_hidetext, sp_volchanged