The msdb database keeps a complete record of every SQL Server backup and restore procedure performed on a server instance.
The data for the SQL Server Agent jobs, job history, operators, and alarms is stored in this database. It includes information about log shipping settings, backup and restore history, and database activity. Additionally, it contains the data required to run Database Mail.
In order to verify the backup and restore operations history, you can query msdb.dbo.backupset, msdb.dbo.backupmediaset and msdb.dbo.backupmediafamily.
SELECT
backupset.[name]
, backupset.[description]
, [type]
, expiration_date
, is_compressed
, Device_Type
, [user_name]
, server_name
, [database_name]
, is_copy_only
, backup_start_date
, backup_finish_date
, backup_size
, compressed_backup_size
, physical_device_name
, [backup_set_id]
, backupset.media_set_id
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediaset ON backupset.media_set_id = backupmediaset.media_set_id
INNER JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE database_name = ‘YourDatabaseName’
The query above returns data about backups, while the one below returns data about restorations.
SELECT TOP 1 * FROM msdb.dbo.restorehistory ORDER BY restore_history_id DESC;
Hope you find this article helpful.
Big Data & SQL 