sp_iqrestoreaction Procedure

Shows what restore actions are needed to bring database to a consistent state with a given past date.

Syntax

sp_iqrestoreactiontimestamp ]

Usage

Parameters

Parameter

Description

timestamp

Specifies the past date target.

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

sp_iqrestoreaction returns an error if the database cannot be brought to a consistent state for the timestamp. Otherwise, suggests restore actions that will return the database to a consistent state.

The common point to which the database can be restored coincides with the last backup time that backed up read-write files just before the specified timestamp. The backup may be all-inclusive or read-write files only.

Output may not be in exact ascending order based on backup time. If a backup archive consists of multiple read-only dbfiles, it may contain multiple rows (with the same backup time and backup id).

If you back up a read-only dbfile or dbspace multiple times, the restore uses the last backup. The corresponding backup time could be after the specified timestamp, as long as the dbspace/dbfile alter ID matches the dbspace/dbfile alter ID recorded in the last read-write backup that is restored.

sp_iqrestoreaction returns the following:

sp_iqrestoreaction columns

Column name

Description

sequence_number

Orders the steps to be taken

backup_id

Identifier for the backup transaction

backup_archive_list

List of archive files in the backup

backup_time

Time of the backup taken

virtual_type

Type of virtual backup: “Non-virtual,” “Decoupled,” or “Encapsulated”

restore_dbspace

Can be empty. Indicates that all dbspaces are to be restored from the backup archive

restore_dbfile

Could be empty. Indicates that all dbfiles in the given dbspace are to be restored from the backup archive

backup_comment

User comment

Example

Sample output of sp_iqrestoreaction:

sequence_number   backup_id   backup_archive_list    backup_time
             1         1192   c:\\\\temp\\\\b1       2008-09-23 14:47:40.0
             2         1201   c:\\\\temp\\\\b2.inc   2008-09-23 14:48:05.0l
             3         1208   c:\\\\temp\\\\b3.inc   2008-09-23 14:48:13.0

virtual_type   restore_dbspace   restore_dbfile   backup_comment
Nonvirtual
Nonvirtual
Nonvirtual