Geeky/Programming SQLServerPedia Syndication

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

    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’
    ) a
    WHERE DatabaseName = @DatabaseName

    — convert the UTC file time to regular time

    RETURN @Result



By Steve Novoselac

Director of Digital Technology @TrekBikes, Father, Musician, Cyclist, Homebrewer

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.