Restore Duration Time for Database

     The query below is to show the duration of Restore Time for a Database.  It is very useful for a big database restoration that we need to know how long the restoration is completed. Please notice this script for your daily DBA jobs :). Please set the value to database which you want to know restoration duration time --SET @databaseName = 'TM201707'

/*

Query - Restore Duration Time for Database

Uses MSDB..RestoreHistory table for Start time and ErrorLog for Finish Time

*/

-- Name of the Database you want restore name of:
DECLARE @databaseName VARCHAR(50)
SET @databaseName = 'TM201707'



DECLARE @TSQL NVARCHAR(2000)
DECLARE @lC INT
DECLARE @ErrorLogStart DATETIME
DECLARE @CurrentLogStart DATETIME
SET @CurrentLogStart = GETDATE()

-- Error Message for ErrorLog search
DECLARE @errorLogResult VARCHAR(4000)
SET @errorLogResult = 'Restore is complete on database ''' + @databaseName
    + '''.  The database is now available.'

-- Results Table
CREATE TABLE #Restore
    (
      [database] VARCHAR(50) ,
      StartTime DATETIME ,
      EndTime DATETIME
    )

INSERT  INTO #Restore
        ( [database] )
VALUES  ( @databaseName )

-- Get start time by looking msdb..restorehistory and selecting the first record with your db
UPDATE  #Restore
SET     StartTime = ( SELECT TOP ( 1 )
                                restore_date
                      FROM      msdb.dbo.restorehistory
                      WHERE     destination_database_name = @databaseName
                    )
WHERE   [database] IS NOT NULL

-- Get end time by loading the errorlog into a temp table and filtering to find the restore command @errorLog Result


-- SET ErrorLogStart Date to Restore Start Time
SET @ErrorLogStart = ( SELECT   StartTime
                       FROM     #Restore
                       WHERE    [database] = @databaseName
                     )

CREATE TABLE #TempLog
    (
      LogDate DATETIME ,
      ProcessInfo NVARCHAR(50) ,
      [Text] NVARCHAR(MAX)
    )

CREATE TABLE #logF
    (
      ArchiveNumber INT ,
      LogDate DATETIME ,
      LogSize INT
    )

INSERT  INTO #logF
        EXEC sp_enumerrorlogs
SELECT  @lC = MIN(ArchiveNumber)
FROM    #logF

WHILE @lC IS NOT NULL
    BEGIN
        IF EXISTS ( SELECT  1
                    FROM    #TempLog )
            BEGIN
                SET @CurrentLogStart = ( SELECT TOP ( 1 )
                                                LogDate
                                         FROM   #TempLog
                                         ORDER BY LogDate
                                       )
            END
        IF ( @CurrentLogStart > @ErrorLogStart )
            BEGIN
                INSERT  INTO #TempLog
                        EXEC sp_readerrorlog @lC
                SELECT  @lC = MIN(ArchiveNumber)
                FROM    #logF
                WHERE   ArchiveNumber > @lC
            END
        ELSE
            BEGIN
                BREAK
            END
    END


   
UPDATE  #Restore
SET     EndTime = ( SELECT TOP ( 1 )
                            LogDate
                    FROM    #TempLog
                    WHERE   ProcessInfo = 'Backup'
                            AND [Text] LIKE @errorLogResult
                  )
WHERE   [database] IS NOT NULL



-- Return the Restore information
SELECT  [database] ,
        StartTime ,
        EndTime ,
        DATEDIFF(MINUTE, StartTime, EndTime) AS 'Restore Duration in Minutes' ,
        DATEDIFF(SECOND, StartTime, EndTime) AS 'Restore Duration in Seconds'
FROM    #Restore

-- Clean up
DROP TABLE #Restore
DROP TABLE #TempLog
DROP TABLE #logF

Sample Output:

Reference: https://sqlnotesfromtheunderground.wordpress.com/2014/10/02/restore-duration-time-for-database/