sp_dump_history

Description

Allows you to purge records from the dump history file. The original dump history file is saved with the timestamp suffixed to the file name.

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 Adaptive Server

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) is the name of the dump history file. By default, the configured dump history file name is used.

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

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.

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