Dump and load command syntax

The dump database, dump transaction, load database, and load transaction commands have parallel syntax. Routine dumps and loads require the name of a database and at least one dump device. The commands can also include the following options:

compress= to compress your dump files to a local file.
at server_name to specify the remote Backup Server
density, blocksize, and capacity to specify tape storage characteristics
dumpvolume to specify the volume name of the ANSI tape label
file = file_name to specify the name of the file to dump to or load from
stripe on stripe_device to specify additional dump devices
dismount, unload, init, and retaindays to specify tape handling
notify to specify whether Backup Server messages are sent to the client that initiated the dump or load, or to the operator_console

When a user database is dumped, its database options are not dumped because they are stored in the sysdatabases table of the master databases. This is not a problem if you load a previously dumped database onto itself because rows in sysdatabases describing this database still exist in master. However, if you drop the database before you perform the load database, or if you load the database dump on a new server, these database options are not restored. To restore the image of a user database, you must also re-create the database options.

Table 12-1 shows the syntax for routine database and log dumps, and for dumping the log after a device failure. It indicates what type of information is provided by each part of the dump database or dump transaction statement.

**Table 12-1: Syntax for routine dumps and log dumps after device failure**
Information provided	Routine database or log dump	Log dump after data segment device failure
Command	`dump {database \| transaction}`	`dump tran[saction]`
Database name	`database_name`	`database_name`
Compression to a local file (supported for backward compatibility)	`to [compress::[compression_level::]]`	`to [compress::[compression_level::]]`
Dump device	`stripe_device`	`stripe_device`
Remote Backup Server	`[at backup_server_name]`	`[at backup_server_name]`
Tape device characteristics	`[density = density, blocksize = number_bytes, capacity = number_kilobytes,`	`[density = density, blocksize = number_bytes, capacity = number_kilobytes,`
Volume name for single device	`dumpvolume = volume_name`	`dumpvolume = volume_name`
File name for single device	`file = file_name]`	`file = file_name]`
Characteristics of additional devices (up to 31 devices; one set per device)	`[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]]...`	`[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]]...`
Characteristics of all dump devices	`[with { density = density, blocksize = number_bytes, capacity = number_kilobytes,`	`[with { density = density, blocksize = number_bytes, capacity = number_kilobytes,`
Compression to a remote server (recommended syntax for compressed dumps)	`compression = compress_level`	`compression = compress_level`
Volume name for all devices	`dumpvolume = volume_name`	`dumpvolume = volume_name`
File name for all devices	`file = file_name,`	`file = file_name,`
Tape handling options	`[nodismount \| dismount], [nounload \| unload], retaindays = number_days, [noinit \| init],`	`[nodismount \| dismount], [nounload \| unload], retaindays = number_days, [noinit \| init],`
Password; not available for dump tran or load tran	`passwd = password,`
Option to dump only completed transactions; not available for dump database	`standby_access`
Option to not truncate log		`no_truncate`
Message destination	`, notify = {client \| operator_console}}]`	, notify = {client \| operator_console}}]

Table 12-2 shows the syntax for loading a database, applying transactions from the log, and returning information about dump headers and files.

**Table 12-2: Syntax for load commands**
Information provided	Load database or apply recent transactions	Return header or file information but do not load backup
Command	`load {database \| transaction}`	`load {database \| transaction}`
Database name	`database_name`	`database_name`
Compression to a local file. Supported for compatibility with older scripts and dumps.¹	`from [compress::]`	`from [compress::]`
Dump device	`stripe_device`	`stripe_device`
Remote Backup Server	`[at backup_server_name]`	`[at backup_server_name]`
Tape device characteristics	`[density = density, blocksize = number_bytes,`	`[density = density, blocksize = number_bytes,`
Volume name	`dumpvolume = volume_name,`	`dumpvolume = volume_name,`
File name	`file = file_name]`	`file = file_name]`
Characteristics of additional devices (up to 31 devices; one set per device)	`[stripe on [compress::]stripe_device [at backup_server_name] [density = density, dumpvolume = volume_name, file = file_name]...`	`[stripe on [compress::]stripe_device [at backup_server_name] [density = density, dumpvolume = volume_name, file = file_name]...`
Characteristics of all dump devices	`[with{ density = density, blocksize = number_bytes,`	`[with{ density = density, blocksize = number_bytes,`
Compression to a remote server.	`compression,`	`compression,`
Volume name for all devices	`dumpvolume = volume_name,`	`dumpvolume = volume_name,`
File name for all devices	`file = file_name,`	`file = file_name,`
Tape handling options	`[nodismount \| dismount], [nounload \| unload],`	`[nodismount \| dismount], [nounload \| unload],`
Password name, not available for load tran	`passwd = password,`	`passwd = password,`
Lists dump files		`listonly [= full],`
Provides header information		`headeronly,`
Message destination	`notify = {client \| operator_console}]]`	`notify = {client \| operator_console}`
Loads log up to a specified time in the log; load tran only	`until_time = datetime}]]`	`until_time = datetime}]]`

¹The older compress:: option is supported for backward compatibility. Sybase recommends that you use the compress= option during dump tran or dump database. No corresponding option is required during a load.

Table 12-3 shows the syntax for truncating a log:

**Table 12-3: Special dump transaction options**
Information provided	Truncate log on same segment as data	Truncate log without making a copy	Truncate log with insufficient free space
Command	`dump transaction`	`dump transaction`	`dump transaction`
Database name	`database_name`	`database_name`	`database_name`
Do not copy log	`with truncate_only`	`with truncate_only`	`with no_log`

Table 12-3: Special dump transaction options

Information provided

Truncate log on same segment as data

Truncate log without making a copy

Truncate log with insufficient free space

Command

dump transaction

Database name

database_name

Do not copy log

with truncate_only

with no_log

dump and load database display the percentage completed while they run. dump database displays the percentage completed for the database you are dumping and load database displays the percentage loaded for the target database.

The dump and load transaction commands do not display the percent completed.

For example, if you dump the sybsystemprocs database to a file named pubs2.dump, Adaptive Server displays:

dump database sybsystemprocs to "pubs2.dump"

Backup Server session id is:  13.  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
/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump.
Backup Server: 6.28.1.1: Dumpfile name 'pubs20805209785  ' section number 1
mounted on disk file '/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump'
Backup Server: 4.188.1.1: Database pubs2: 876 kilobytes (46%) DUMPED.
Backup Server: 4.188.1.1: Database pubs2: 1122 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 pubs2: 1130 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database pubs2).

When you load pubs2.dump into a database, Adaptive Server reports:

load database pubs2 from “pubs2.dump”

Backup Server session id is:  17.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pubs20805209785  ' section number 1
mounted on disk file '/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump'
Backup Server: 4.188.1.1: Database pubs2: 1880 kilobytes (45%) LOADED.
Backup Server: 4.188.1.1: Database pubs2: 4102 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database pubs2: 4110 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database pubs2).
Started estimating recovery log boundaries for database 'pubs2'.
Database 'pubs2', checkpoint=(1503, 22), first=(1503, 22), last=(1503, 22).
Completed estimating recovery log boundaries for database 'pubs2'.
Started ANALYSIS pass for database 'pubs2'.
Completed ANALYSIS pass for database 'pubs2'.
Started REDO pass for database 'pubs2'. The total number of log records to
process is 1.
Completed REDO pass for database 'pubs2'.
Use the ONLINE DATABASE command to bring this database online; ASE will not
bring it online automatically.

For dump database, the percentage displayed are estimated according to the total size of the database being dumped. Howeer, for load database, the percentage displayed is estimated according to the total size of the receiving database. For example, if you load a 50 megabyte database dump into a 100 megabyte database, the percentage completed are estimated according to the 100 megabyte database, not the 50 megabyte dump.

The remainder of this chapter provides greater detail about the information specified in dump and load commands and volume change messages. Routine dumps and loads are described first, followed by log dumps after device failure and the special syntax for truncating logs without making a backup copy.

Dump and load command syntax

Percent dump and load complete