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],
headeronly,
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.
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.
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.
is ignored. For more information, see the dump database command.
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.
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.
When 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.
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 dump files.
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:
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
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.
Reloads the database pubs2 from a tape device:
load database pubs2 from "/dev/nrmt0"
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
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"
The listonly and headeronly options display information about the dump files without loading them.
Dumps and loads are performed through Backup Server.
To make sure databases are synchronized correctly so that all the proxy tables have the correct schema to the content of the primary database you just reloaded, you may need to run the alter database dbname for proxy_update command on the server hosting the proxy database.
Table 7-27 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 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 server version before version 10.0.
If a database has cross-database referential integrity constraints, the sysreferences system table stores the name—not the ID number—of the external database. Adaptive Server cannot guarantee referential integrity if you use load database to change the database name or to load it onto a different server.
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. Before dumping a database to load it with a different name or move it to another Adaptive Server, use alter table to drop all external referential integrity constraints.
load database clears the suspect page entries pertaining to the loaded database from master..sysattributes.
load database overwrites any existing data in the database.
After a database dump is loaded, two processes may require additional time before the database can be brought online:
All unused pages in the database must be zeroed after the load completes. The time required depends on the number of unused pages. If the target database is the same size as the database that is loaded, the Backup Server performs this step. If the target database is larger than the database that is loaded, Adaptive Server performs this step after the Backup Server completes the load. The time required for this step depends on the number of empty pages.
All transactions in the transaction log included in the database dump must be rolled back or rolled forward. The time required depends on the number and type of transactions in the log. This step is performed by Adaptive Server.
The receiving database must be as large as or larger than the database to be loaded. If the receiving database is too small, Adaptive Server displays an error message that gives the required size.
You cannot load from the null device (on UNIX, /dev/null).
You cannot use load database in a user-defined transaction.
While you are loading a database, it cannot be in use. load database sets the status of the database to “offline.” No one can use the database while its status 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 log dumps made since the last database dump.
Adaptive Server checks the timestamp on each dump to make sure that it is being loaded to the correct database and in the correct sequence.
Issue online database to do the upgrade and make the database available for public use.
Dump the newly upgraded database immediately after upgrade, to create a dump 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.
You can specify a local device as:
A logical device name from the sysdevices system table
An absolute path name
A relative path name
The 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. The path name must be valid on the machine on which the Backup Server is running. If the name includes characters other than letters, numbers, or the underscore (_), enclose the entire name in quotes.
Ownership and permissions problems on the dump device may interfere with use of load commands.
You can run more than one load (or dump) at the same time, as long as each load uses a different physical device.
You must have a Backup Server running on the same machine as 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 the network, you must specify
the same number of stripe devices for each operation.
If the 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 the Backup Server by executing sp_volchanged on any Adaptive Server that can communicate with the Backup Server.
See the System Administration Guide for step-by-step instructions for restoring the system databases from dumps.
At the beginning of a load, Adaptive Server passes Backup Server the primary device name of each logical database and log device. If the primary device has been unmirrored, Adaptive Server passes the name of the secondary device instead. If any named device fails before Backup Server completes its data transfer, Adaptive Server aborts the load.
If you attempt to unmirror any named device while a load database is in progress, Adaptive Server displays a message. The user executing disk unmirror can abort the load or defer the disk unmirror until after the load completes.
Backup Server loads the data onto the primary device, then load database copies it to the secondary device. load database takes longer to complete if any database device is mirrored.
SQL92 – Compliance level: Transact-SQL extension.
Only a System Administrator, Database Owner, or user with the Operator role can execute load database.
Commands alter database, dbcc, dump database, dump transaction, load transaction, online database
System procedures sp_helpdevice, sp_volchanged, sp_helpdb
