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

config = config_name – reads the specified dump configuration and performs a dump operation using the specified values.
You cannot specify a stripe directory as a parameter for the command if you use a dump configuration. The SAP ASE server creates the dump files in the stripe directory specified by the dump configuration. The dump files are named using this convention:

database_name.dump_type.date-timestamp.stripeID

Explicitly specified command parameters override the parameter values specified by the dump configuration.
cumulative – specify that the backup you create is a cumulative incremental dump.
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 – has been deprecated, and is included only for compatibility with older applications. Use "compression = compress_level" for compression instead. See Backing Up and Restoring User Databases in the System Administration Guide, Volume 2 for more information about the compress option.
Note: You should use the native "compression = compress_level" option over the older "compress::compression_level" option. The native option allows compression of both local and remote dumps, and the dumps that it creates 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.
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 compress_level, the SAP ASE server does not compress the dump.
Note: You should use the native "compression = compress_level" option over the older "compress::compression_level" option, which is retained only for compatibility with older applications.
verify={crc | read_after_write} – adds a cyclic redundancy check for accidental changes to raw data for database dumps created with compression to check and for verification that the compression blocks can be correctly read and decompressed. Options are:
- verify=crc – indicates that you are performing a cyclic redundancy check.
- verify=read_after_write – Backup Server rereads every compressed block after writing and decompressing it. If Backup Server finds an error, it prints the offset in the file it finds the error. You cannot use verify=read_after_write with load database commands.
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 shrink_log – is used when a hole in the database might be created when the alter database log off command is used to shrink space from the log. This command automatically removes holes at the end of the database if the database is not in a dump sequence. Likewise, dump database automatically removes any hole at the end of the database if the database is not in a dump sequence (that is, when youa re forced to run dump database because dump transaction is not allowed, when, for example, any minimally logged command is performed). The with shrink_log option of dump database removes holes at the end of the database, regardless of whether the database is in a dump sequence or not..
with verify[= header | full] – allows the Backup Server 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. Any faults found are reported in the backupserver error log.
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..
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. See Managing Adaptive Server Logins, Database Users, and Client Connections in the System Administration Guide, Volume 1.
retaindays = number_days – (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.
Note: This option applies only when dumping to a disk; it does not apply to 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, the SAP ASE 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. 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.
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 using the dmp_cfg2 dump configuration:
```
dump database testdb using config = dmp_cfg2
```
Example 2 – Dumps the database using the dmp_cfg2 dump configuration. The archive files created as part of the dump operation are password-protected:
```
dump database testdb using config = dmp_cfg2 
    with passwd='mypass01'
```
Note: The password must be in single or double quotes.
Example 3 – Performs a database dump using the dmp_cfg2 dump configuration, explicitly specifying compression level 6, thereby overriding the compression level that was configured in dmp_cfg2:
```
dump database testdb using config = dmp_cfg2
    with compression=6
```
Example 4 – 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 5 – (UNIX only) 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. 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 6 – Initializes the tape volume, overwriting any existing files:
```
dump database pubs2
    to "/dev/nrmt0"
    with init
```
Example 7 – Rewinds the dump volumes upon completion of the dump:
```
dump database pubs2
    to "/dev/nrmt0"
    with unload
```
Example 8 – (UNIX only) 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 9 – 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 10 – 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 11 – Dumps the pubs2 database to the TSM backup object “obj1.1”:
```
dump database pubs2 to "syb_tsm::obj1.1"
```

Example 12 – 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"

Example 13 – Removes the last fragment in sales_db1, which is a database hole at the end of the database.

select * indicates there is a hole at the end of the database:

select * from sysusages where dbid=db_id("sales_db1")
go

 dbid segmap lstart  size vstart location unreservedpgs  crdate            vdevno
 ---- ------ ------- ---- ------ -------- -------------- ----------------- ------
    5      3      0  1536   1536        0           598  May 5 2011 2:59PM      3
    5      4   1536  1536   1536        0          1530  May 5 2011 2:59PM      4
    5      0   3072  1536   3072        4          1526  May 5 2011 2:59PM     -5

dump database sales_db1 to "/tmp/sales_db1.dmp" with shrink_log
go

Backup Server session id is: 42. Use this value when executing the 'sp_volchanged' system 
stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /tmp/sales_db1.dmp.
Backup Server: 6.28.1.1: Dumpfile name 'sales_db1111250D8E6  ' section number 1 mounted
on disk file '/tmp/sales_db1.dmp'
Backup Server: 4.188.1.1: Database sales_db1: 892 kilobytes (55%) DUMPED.
Backup Server: 4.188.1.1: Database sales_db1: 934 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database sales_db1: 942 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database sales_db1).

Run select * to confirm that the fragment is successfully removed:

select * from sysusages where dbid=db_id("sales_db1")
go

 dbid segmap lstart  size vstart location unreservedpgs  crdate            vdevno
 ---- ------ ------- ---- ------ -------- -------------- ----------------- ------
    5      3      0  1536   1536        0           598  May 5 2011 2:59PM      3
    5      4   1536  1536   1536        0          1530  May 5 2011 2:59PM      4

Example 14 – Verifies database new_dump before dumping it to the mydumpdev device:
```
dump database new_dump to mydumpdev with compression=x,verify=read_after_write
```
Example 15 – Performs a cyclic redundancy check and rereads every compressed block before dumping database new_dump to the mydumpdev device:
```
dump database new_dump to mydumpdev with compression=x,verify=read_after_write,verify=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.

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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Setting	Description
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:

Information	Values
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