Loads a backup copy of a user database, including its transaction log, that was created with dump database.
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.
To make 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}
}]]
To return 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}
}]]
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.
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.
Sybase 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.
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 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.
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.
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.
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.
Sybase 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.
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 password you provided to protect the dump file from unauthorized users. The password must be between 6 and 30 characters long.
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.
The backupserver performs a minimal header or structural row check on the data pages as they are being copied to the archives. 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.
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"
If you use sp_hidetext followed by a cross-platform dump and load, you must manually drop and re-create all hidden objects in order that they remain hidden.
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 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 1-29 describes the commands and system procedures used to restore databases from backups:
Component Integration Services only – Any proxy tables in the database are part of the database save set. The content data of proxy tables is not included in the save; only the pointer is saved and restored.
You cannot load a dump that was made on a different platform.
You cannot load a dump that was generated on a server earlier than version 11.9.
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:
Backup Server zeroes the non-allocated pages that are in the source database’s space map. This zeroing is embedded as part of the physical load, and happens during the load database.
If the target database is larger than the source, then the space above the ceiling of the source database’s space map is zeroed by Adaptive Server after Backup Server has completed the load.
Recovery ignores transactions that completed before the checkpoint that was written by dump database at the start of its operation. Completed transactions in the active portion of the transaction log are rolled forward by recovery. In a load sequence, rollback of incomplete transactions happens at the end of that sequence, under online database.
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.
Once you load the database, Adaptive Server automatically identifies the endian type on the dump file and performs all necessary conversions during the load database and online database.
After Adaptive Server converts the index rows, the order
of index rows may be incorrect. Adaptive Server marks following
indexes on user tables as suspect indexes during online
database.
Non-clustered index on APL table.
Clustered index on DOL table.
Non-clustered index on DOL table.
See Chapter 1, “System Procedures,” in Reference Manual: Procedures for information about checking and rebuilding indexes on user tables using the sp_post_xpload stored procedure.
dump transaction and load transaction is not allowed across platforms.
dump database and load database to or from a remote backupserver are not supported across platforms.
You cannot load a password-protected dump file across platforms.
If you perform dump database and load database for a parsed XML object, you must parse the text again after the load database is completed.
You cannot perform dump database and load database across platforms on Adaptive Servers versions earlier than 11.9.
Adaptive Server cannot translate embedded data structures stored as binary, varbinary, or image columns.
load database is not allowed on the master database across platforms.
Stored procedures and other compiled objects are recompiled from the SQL text in syscomments at the first execution after the load database.
If you do not have permission to recompile from text, then the person who does has to recompile from text using dbcc upgrade_object to upgrade objects.
A database cannot be in use while it is being loaded. 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 11.9 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 the current working directory in Adaptive Server.
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; do not delete it.
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.
ANSI SQL – Compliance level: Transact-SQL extension.
Only a System Administrator, Database Owner, or user with the Operator role can execute load database.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
43 |
load |
load database |
|
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
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
