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

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

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]}]]

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
		}]]

load database database_name
	from dump_device
	[ [stripe on stripe_device] ... ]
	[with [norecovery,][passwd=password]

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

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

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

  For archive databases, database_name is the name of the archive database into which you want to load.

• cumulative – load a backup created with the dump database cumulative keyword.
• compress:: – invokes the decompression of the archived database. For more information about the compress option, see Backing Up and Restoring User Databases in the System Administration Guide.
  Note: You should use the native "compression = compress_level" option 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.
• from dump_device – specifies the name of the disk database dump from which you want to load the dump.
• from stripe_device – is the device from which data is being loaded. For a list of supported dump devices, see the SAP ASE 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.
• listonly = [load_sql | create_sql | volume] – generates the following:

  • load_sql – sequence of load database or load transaction SQL commands to perform restore to a specified point in time.

    Do not use the from clause when using with listonly=load_sql; the server displays an error and the load database command does not execute.

  • create_sql – displays the sequence of disk init/sp_cacheconfig, disk init, create/alter database, and the create/alter database sequence from the latest dump image obtained from the backup history.

    When you use with listonly=create_sql when loading a database from a stripe device, this option displays disk init/sp_cacheconfig, disk init, create, or alter database sequence from the dump image.

  • volume – displays volume information from the dump image.

  Note: To use the listonly = create_sql and listonly = load_sql parameters, you must first turn on the sp_configure enable dump history parameter:

  sp_config "enable dump history" 1

• density = density_value – is ignored. See dump database.
• 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. See dump database.
• 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.
  Note: 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.
• file = file_name – 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.
• 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.
• 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.
• with [norecovery,] – indicates when materializing an archive database that the load database command does not run recovery, and that the database is brought online automatically after the load database command has completed.
• passwd = password – is the password you provided to protect the dump file from unauthorized users. The password must be between 6 and 30 characters long. You cannot use variables for passwords. For rules on passwords, see Managing Adaptive Server Logins, Database Users, and Client Connections in the System Administration Guide, Volume 1.
• until_time = datetime – generates a load sequence to load (to a specified point in time).
• 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.
• with verify[only][=header | full | crc] – performs a minimal header or structural row check on the data pages as they are being copied to the archives, but does not load the database . 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. Any faults found are reported in the backupserver error log

  Use crc to indicate that you are performing a cyclic redundancy check for accidental changes to raw data for compressed database or transaction dumps.

• headeronly – 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

• notify = {client | operator_console} – 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.

• override – you must use with override to successfully load the database containing encryption keys that encrypt columns in other databases.
• syb_tsm::object_name – is the keyword that invokes the libsyb_tsm.so module that enables communication between Backup Server and TSM.
• -S source_server_name – specifies the name of the source SAP ASE server when it is not the same as the target SAP ASE server. This parameter is required when the target server for the load operation is different from the source server used for the dump operation.
• -D source_database_name – specifies the name of the source database when it is not the same as the target database. This parameter is required when the target database for the load operation is different from the source database used for the dump operation.
• verify[only]=crc – performs a cyclic redundancy check as it loads the database.

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 – Loads the testdb database from “syb_tsm::obj1.2”. See dump database for the associated dump command.

  load database testdb from "syb_tsm::obj1.2"
  stripe on "syb_tsm::obj1.2"
  stripe on "syb_tsm::obj1.2"
  stripe on "syb_tsm::obj1.2"
  stripe on "syb_tsm::obj1.2"

• Example 6 – Loads the pubs2 database from the TSM backup object “obj1.1” when the source database (testdb) of the associated dump command is different than the target database (pubs2) of the load command.

  load database pubs2 from "syb_tsm::-D testdb::obj1.1"

• Example 7 – Creates database testdb on five database devices named testdata1, testdata2, testdata3, testlog4, and testlog5:

  disk init name = 'testdata1', physname='/tmp/t_dat1',size='10M'
  go
  disk init name='testdata2',physname='/tmp/t_dat2',size='10M'
  go
  disk init name='testdata3',physname='/tmp/t_dat3',size='10M'
  go
  disk init name='testlog4',physname='/tmp/t_log4',size='10M'
  go
  disk init name='testlog5',physname='/tmp/t_log5',size='10M'
  go
  create database testdb on testdata1='10M', testdata2='8M', testdata3='5M' 
      log on testlog4='6M',testlog5 with override
  go
  alter database testdb on testdata3 = '5M'
  go
  alter database testdb log on testlog4 = '2M'
  go

  The dump of the database testdb is taken using dump database, which writes additional database device information in dump header.

  dump database testdb to "test.dmp"
  go

  The load of the database testdb using the dump image test.dmp with the with headeronly option results in displaying dump header contents. This results in displaying additional information about the database devices:

  1> load database testdb from "test.dmp" with headeronly
  2> go
  
  Backup Server: 6.28.1.1: Dumpfile name 'test1025109FD6   ' section number 1
  mounted on disk file '/punedbaccess3_dev3/kelkara/backupserver/test.dmp'
  …..
  dbdevinfo: vdevno=1 devname=testdata1 path=/tmp/test1.dat db_size=10485760 device_size=20967424
  dbdevinfo: vdevno=2 devname=testdata2 path=/tmp/test2.dat db_size=8388608 device_size=20967424
  dbdevinfo: vdevno=3 devname=testdata3 path=/tmp/test3.dat db_size=10485760 device_size=20967424
  dbdevinfo: vdevno=4 devname=testlog4 path=/tmp/test4.dat db_size=8388608 device_size=20967424
  dbdevinfo: vdevno=5 devname=testlog5 path=/tmp/test5.dat db_size=6291456 device_size=20967424
  …..

  The database device information consists of vdevno, devname, path, db_size and device_size. The device_size is the total size of device allocated at the time of disk init command. The db_size is the size of the device used by database testdb.The load of the database testdb using the dump image test.dmp with the create_sqlgenddlonly option displays sequence of create/alter database commands which can be used to create target database with same data/log segment layout as for the source database at the time of dump command. This output can be routed to a file so as to generate isql command script to create target the database:

  1> load database test from "test.dmp" with listonly=create_sql
  2> go
  
  DISK INIT
           name = 'testdata1'
           , physname = '/tmp/t_dat1'
           , size = '10M'
  go
  DISK INIT
           name = 'testdata2'
           , physname = '/tmp/t_dat2'
           , size = '10M'
  go
  DISK INIT
           name = 'testdata3'
           , physname = '/tmp/t_dat3'
           , size = '10M'
  go
  DISK INIT
           name = 'testlog4'
           , physname = '/tmp/t_log4'
           , size = '10M'
  go
  DISK INIT
           name = 'testlog5'
           , physname = '/tmp/t_log5'
           , size = '10M'
  go
  
  CREATE DATABASE testdb 
  ON testdata1 = '10M'
  , testdata2 = '8M'
  , testdata3 = '5M' 
  LOG ON  testlog4 = '6M'
  , testlog5 = '6M'
  go
  ALTER DATABASE testdb 
  ON testdata3 = '5M' 
  LOG ON  testlog4 = '2M'
  go

• Example 8 – Displays the load command sequence required to restore a specific database using the latest available dumps. The dump records from the dump history file are read to prepare the load sequence:

  1> load database testdb with listonly=load_sql
  2> go
  
  LOAD DATABASE testdb FROM '/dumpdir/testdb_DB_1.1.dmp'
  STRIPE ON '/dumpdir/testdb_DB_1.2.dmp'
  STRIPE ON '/dumpdir/testdb_DB_1.3.dmp'
  go
  LOAD TRANSACTION testdb FROM '/dumpdir/testdb_XACT_2.dmp'
  go
  LOAD TRANSACTION testdb FROM '/dumpdir/testdb_XACT_3.dmp'
  go
  LOAD TRANSACTION testdb FROM '/dumpdir/testdb_XACT_4.1.dmp'
  STRIPE ON '/dumpdir/testdb_XACT_4.2.dmp'
  go

• Example 9 – Performs a cyclic redundancy check as it loads the new_dump database dump:

  load database new_dump from mydumpdev with verify[only]=crc

Usage

• If you use sp_hidetext followed by a cross-platform dump and load, you must manually drop and re-create all hidden objects.

• The listonly and headeronly options display information about the dump files without loading them.

• If you run load database ... listonly=create_sql from a database dump that includes a shrunken log device, the SAP ASE server may list an unknown device in the create database command. You can eliminate this unknown device from the create database command if you include the shrunken log device is on the last mapped segment and issue dump database ... with shrink_log.

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

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: