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.

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.

See Using Backup Server with IBM Tivoli Storage Manager for dump database syntax when the Tivoli Storage Manager is licensed at your site.

Syntax

dump database database_name
	using config[uration] = 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 {
		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) 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

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'

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

The init option 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) 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 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

Usage

• The maximum file path/name size for a physical device is 127 characters.

• If a database has proxy tables, the proxy tables are a part of the database save set. The content data of proxy tables is not included in the save; only the pointer is saved and restored.

• You cannot remove holes that are not at the end of the database using the with shrink_log option.

• You cannot mix Sybase dumps and non-Sybase data (for example, UNIX archives) on the same tape.

• If a database has cross-database referential integrity constraints, the sysreferences system table stores the name—not the ID number—of the external database. Adaptive Server cannot guarantee referential integrity if you use load database to change the database name or to load it onto a different server.

  WARNING! Before dumping a database to load it with a different name or move it to another Adaptive Server, use alter table to drop all external referential integrity constraints.

• You cannot use dump database in a user-defined transaction.

• If you issue dump database on a database where a dump transaction is already in progress, dump database sleeps until the transaction dump completes.

• When using 1/4-inch cartridge tape, you can dump only one database or transaction log per tape.

• You cannot dump a database if it has offline pages. To force offline pages online, use sp_forceonline_db or sp_forceonline_page.

• Before you run dump database, for a cross platform dump and load, move the database to a transactional quiescent status:

  1. Verify the database runs cleanly by executing dbcc checkdb and dbcc checkalloc.

  2. To prevent concurrent updates from open transactions by other processes during dump database, use sp_dboption to place the database in a single-user mode.

  3. Flush statistics to systabstats using sp_flushstats.

  4. Wait for 10 to 30 seconds, depending on the database size and activity.

  5. Run checkpoint against the database to flush updated pages.

  6. Run dump database.

• dump transaction and load transaction are not allowed across platforms.

• dump database and load database to or from a remote backupserver are not supported across platforms.

• You cannot load a password-protected dump file across platforms.

• If you perform dump database and load database for a parsed XML object, you must parse the text again after the load database is completed.

• Adaptive Server cannot translate embedded data structures stored as binary, varbinary, or image columns.

• load database is not allowed on the master database across platforms.

• Stored procedures and other compiled objects are recompiled from the SQL text in syscomments at the first execution after the load database.

  If you do not have permission to recompile from text, then the person who does must recompile from text using dbcc upgrade_object to upgrade objects.

  If you migrate login records in the syslogins system table in the master database from Solaris to Linux, you can perform a bcp -c character format bulk copy, and the login password from Solaris is compatible on Linux. For all other combinations and platforms, login records must be re-created because the passwords are not compatible.

• Adaptive Server database dumps are dynamic—they can take place while the database is active. However, they may slow the system down slightly, so you may want to run dump database when the database is not being heavily updated.

• Back up the master database regularly and frequently. In addition to your regular backups, dump master after each create database, alter database, and disk init command is issued.

• Back up the model database each time you make a change to the database.

• Use dump database immediately after creating a database, to make a copy of the entire database. You cannot run dump transaction on a new database until you have run dump database.

• Each time you add or remove a cross-database constraint or drop a table that contains a cross-database constraint, dump both of the affected databases.

  WARNING! Loading earlier dumps of these databases can cause database corruption.

• Develop a regular schedule for backing up user databases and their transaction logs.

• Use thresholds to automate backup procedures. To take advantage of Adaptive Server last-chance threshold, create user databases with log segments on a device that is separate from data segments. For more information about thresholds, see the System Administration Guide.

• The master, model, and sybsystemprocs databases do not have separate segments for their transaction logs. Use dump transaction with truncate_only to purge the log, then use dump database to back up the database.

• Backups of the master database are needed for recovery procedures in case of a failure that affects the master database. See the System Administration Guide for step-by-step instructions for backing up and restoring the master database.

• If you are using removable media for backups, the entire master database must fit on a single volume, unless you have another Adaptive Server that can respond to volume change messages.

• You can specify the dump device as a literal, a local variable, or a parameter to a stored procedure.

• You cannot dump to the null device (on UNIX, /dev/null ).

• Dumping to multiple stripes is supported for tape and disk devices. Placing multiple dumps on a device is supported only for tape devices.

• You can specify a local dump device as:

  • A logical device name from the sysdevices system table

  • An absolute path name

  • A relative path name

  Backup Server resolves relative path names using the current working directory in Adaptive Server.

• When dumping across the network, you must specify the absolute path name of the dump device. The path name must be valid on the machine on which Backup Server is running. If the name includes any characters except letters, numbers, or the underscore (_), you must enclose it in quotes.

• Ownership and permissions problems on the dump device may interfere with the use of dump commands. sp_addumpdevice adds the device to the system tables, but does not guarantee that you can dump to that device or create a file as a dump device.

• You can run more than one dump (or load) at the same time, as long as each uses different dump devices.

• If the device file already exists, Backup Server overwrites it; it does not truncate it. For example, suppose you dump a database to a device file and the device file becomes 10MB. If the next dump of the database to that device is smaller, the device file is still 10MB.

• 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.

• You must have a Backup Server running on the same machine as Adaptive 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.

• Dumping a database with the init option overwrites any existing files on the tape or disk.

• If you perform two or more dumps to a tape device and use the same file name for both dumps (specified with the FILENAME parameter), Adaptive Server appends the second dump to the archive device. You cannot restore the second dump, because Adaptive Server locates the first instance of the dump image with the specified file name and restores this image instead. Adaptive Server does not search for subsequent dump images with the same file name.

• Backup Server sends the dump file name to the location specified by the with notify clause. Before storing a backup tape, the operator should label it with the database name, file name, date, and other pertinent information. When loading a tape without an identifying label, use the with headeronly and with listonly options to determine the contents.

• Dump volumes are labeled according to the ANSI tape-labeling standard. The label includes the logical volume number and the position of the device within the stripe set.

• During loads, Backup Server uses the tape label to verify that volumes are mounted in the correct order. This allows you to load from a smaller number of devices than you used at dump time.

  When dumping and loading across the network, you must specify the same number of stripe devices for each operation.

• (UNIX systems) Backup Server requests a volume change when the tape capacity has been reached. After mounting another volume, the operator notifies Backup Server by executing sp_volchanged on any Adaptive Server that can communicate with Backup Server.

• If Backup Server detects a problem with the currently mounted volume, it requests a volume change by sending messages to either the client or its operator console. The operator can use the sp_volchanged system procedure to respond to these messages.

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.

• At the beginning of a dump database, Adaptive Server passes Backup Server the primary device name of all database and log devices. If the primary device has been unmirrored, Adaptive Server instead passes the name of the secondary device. If any named device fails before the Backup Server completes its data transfer, Adaptive Server aborts the dump.

• If a user attempts to unmirror any of the named database devices while a dump database is in progress, Adaptive Server displays a message. The user executing the disk unmirror command can abort the dump or defer the disk unmirror until after the dump is complete.

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 that contain row identifiers (RIDs), are treated as binary to achieve 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, the order by which optimization was performed is invalidated, leading to an invalid index on user tables in a cross-platform dump and load.

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

• Nonclustered index on APL tables.

• Clustered index on DOL tables.

• Nonclustered index on DOL tables.

To fix indexes on the target system, after loading from a different architecture dump, either:

• Drop and re-create all of the indexes, or,

• Use sp_post_xpload. See “System Procedures” in Reference Manual: Procedures.

Re-creating indexes on large tables can be a lengthy process. sp_post_xpload validates indexes, drops invalid indexes, and re-creates dropped indexes, in a single command.

Using sp_post_xpload may take longer than dropping and re-creating indexes individually. Sybase recommends that you use the drop and re-create indexes on those databases larger than 10GB.

To use a compressed dump for an archive database:

• Create the compressed dump using the with compression = <compression level> option of the dump database or dump tran command.

• Create a memory pool for accessing the archive database.

The format of a compressed dump generated with the with compression = compression_level option has changed. Backup Server versions 15.0 ESD #2 and later generate a different format of compressed dump than earlier versions. Therefore:

• You can load compressed dump made using a Backup Server version 15.0 ESD #2 and later only into a pre-15.0 ESD #2 installation using a Backup Server version 15.0 ESD #2 or later.

• If you are using a pre-15.0 ESD #2 installation and want to use your dumps for an archive database, use Backup Server version 15.0 ESD #2 or later to create compressed database dumps.

  You can use a 15.0 ESD #2 Backup Server for both dump and loads.

dump and load work on the ciphertext of encrypted columns, ensuring 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:

• When you dump the database containing encrypted columns, you also dump the database where the key was created. This is necessary if new keys have been added since the last dump.

• When you dump the database containing an encryption key, dump all databases containing columns encrypted with that key. This keeps the encrypted data in sync with the available keys.

• After loading the database containing the encryption keys and the database containing the encrypted columns, bring both databases online at the same time.

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 reencrypt 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.

See user documentation for Tivoli Storage Manager for more information about creating backups when TSM is supported at your site.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are:

See also