SELECT
resh.destination_database_name,
resh.restore_date,
bckst.backup_start_date,
bckst.backup_finish_date,
bckst.database_name as sourceDBName,
bmf.physical_device_name as backupFileUsed,
bckst.*
FROM msdb..restorehistory resh
INNER JOIN msdb..backupset bckst ON resh.backup_set_id = bckst.backup_set_id
INNER JOIN msdb..backupmediafamily bmf ON bckst.media_set_id = bmf.media_set_id
WHERE resh.destination_database_name = '**Database Name**'
ORDER BY resh.restore_date DESC
Most of time i was asked which database bacup has been restore on particular database, who restore the database, what is machine name, which backup file used, where the backup has been placed.
Donot worry about this sqlserver store all of this information as a metadata so above query is capable of give answers of what/when/where 3W/5W
Execute above query on any of database query window, add filder of database name on which you want to find all these questions. make sure user have the rights of executing query in cross databases.
resh.destination_database_name,
resh.restore_date,
bckst.backup_start_date,
bckst.backup_finish_date,
bckst.database_name as sourceDBName,
bmf.physical_device_name as backupFileUsed,
bckst.*
FROM msdb..restorehistory resh
INNER JOIN msdb..backupset bckst ON resh.backup_set_id = bckst.backup_set_id
INNER JOIN msdb..backupmediafamily bmf ON bckst.media_set_id = bmf.media_set_id
WHERE resh.destination_database_name = '**Database Name**'
ORDER BY resh.restore_date DESC
Most of time i was asked which database bacup has been restore on particular database, who restore the database, what is machine name, which backup file used, where the backup has been placed.
Donot worry about this sqlserver store all of this information as a metadata so above query is capable of give answers of what/when/where 3W/5W
Execute above query on any of database query window, add filder of database name on which you want to find all these questions. make sure user have the rights of executing query in cross databases.