restorehistory (Transact-SQL)

Contains one row for each restore operation. This table is stored in the msdb database.

Column name

Data type

Description

restore_history_id

int

Unique identification number that identifies each restore operation. Identity, primary key.

restore_date

datetime

Date and time of the completion of the restore operation. Can be NULL.

destination_database_name

nvarchar(128)

Name of the destination database for the restore operation. Can be NULL.

user_name

nvarchar(128)

Name of the user who performed the restore operation. Can be NULL.

backup_set_id

int

Unique identification number identifying the backup set being restored. References backupset(backup_set_id).

restore_type

char(1)

Type of restore operation:

D = Database

F = File

G = Filegroup

I = Differential

L = Log

V = Verifyonly

Can be NULL.

replace

bit

Indicates whether the restore operation specified the REPLACE option:

1 = Specified

0 = Not specified

Can be NULL.

When a database is reverted to a database snapshot, 0 is the only option.

recovery

bit

Indicates whether the restore operation specified the RECOVERY or NORECOVERY option:

1 = RECOVERY

Can be NULL.

When a database is reverted to a database snapshot, 1 is the only option.

0 = NORECOVERY

restart

bit

Indicates whether the restore operation specified the RESTART option:

1 = Specified

0 = Not specified

Can be NULL.

When a database is reverted to a database snapshot, 0 is the only option.

stop_at

datetime

Point in time to which the database was recovered. Can be NULL.

device_count

tinyint

Number of devices involved in the restore operation. This number can be less than the number of media families for the backup. Can be NULL.

When a database is reverted to a database snapshot, the number is always 1.

stop_at_mark_name

nvarchar(128)

Indicates recovery to the transaction containing the named mark. Can be NULL.

When a database is reverted to a database snapshot, this value is NULL.

stop_before

bit

Indicates whether the transaction containing the named mark was included in the recovery:

0 = Recovery halted before marked transaction.

1 = Recovery included marked transaction.

Can be NULL.

When a database is reverted to a database snapshot, this value is NULL.

Remarks

To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.

See Also

Reference

restorefile (Transact-SQL)

restorefilegroup (Transact-SQL)

System Tables (Transact-SQL)

Other Resources

Backup and Restore Tables (Transact-SQL)