Find the restore details of database in SQL Server
Below query will give the complete details of the database which are all restored from any specific db backups.
Use full query for tracking purpose instead checking logs and troubleshooting
/*Change Destination_database_name ='MYDB' with the DB whose information is needed */with RH (Destination_database_name,user_name,restore_type,replace,stop_at,device_count,backup_set_id)
from msdb..RESTOREHISTORY WITH (nolock) where DESTINATION_DATABASE_NAME = 'MYDB')select RH.Destination_database_name,RH.user_name,RH.restore_type,RH.replace,RH.stop_at,RH.device_count,BS.server_name Source_server_name,BS.Database_name Source_database_name, BS.recovery_model Source_recovery_model,
BS.Backup_size Source_backup_size,BS.Compatibility_level Source_DB_compatibility_level,BS.user_name Souce_db_user_name,
BS.Last_family_number Source_db_backup_sets,
BMF.physical_device_name Backup_file_used
from RH join msdb..backupset BS on RH.backup_set_id=BS.backup_set_idjoin msdb..backupmediafamily BMF on BS.media_set_id=BMF.media_set_id
Hope the above query helps in troubleshooting
Thanks
