SQLServerWiki

“The Only Thing That Is Constant Is Change”

Last Restored Backup for all the User databases.

Posted by database-wiki on February 7, 2017

WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
r.restore_date,
RIGHT(m.physical_device_name , CHARINDEX(‘\’, REVERSE(m.physical_device_name )) -1) [file_name],
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id
inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id
inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: