load database

Loads a backup copy of a user database, including its transaction log, that was created with dump database, as well as materializes 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 cumulative
	from [compress::[compression_level::]]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name, 
		file = file_name] 
		with verify only [= header | full | crc]
	[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},
        verify[only]=crc
		}]]
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

Usage

See also:
  • Backing Up and Restoring User Databases in the System Administration Guide.

  • sp_helpdb, sp_helpdevice, sp_hidetext, sp_volchanged in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be the database owner, or a user with load database privilege or own database privilege on the database.

Disabled
With granular permissions disabled, you must be the database owner, or a user with any of these roles:
  • sa_role, or,

  • replication_role, or,

  • oper_role

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

43

Audit option

load

Command or access audited

load database

Information in extrainfo
  • 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 is in effect

Related reference
Encrypted Columns and dump database
dump database
alter database
dbcc
dump transaction
load transaction
online database