Hi, How can we know that a database has been restored
a) From which database backup ?
b) On which date ?
Regards RajRam.
asked
How to Know DB properties
You can you the Backup/Restory table that SQL Server stores in the msdb database.
http://msdn.microsoft.com/en-us/library/ms188653%28SQL.90%29.aspx
I think this is what you need;
USE [msdb]
go
SELECT
[rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM
msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY
[rs].[restore_date] DESC
Courtesy of Thomas LaRock at http://www.mssqltips.com/tip.asp?tip=1860&ctc
