If you need to get the last time that a database is restored from log shipping based off the transaction log file name, here is a function to do so. It parses out the date from the "last_restored_file" column, then converts it from UTC to your regular time.
CREATE FUNCTION [dbo].[GetLogShippingFileDate]
(
@DatabaseName varchar(100)
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @Result smalldatetime
SELECT @Result =
CAST(SUBSTRING(fds,5,2)+’/’+SUBSTRING(fds,7,2)+’/’+LEFT(fds,4) + ‘ ‘ + SUBSTRING(fds,9,2) + ‘:’ + SUBSTRING(fds,11,2) AS SMALLDATETIME)
FROM (
SELECT secondary_database AS ‘DatabaseName’,
LEFT(RIGHT(last_restored_file,18),14) AS ‘fds’
FROM
msdb.dbo.log_shipping_secondary_databases
) a
WHERE DatabaseName = @DatabaseName
— convert the UTC file time to regular time
SELECT @Result = DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),@Result)
RETURN @Result
END