SQLServerWiki

“The Only Thing That Is Constant Is Change”

Query to Monitoring Log Shipping on secondary instance.

Posted by database-wiki on February 7, 2017

WITH LastRestores AS

(

SELECT

DatabaseName = [d].[name] ,

r.restore_date,

bs.server_name, m.physical_device_name as backup_device, b.physical_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: