Merhaba;

Aşağıda yazmış olduğum script ile serverda bulunan tüm databaselerin en son restore bilgilerini elde edebilirsiniz.

Serverda bulunmayan databaselerin bilgilerini almak için ise scriptin alt satırlarında bulunan “sys.databases”  JOIN modelini LEFT JOIN yapabilirsiniz.
Eğer tek bir databasein bilgilerini istiyorsanız “SET @dbname = NULL” parametresine db ismini yazmanız yeterli.

DECLARE @dbname SYSNAME
------------
SET @dbname = NULL
------------
SELECT tbl.*
FROM ( SELECT ROW_NUMBER() over (partition BY destination_database_name
 ORDER BY restore_date DESC) AS [row] ,
 destination_database_name AS 'Database Name',
 rsh.[user_name] AS [Restored By],
 CASE restore_type
 WHEN NULL THEN 'NULL'
 WHEN 'D' THEN 'Database'
 WHEN 'F' THEN 'File'
 WHEN 'G' THEN 'Filegroup'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Log File'
 WHEN 'V' THEN 'Verifyonly'
 WHEN 'R' THEN 'Revert'
 END AS 'Restore Type',
 CASE [replace]
 WHEN NULL THEN 'NULL'
 WHEN 1 THEN 'YES'
 WHEN 0 THEN 'NO'
 END AS 'DB Replaced',
 restore_date AS 'Date Restored',
 bmf.physical_device_name AS [Restored From],
 rf.destination_phys_name AS [Restored To (.MDF)]
 FROM msdb..restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
 WHERE destination_database_name = CASE WHEN @dbname IS NOT NULL THEN @dbname ELSE destination_database_name END
 AND rf.destination_phys_name LIKE '%.mdf' ) AS tbl
JOIN sys.databases d ON tbl.[Database Name] = d.name
WHERE tbl.[row] = 1
ORDER BY tbl.[Date Restored] DESC