dump database

Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server.

If you are not dumping compressed data, the target platform of a load database operation need not be the same platform as the source platform where the dump database operation occurred. Dumps and loads of compressed data must occur on the same platform. However, 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.

Syntax

dump database database_name cumulative
	using config = config_name
	[with {
		verify [= header | full]
		}]
	to [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		capacity = number_kilobytes, 
		dumpvolume = volume_name, 
		file = file_name] 
		[with shrink_log]
		with verify[= header | full]
	[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]] 
	[[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		listonly=load_sql | create_sql,
        density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level, verify={crc | read_after_write}
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		passwd = password,
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}
		}] 
(Tivoli Storage Manager) Use this syntax for copying the database when the Tivoli Storage Manager provides backup services.
dump database database_name 
	to "syb_tsm::object_name"
		[blocksize = number_bytes]
	[stripe on "[syb_tsm::]object_name"
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		compression = compress_level,
		passwd = password,
		[noinit | init],
		notify = {client | operator_console},
		verify[ = header | full]
		} ]

Parameters

Examples

Usage

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

dump database executes in three phases. A progress message informs you when each phase completes. When the dump is finished, it reflects all changes that were made during its execution, except for those initiated during phase 3.

Cyclic redundancy checks:
  • Dumps created without the verify=crc parameter use the same format as versions of Backup Server earlier than 16.0.
  • SAP ASE ignores the verify=crc option if the database was not originally dumped using verify=crc.
  • You cannot load dumps that include cyclic redundancy checks with versions of Backup Server that do not include this functionality.
  • verify={crc | read_after_write} checks are applicable only for files created using the with compression parameter.
  • verify=crc works with any file type, including raw devices, disk files, tapes, pipes, or APIs. However, verify=read_after_write requires a ‘seek back’ for rereading the block, and is applicable only with raw devices and disk files.
  • SAP ASE ignores, and does not raise an error message, if you include verify={crc | read_after_write} parameters that are not applicable.
Dumping compressed data:
  • You cannot create a dump of a compressed table on one platform and load this dump on a different platform.

  • Compressed data is dumped directly to an archived location.

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

If you issue a dump command without the init qualifier and Backup Server cannot determine the device type, the dump command fails. See the System Administration Guide.

Backup servers:
  • You must have a Backup Server running on the same machine as the SAP ASE 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 dump across a network, you must also have a Backup Server installed on the remote machine.

The dump database command takes into account when a device is mirrored and will store the path to mirror devices along with regular devices in the dump header. The sybdumptran utility first attempts to open the primary device, and in the event the primary device cannot be opened, the mirrored device is opened, if one exists.     

Database dumps from a 32-bit version of an SAP ASE server are fully compatible with a 64-bit version of an SAP ASE server of the same platform, and vice-versa.

See also:
  • Backing Up and Restoring User Databases in the System Administration Guide; Using Backup Server with IBM Tivoli Storage Manager for dump database syntax when the Tivoli Storage Manager is licensed at your site..

  • sp_addthreshold, sp_dumpdevice, sp_dropdevice, sp_dropthreshold, sp_helpdb, sp_helpdevice, sp_helpthreshold, sp_hidetext, sp_logdevice, sp_spaceused, sp_volchanged in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be the database owner, or a user with dump 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

34

Audit option

dump

Command or access audited

dump 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
dump transaction
load database
load transaction