SQL DBA: Function To Get Database Last Restore Time (From Log Shipping Filename)

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