Compressing a dump

The dump command includes two options that allow you to compress databases and transaction logs using Backup Server, thereby reducing your space requirements for your archived databases. The parameters are:

compress::[compression_level::] – compresses to a local file. Causes the Backup Server to invoke a external filter, and is supported for backward compatibility.
with compress=compress_level – compresses to a remote server. Causes the Backup Server to use its own native compression method. This is the preferred compression option.

Table 12-5 shows the syntax for compress= and with compress= commands:

**Table 12-5: Indicating the database name and dump device**
	Backing up a database or log	Loading a database or log
	`dump {database \| tran} database_name to`	`load {database \| tran} database_name from`
Compress to local file (supported for backward compatibility)	`[compress::[compression_level:: ]]`	`[compress::]`
	`stripe_device [at backup_server_name] [density = density, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] [stripe on`	`stripe_device [at backup_server_name] [density = density, blocksize = number_bytes, dumpvolume = volume_name, file = file_name] [stripe on`
	`[compress::[compression_level::]]` `stripe_device [at backup_server_name] [density = density, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] ...] [with{ density = density, blocksize = number_bytes, capacity = number_kilobytes,`	[compress::] `stripe_device [at backup_server_name] [density = density, dumpvolume = volume_name, file = file_name] ...] [with{ density = density, blocksize = number_bytes,`
Compress to remote machine	`compression = compress_level,`	`compression,`
	`dumpvolume = volume_name, file = file_name, [nodismount \| dismount], [nounload \| unload], passwd = password, retaindays = number_days, [noinit \| init], standby_access [notify = {client \| operator_console}]}]`	`dumpvolume = volume_name, file = file_name, [nodismount \| dismount], [nounload \| unload], passwd = password, [notify = {client \| operator_console}] until_time = datetime}]]`

Syntax

The partial syntax specific to dump database … compress= and dump transaction … compress= is:

dump database database_name
	to compress=[compression_level] stripe_device
	…[stripe on compress::[compression_level::]stripe_device] …

dump transaction database_name
	to compress=[compression_level] stripe_device
	…[stripe on compress::[compression_level::]stripe_device]…

Where:

database_name – is the database you are loading into
compress=compression_level – is a number between 0 and 9, with 0 indicating no compression, and 9 providing the highest level of compression. If you do not specify compression_level, Adaptive Server does not compress the dump.
compress::compression_level – is included to support backward compatibility. The compress=compression_level option is the preferred syntax to compress a dump.
stripe_device – is the full path to the archive file of the database or transaction log you are compressing. If you do not include a full path for your dump file, Adaptive Server creates a dump file in the directory in which you started Adaptive Server.

Use the stripe on clause to use multiple dump devices for a single dump. See “Specifying additional dump devices: the stripe on clause” for more information about the stripe on clause.

The older compress:: option works only with local archives; you cannot use the servername option. To compress to a remote machine, you must use the preferred compress=compress_level option.

The compress= parameter of the dump command allows you to reduce your space requirements for your archived databases. With Adaptive Server 12.5.2 and later, the compress= parameter enables you to compress your dumps to a remote machine.

If you use the older compress:: option, you need not include the compression level when you load the database dump. However, you can issue load with listonly=full to determine the compression level at which the dump was made.

If you use the native compress= option, you need not include the compress= option when you load the database dump.

The partial syntax for dump database is:

dump database database_name to file_name 
	[ with compression=compress_level]

where:

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.
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. The pathname cannot exceed 255 characters.
compress_level – is a number between 1 and 9, with 9 providing the highest level of compression. There is no default compression level; if you do not specify a compress_level, Adaptive Server does not compress the dump.

Examples

Example 1 Dumps the pubs2 database into a compressed file:

dump database pubs2 to device_options...compress=4

The compression_level must be a number between 0 and 9. The compress= option does not recognize numbers outside this range. For example, the following syntax creates a file which is compressed with a compression level of 9:

dump database pubs2 to device_options...compress=9

In general, the higher the compression numbers, the smaller your archives are compressed. However, the compression result depends on the actual content of your files.

Table 12-6 shows the compression levels for the pubs2 database. These numbers are for reference only; the numbers for your site may differ depending on OS level and configuration.

**Table 12-6: Compression levels and compressed file sizes for pub2**
Compression levels	Compressed file size
No compression/Level 0	630K
Level 1	128K
Level 2	124K
Level 3	121K
Level 4	116K
Level 5	113K
Level 6	112K
Level 7	111K
Level 8	110K
Level 9	109K

The higher the compression level, the more CPU-intensive the process is.

For example, you may not want to use a level-9 compression when archiving your files. Instead, consider the trade-off between processing effort and archive size. Compression level 6 provides optimal CPU usage, producing an archive that is 60 – 80 percent smaller than a regular uncompressed archive. Sybase recommends that you initially use compression level 6, then increase or decrease the level based on your performance requirements.

Example 2 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 remote_machine 
with compression =”4”

For complete syntax information about dump database and dump transaction, see the Reference Manual: Commands.