restorehistory (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

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