load database


Loads a backup copy of a user database, including its transaction log, that was created with dump database.


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,
		dumpvolume = volume_name, 
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		notify = {client | operator_console}



is the name of the database that will 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.


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.

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

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.

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.

file = file_name

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

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.


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.


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 



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


SQL92 – Compliance level: Transact-SQL extension.


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

See also

Commands alter database, dbcc, dump database, dump transaction, load transaction, online database

System procedures sp_helpdevice, sp_volchanged, sp_helpdb