Shows what restore actions are needed to bring database to a consistent state with a given past date.
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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:
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 |
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