After restoring several backups in SQL Server on different days, sometimes you need to know exactly which databases were restored and when. Is there a way in SQL Server to have the restore history? The short answer is that there are options to get the SQL database restore history. In this article, we will discuss different options to know the restore history of databases in SQL Server.
You can get the information related to database restore with the help of system tables. Restore history is a system table in the MSDB database. You can find this system table in the System Databases folder in the Object Explorer in SQL Server Management Studio (SSMS).
To check all the records in the restorehistory table, use the following T-SQL sentences.
USE msdb
GO
SELECT [restore_history_id]
,[restore_date]
,[destination_database_name]
,[user_name]
,[backup_set_id]
,[restore_type]
,[replace]
,[recovery]
,[restart]
,[stop_at]
,[device_count]
,[stop_at_mark_name]
,[stop_before]
FROM [msdb].[dbo].[restorehistory]
The following example shows how to check the information of a backup restored on a specified date and time.
SELECT [restore_history_id]
,[restore_date]
,[destination_database_name]
,[user_name]
,[backup_set_id]
,[restore_type]
,[replace]
,[recovery]
,[restart]
,[stop_at]
,[device_count]
,[stop_at_mark_name]
,[stop_before]
FROM [msdb].[dbo].[restorehistory]
where restore_date=‘2021-10-27 08:57:52.187’
In the above example, the ‘where’ sentence defines the time.
where restore_date=‘2021-10-27 08:57:52.187’
If you want to search restoration information per date and not time, the following ‘where’ clause can help.
where FORMAT(restore_date,’yyyy-MM-dd’)=‘2021-10-27’
If you want to search restoration information per time and not per date, the following example can help.
where FORMAT(restore_date,’hh:mm:ss‘)=’08:57:52‘
The restorehistory Columns
Now, we will provide more information about the columns.
- Restore_history_id is the primary key used by this table. This is an integer identifier.
- Restore_date is used to store the restoration date. It stores the date and time of the restoration. The data type is DateTime.
- Destination_database_name is the name of the database when it is restored. You can change the original name of the backup to a new name during the restoration process. The data type is nvarchar(128).
- User_name is the name of the user restoring the database. The data type is nvarchar(128).
- Backup_set_id is a foreign key to the dbo.backupset table. It is a number to identify the backup set used. This is an integer identifier.
- Restore_type is the way data will be restored. It is a char(1) data type. D means that we will restore the entire database. F means that we will restore specific files in the database. G means that we will restore filegroups. I means that we will restore differential backups. L means to restore Transaction Log files. V means to only verify that the backup does not have errors.
- Replace is an option that indicates if the restore will replace an existing database or not. If the value is 1, the restore process will replace the existing database. If the value is 0, the replace option is disabled. This value is a bit data type. It supports the value 1, 0, or NULL.
- Recovery option is used when you restore data. If the value is set to recovery, the database will be ready for the user. If this column is set to NORECOVERY, the database restored will not be available for the users. This option is used when you need to restore other backups. A value equal to 1 means RECOVERY and 0 means NORECOVERY. This column uses the bit data type.
- Restart is an option that contains 3 values – 1, 0, and NULL. 1 means that if the restoration fails, it will be restarted from the point where it was interrupted. 0 means that the restore operation will not be restarted, if it fails. This column is a bit data type.
- Stop_at is used when you want to recover your data at a given time. It is usually NULL because users commonly restore the entire backup set. The data type for this column is datetime.
- Device_count is a tiny data type. This stores the number of devices used in the restore operation.
- Stop_at_mark_name contains a mark name. This mark is used to restore the transaction until a named mark. The data type is nvarchar(128).
- The stop_before column is a bit. It returns 0 if you do not want to stop before the restore operation is completed and returns 1 if you want to stop before completion.
What to do if the backup is corrupt?
Sometimes, the backup gets corrupted and you receive error messages, like “The backup set is not valid” or “The media family on device stellar device is incorrectly formed”.
In such a situation, you can take the help of Stellar Repair for MS SQL Technician. This software can recover data from corrupt MS SQL database backup files. In addition, it can repair MS SQL database and recover all the data from the database. On the other hand, it can help recover MS SQL passwords. It supports all the MS SQL Server versions – MS SQL Server 2019 and earlier.