Just a real quick post... I took on a new project very recently, and I was doing an assessment of their farm. Last DBCC's, last backups... even last restores. I had several different statements, but I returned multiple restore dates, when the database data and log file were restored in separate operations. This statement takes that into account, and just gives you back a single entry, for the most recent restore date for your databases. It also tells you from what file they were restored. Check it out. Let me know what you think.
/*
When was your database last restored?
From what file? */
WITH LastRestored AS
(
SELECT
rh.destination_database_name [DatabaseName],
rh.restore_date [Restored],
bmf.physical_device_name [SourceFile],
RowNum = ROW_NUMBER() OVER (PARTITION BY bs.database_name
ORDER BY rh.restore_date DESC )
FROM
msdb..restorehistory rh INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id INNER JOIN msdb..backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
)
SELECT DatabaseName,Restored,SourceFile
FROM LastRestored
WHERE RowNum = 1
Your results will look a little something like this:
DatabaseName Restored SourceFile
AdventureWorks2012 2015-01-20 21:59:41.130 C:\Backups\AdventureWorks2012_Backup.bak
ReportServer 2015-01-20 11:39:33.853 C:\SSRS\ReportServerLog.bak
ReportServerTempDB 2015-01-20 11:39:43.433 C:\SSRS\ReportServerTempDBLog.bak
Here are a couple of references with greater detail on the tables I am targeting:
RestoreHistory https://msdn.microsoft.com/en-us/library/ms187408(v=sql.110).aspx
BackupSet https://msdn.microsoft.com/en-us/library/ms186299.aspx
BackupMediaFamily https://msdn.microsoft.com/en-us/library/ms190284(v=sql.110).aspx
No comments:
Post a Comment