sp_dump_history

The original dump history file is copied to a new file named original_name.XXX, where XXX represents an increasing numerical value (001, 002, and so on).

Syntax

sp_dump_history [  @operation = {'list' | 'purge' | 'listfiles' | 'help'}]
	[, @until_time = 'date'] 
	[, @database_name = 'database_name']
	[, @dump_type = 
		{'DATABASE' | 'TRAN[SACTION]' | 'CONFIG[URATION]'}]
	[, @status = {'success' | 'fail'}]
	[, @file = 'filename']

Parameters

@operation = {'list' | 'purge' | 'listfiles' | 'help'} – is a required parameter that include these options:
- list – displays the records from the dump history file. By default, the list includes:
  - Database name – the server configuration name
  - Dump type
  - Total number of dump stripes
  - Compression level
  - Remote Backup Server name
  - Dump creation time
  - Error number – on SAP ASE
- purge – purges records from the dump history file. The records to be purged are selected based on criteria specified using the other sp_dump_history parameters.
- listfiles – displays the list of dump history file names.
- help – shows the syntax for sp_dump_history.
@until_time = 'date' – (optional) allows you to specify a date and time, with Backup Server purging all dump objects created before that time. By default, all dump records are purged.
@database_name = 'database_name' – (optional) is the name of the database that has dump records from the dump history file that are listed or purged. By default, all database dump records are included.
@dump_type' – (optional) specifies the type of dump record to be listed or purged. Valid types are:
- 'DATABASE' – database dump objects created by dump database.
- 'TRAN[SACTION]' – transaction dump objects created by dump transaction.
- 'CONFIG[URATION]' – server configuration objects created by dump configuration.
By default, all types of dump records are listed or purged.
@status = {'success' | 'fail'} – (optional) specifies with to list or purge the successful or failed dump records. By default, only successful dump records are included.
@file = 'filename' – (optional) specifies the name of the dump history file. You must specify the the path, or location, of the file as part of filename. The default location of the dump history file is $SYBASE/$SYBASE_ASE (%SYBASE%\%SYBASE_ASE% in Windows).

Examples

Example 1 – Lists all dump records from the dump history file:
```
sp_dump_history 'list'
go
```
Example 2 – Lists dump records of a specified database created before a specified time:
```
sp_dump_history 'list', "mar 20, 2010 10:51:43:866am", 
   'testdb'
go
```
Example 3 – Lists the transaction dump objects from the model database, specifying a full path for the dump history file:
```
sp_dump_history @operation='list', @database_name = "model",
     @dump_type='TRAN', @status = 'success',
     @file = '/john_machine/john/ASE/ASE-16_0/dumphist'
```

Usage

The default behavior for sp_dump_history with no parameters is to display the output from its list parameter.

The output for database and transaction dumps differs from that of configuration files.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with`manage dump configuration` privilege.
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:

Information	Values
Event
Audit option
Command or access audited
Information in `extrainfo`	Roles– current actve roles. Keywords or options – NULL. Previous value – NULL. Current value – NULL. Other information – all input parameters. Proxy information – original login name, if set proxy in effect.