dump database

Description

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.

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 Tivoli Storage Manager only for dump database syntax when the Tivoli Storage Manager is licensed at your site.

Syntax

dump database database_name
	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 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 {
		density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		passwd = password,
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}
		}] 

Tivoli Storage Manager only 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

database_name

is the name of the database from which you are copying data. The database name can be specified as a literal, a local variable, or a stored procedure parameter.

compress::compression_level

is a number between 0 and 9, 100, or 101. For single-digit compression levels, 0 indicates no compression, and 9 provides the highest level of compression. Compression levels of 100 and 101 provide a faster, more efficient compression mode, with 100 providing faster compression and 101 providing better compression. If you do not specify compression_level, Adaptive Server does not compress the dump.

See Chapter 28, “Backing Up and Restoring User Databases” in the System Administration Guide for more information about the compress option.

NoteSybase recommends the native "compression = compress_level" option as preferred over the older "compress::compression_level" option. The native option allows compression of both local and remote dumps, and the dumps that it creates will describe their own compression level during a load. The older option is retained for compatibility with older applications.

to stripe_device

is the device to which to copy the data. See “Specifying dump devices” in this section for information about what form to use when specifying a dump device.

at backup_server_name

is the name of the Backup Server. Do not specify this parameter when dumping to the default Backup Server. Specify this parameter only when dumping over the network to a remote Backup Server. You can specify as many as 32 remote Backup Servers with this option. When dumping across the network, specify the network 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.

density = density_value

overrides the default density for a tape device. Valid densities are 800, 1600, 6250, 6666, 10000, and 38000. Not all values are valid for every tape drive; use the correct density for your tape drive.

blocksize = number_bytes

overrides the default block size for a dump device. The block size must be at least one database page (2048 bytes for most systems) and must be an exact multiple of the database page size. For optimal performance, specify the blocksize as a power of 2, for example, 65536, 131072, or 262144.

capacity = number_kilobytes

is the maximum amount of data that the device can write to a single tape volume. The capacity must be at least five database pages and should be less than the recommended capacity for your device.

A general rule for calculating capacity is to use 70 percent of the manufacturer’s maximum capacity for the device, allowing 30 percent for overhead such as record gaps and tape marks. The maximum capacity is the capacity of the device on the drive, not the drive itself. This rule works in most cases, but may not work in all cases due to differences in overhead across vendors and across devices.

On UNIX platforms that cannot reliably detect the end-of-tape marker, indicate how many kilobytes can be dumped to the tape. You must supply a capacity for dump devices specified as a physical path name. If a dump device is specified as a logical device name, the Backup Server uses the size parameter stored in the sysdevices system table unless you specify a capacity.

compression = compress_level

is a number between 0 and 9, 100, or 101. For single-digit compression levels, 0 indicates no compression, and 9 provides the highest level of compression. Compression levels of 100 and 101 provide a faster, more efficient compression mode, with 100 providing faster compression and 101 providing better compression. If you do not specify compression_level, Adaptive Server does not compress the dump.

NoteSybase recommends the native "compression = compress_level" option as preferred over the older "compress::compression_level" option. The native option allows compression of both local and remote dumps, and the dumps that it creates will describe their own compression level during a load. The older option is retained for compatibility with older applications.

dumpvolume = volume_name

establishes the name that is assigned to the volume. The maximum length of volume_name is 6 characters. Backup Server writes the volume_name in the ANSI tape label when overwriting an existing dump, dumping to a new tape, or dumping to a tape whose contents are not recognizable. The load database command checks the label and generates an error message if the wrong volume is loaded.

WARNING!  Label each tape volume as you create it so that the operator can load the correct tape.

with verify[= header | full]

allows the backupserver to perform 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.

stripe on stripe_device

is an additional dump device. You can use as many as 32 devices, including the device named in the to stripe_device clause. The Backup Server splits the database into approximately equal portions, and sends each portion to a different device. Dumps are made concurrently on all devices, reducing the time required to make a dump and requiring fewer volume changes during the dump. See “Specifying dump devices” for information about how to specify a dump device.

dismount | nodismount

on platforms that support logical dismount, determines whether tapes remain mounted. By default, all tapes used for a dump are dismounted when the dump completes. Use nodismount to keep tapes available for additional dumps or loads.

nounload | unload

determines whether tapes rewind after the dump completes. By default, tapes do not rewind, allowing you to make additional dumps to the same tape volume. Specify unload for the last dump file to be added to a multidump volume. This rewinds and unloads the tape when the dump completes.

passwd = password

is the password you provide 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 Chapter 14, “Managing Adaptive Server Logins, Database Users, and Client Connections,” in the System Administration Guide, Volume 1.

retaindays = number_days

on UNIX systems – when dumping to disk, specifies the number of days that Backup Server protects you from overwriting the dump. If you try to overwrite the dump before it expires, Backup Server requests confirmation before overwriting the unexpired volume.

NoteThis option is meaningful only when dumping to a disk. It is not meaningful for tape dumps.

The number_days must be a positive integer or 0, for dumps that you can overwrite immediately. If you do not specify a retaindays value, Backup Server uses the tape retention in days value set by sp_configure.

noinit | init

determines whether to append the dump to existing dump files or reinitialize (overwrite) the tape volume. By default, Adaptive Server appends dumps following the last end-of-tape mark, allowing you to dump additional databases to the same volume. New dumps can be appended only to the last volume of a multivolume dump. Use init for the first database you dump to a tape to overwrite its contents.

Use init when you want Backup Server to store or update tape device characteristics in the tape configuration file. For more information, see the System Administration Guide.

file = file_name

is the name of the dump file. The name cannot exceed 17 characters and must conform to operating system conventions for file names. For more information, see “Dump files”.

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 Backup Server is running. Use client to route other Backup Server messages to the terminal session that initiated the dump database.

On operating systems that do not offer an operator terminal feature, such as UNIX, messages are sent to the client that initiated the dump database. Use operator_console to route messages to the terminal on which Backup Server is running.

syb_tsm::obj_name

is the keyword that invokes the libsyb_tsm.so module that enables communication between Backup Server and Tivoli Storage Manager.

object_name

is the name of the backup object on TSM server.

Examples

Example 1

Dumps the database pubs2 to a tape device. If the tape has an ANSI tape label, this command appends this dump to the files already on the tape, since the init option is not specified:

dump database pubs2 
    to "/dev/nrmt0"

Example 2

For UNIX – dumps the pubs2 database, using the REMOTE_BKP_SERVER Backup Server. The command names three dump devices, so the Backup Server dumps approximately one-third of the database to each device. This command appends the dump to existing files on the tapes. On UNIX systems, the retaindays option specifies that the tapes cannot be overwritten for 14 days:

dump database pubs2
    to "/dev/rmt4" at REMOTE_BKP_SERVER
    stripe on "/dev/nrmt5" at REMOTE_BKP_SERVER
    stripe on "/dev/nrmt0" at REMOTE_BKP_SERVER
with retaindays = 14

Example 3

The init option initializes the tape volume, overwriting any existing files:

dump database pubs2
    to "/dev/nrmt0"
    with init

Example 4

Rewinds the dump volumes upon completion of the dump:

dump database pubs2
    to "/dev/nrmt0"
    with unload

Example 5

For UNIX – the notify clause sends Backup Server messages requesting volume changes to the client which initiated the dump request, rather than sending them to the default location, the console of the Backup Server machine:

dump database pubs2
    to "/dev/nrmt0"
    with notify = client

Example 6

Creates a compressed dump of the pubs2 database into a local file called dmp090100.dmp using a compression level of 4:

dump database pubs2 to 
    "compress::4::/opt/bin/Sybase/dumps/dmp090100.dmp"

Alternatively, you can create a compressed dump of the pubs2 database into a local file called dmp090100.dmp using a compression level of 100 using compression = compression_level syntax

dump database pubs2 to "/opt/bin/Sybase/dumps/dmp090100.dmp" 
    with compression = 100

Example 7

Dumps the pubs2 database to the remote machine called “remotemachine” and uses a compression level of 4:

dump database pubs2 to "/Syb_backup/mydb.db" at remotemachine 
    with compression = "4"

Example 8

Dumps the pubs2 database to the TSM backup object “obj1.1”.

dump database pubs2 to "syb_tsm::obj1.1"

Example 9

Dumps the pubs2 database to the TSM backup object “obj1.2” using multiple stripes.

dump database pubs2 to "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"


Usage


Restrictions


Scheduling dumps


Dumping the system databases


Specifying dump devices


Determining tape device characteristics


Backup servers


Dump files


File names and archive names


Volume names


Changing dump volumes


Appending to or overwriting a volume


Dumping from a 32-bit OS to a 64-bit OS

Database dumps from a 32-bit version of Adaptive Server are fully compatible with a 64-bit version of Adaptive Server of the same platform, and vice-versa.


Dumping databases whose devices are mirrored


Performance Notes

Due to the design of indexes within a dataserver that provides an optimum search path, index rows are ordered for fast access to the table's data row. Index rows which contain row identifiers (RIDs), are treated as binary to achieve a fast access to the user table.

Within the same architecture platform, the order of index rows remains valid and search order for a selection criteria takes its normal path. However, when index rows are translated across different architectures, this invalidates the order by which optimization was done. This results in an invalid index on user tables when the cross platform dump and load feature is performed.

A database dump from a different architecture, such as big endian to little endian, is loaded, certain indexes are marked as suspect:

To fix indexes on the target system, after load from a different architecture dump, you could use one of two methods:

  1. Drop and re-create all of the indexes.

  2. Use sp_post_xpload, see Chapter 1, “System Procedures,” in Reference Manual: Procedures.

Since the data point and information varies from usage on indexes, the schema, user data, number of indexes, index key length, and number of index rows, in general, it requires planning to recreate indexes on large tables as it can be a lengthy process. sp_post_xpload validates indexes, drops invalid indexes, and recreates dropped indexes, in a single command on databases.

Since sp_post_xpload performs many operations it can take longer than drop and recreate indexes. Sybase recommends that you use the drop and recreate indexes on those databases larger that 10G..


Compressed dumps for an archive database

To use a compressed dump for an archive database, you must:

NoteDumps generated with “compress::” cannot be loaded into an archive database. Therefore, any references to compression in this chapter refer to dumps generated using the with compression = <compression level> option.


Compatibility issues for a compressed dump


Encrypted columns and dump database

dump and load work on the ciphertext of encrypted columns. This behavior ensures that the data for encrypted columns remains encrypted while on disk.

dump and load pertain to the whole database. Default keys and keys created in the same database are dumped and loaded along with the data to which they pertain.

If your keys are in a separate database from the columns they encrypt, Sybase recommends that:

Because of metadata dependencies of encrypted columns on the key’s database, follow the steps below if you intend to load the key database into a database with a different name (if your data is stored in the same database as your keys, you need not follow these steps):

  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.

The consistency issues between encryption keys and encrypted columns are similar to those for cross-database referential integrity. See “Cross-database constraints and loading databases” in the System Administration Guide.


dump database and support for the Tivoli Storage Manager

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only the System Administrator, the Database Owner, and users with the Operator role can execute dump database.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

34

dump

dump database

  • 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 in effect

See also

Documents Chapter 28, “Backing Up and Restoring User Databases” in the System Administration Guide.

Commands dump transaction, load database, load transaction

System procedures sp_addthreshold, sp_addumpdevice, sp_dropdevice, sp_dropthreshold, sp_helpdb, sp_helpdevice, sp_helpthreshold, sp_hidetext, sp_logdevice, sp_spaceused, sp_volchanged