restorehistory (Transact-SQL)

Applies to: yesSQL Server (all supported versions)

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

Backup and Restore Tables (Transact-SQL)
restorefile (Transact-SQL)
restorefilegroup (Transact-SQL)
System Tables (Transact-SQL)