sp_config_dump

Description

Allows you to list, add, or change dump configurations.

Syntax

sp_config_dump
	[@config_name = ‘configuration_name’
	[, {
		[@stripe_dir = ’stripe_dir_name’,]
		[@ext_api = ’external_api’,]
		[@num_stripes = ’number_of_stripes’,]
		[@retry = ’number_of_retries’,]
		[@blocksize = ’number_of_bytes’,]
		[@compression = ’compression_level’,]
		[@retain_days = ’number_of_days’,]
		[@init = ’[noinit | init]’,]
		[@verify = ’[header | full]’,]
		[@notify = ’[client | operator_console]',]
		[@backup_srv_name = backup_server_name’,]
		} | [‘delete’]
	] ]

Parameters

@config_name = ‘configuration_name’: is a unique dump configuration name that is required for adding or changing any specific dump configuration. Adaptive Server lists all dump configurations when you do not include 'configuration_name'. Additional parameters, when supplied, are changed to new values.

@stripe_dir = ’stripe_dir_name’: is the directory in which files are archived during the dump operation. Archived files are typically named using this format:

database_name.dump_type.date-timestamp.stripeID

@stripe_dir defaults to the directory where the Backup Server is started.

@ext_api = ’external_api’: is the name of the external API (byte stream device) used for the dump operation. By default, this parameter is unused. Provide external_api in this format:

external_API_name::additional_options

@num_stripes = ’number_of_stripes’: is the number of stripe devices used during the dump operation. The default is 1.

@retry = ’number_of_retries’: is the number of times the server tries the dump operation for nonfatal errors. Valid values are 0 to 5; the default is 0 (which indicates no retry).

@blocksize = 'number_of_bytes': is the block size for the dump device, overriding the default block size for the device. The value must be at least 1 database page (2048 bytes for most systems), and an exact multiple of the database page size. For optimal performance, specify blocksize as a power of 2 (such as 65,536, 131,072, or 262,144).

@compression = 'compression_level': is the compression level for compressed dumps. By default, compression is disabled.

@retain_days = 'number_of_days': is the number of days that Backup Server prevents a dump from being overwritten. Backup Server requires you to confirm any overwrite requests on an unexpired volume. By default, value is 0, meaning dumps can be overwritten.

@init = '[noinit | init]': specifies whether to initialize the volume. The default is noinit.

@verify = '[header | full]': specifies whether you want Backup Server to perform a minimal page-header or full structural row check on the data pages as they are copied to archives. There is no structural check made to global allocation map (GAM), object allocation map (OAM), allocation pages, indexes, text, or log pages. By default, there is no verification of data pages during archiving.

@notify = ’[client | operator_console]': specifies whether Backup Server routes messages to the client terminal that initiated the dump, or to the operator-console terminal where the Backup Server is running.

@backup_srv_name = 'backup_server_name’: specifies the network name of the remote Backup Server running on the machine to which the dump device is attached. Do not use backup_server_name to dump to SYB_BACKUP, the default Backup Server. You can specify up to 32 remote Backup Servers using this option.

For platforms that use interfaces files, the Backup Server name must appear in the interfaces file.

'delete': specifies the dump configuration to be deleted.

Examples

Example 1

Lists all dump configurations:

sp_config_dump
go

Configuration name
-------------------
dmp_cfg1
dmp_cfg2
dmp_cfg3

Example 2

Lists parameter values for a dump configuration called dmp_cfg1:

sp_config_dump 'dmp_cfg1'
go

Dump configuration: dmp_cfg1

Option name           Option value
-----------           -------------
compression          5
num_stripes          3
stripe_dir           /work/dump_dir

Example 3

Creates a new dump configuration called dmp_cfg2 that specifies that a dump operation creates 5 stripes in the /work1/dmp_dir stripe directory, and that retries once if it fails with a nonfatal error:

sp_config_dump 'dmp_cfg2', 
   @stripe_dir='/work1/dmp_dir', @num_stripes='5', 
   @retry='1'

Example 4

Changes the stripe directory of an existing dump configuration:

sp_config_dump 'dmp_cfg2', 
   @stripe_dir='/work2/dmp_dir'

Example 5

Deletes a dump configuration:

sp_config_dump 'dmp_cfg2', 'delete'

Usage

Permissions

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

Granular permissions enabled	With granular permissions enabled, you must be a user with manage dump configuration privilege.
Granular permissions disabled	With granular permissions disabled, you must be a user with sa_role or oper_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
exec_procedure		Execution of procedure	Roles – current active roles. Keywords or options – NULL. Previous value – NULL. Current value – NULL. Other information – all input parameters. Proxy information – original login name, if set proxy is enabled.