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 Using Backup Server with IBM Tivoli Storage Manager 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 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 stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name, 
		file = file_name]
	[[stripe on stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name, 
		file = file_name]]...]
	[with {
		listonly=load_sql | create_sql, 
		density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name, 
		file = file_name, 
		[dismount | nodismount],
		[nounload | unload],
		passwd = password, 
		until_time = datetime, 
		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, 
		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]

Generates a sequence of load database SQL statements to restore a database to a specified point in time:

load database database_name 
	[from stripe_device]
	with listonly=[load_sql | create_sql | volume]

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

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

• CIS 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 12.5.4.

• 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 a database, Adaptive Server automatically identifies the endian type on the dump file and performs all necessary conversions while the load database and online database commands execute.

  After Adaptive Server converts the index rows, the order of index rows may be incorrect. Adaptive Server marks the following indexes on user tables as suspect indexes during execution of online database:

  • Nonclustered index on APL (all pages locked) table

  • Clustered index on DOL (data-only locked) table

  • Nonclustered index on DOL table

  During cross-platform dump and load operations, suspect partitions are handled as follows:

  • During the first online database command, after you execute load database across two platforms with different endian types, the hash partition is marked suspect.

  • Any global clustered index on a round-robin partition, which has an internally generated partition condition with a unichar or univarchar partition key, is marked suspect.

  • After the database is online, use sp_post_xpload to fix the suspect partitions and indexes.

  See “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 are 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 command has 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:

  1. Load the most recent database dump.

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

  3. Issue online database to do the upgrade and make the database available for public use.

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

• You can only load dumps of the master database into the master database or an archive database.

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

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.

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.

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.

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

• The database dump for an archive database is required to be a disk dump on a file system mounted on the local machine. This can be local storage or NFS storage. load database ... at <remote server> syntax is not supported, nor are database dumps on tape.

• Cross-architecture loads are not supported. The database dump and the load database command must be performed on the same architecture with respect to byte ordering.

• The dumped database must have the same page size as that used by the server that is hosting the archive database.

• The major version of the server on which the dump was taken must be earlier than or equal to the major version of the server hosting the archive database.

• The character set and sort order on the server on which the database dump was taken must be the same as the character set and sort order of the server hosting the archive database.

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.

• You cannot create a dump of a compressed table on one platform and load this dump on a different platform.

• create index commands on compressed tables that contain any form of compressed or uncompressed rows are fully recovered during a load transaction.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for load database differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also