Where can I find the restore duration?

Question

I can't seem to find the same level of granularity for SQL Server restores that we can for backups? I see the restore start date/time in msdb.dbo.restorehistory but not start and stop date and times. Does it exist?

Answer 1

Please use this with caution. Especially reading registry via tsql is not allowed in many company policy, or recommended in many case. This script assumes you have enough SQL error log retention. Especially the error log from the time you database restore was done. I cannot remember if found this query from a open source or I wrote this. If you find it somewhere else let me know and I will add the source.

USE [MSDB];
GO
SET NOCOUNT ON;
DECLARE @sqlversion VARCHAR(10) ,
    @restore_finish_time DATETIME ,
    @reccount INT ,
    @maxerrorlogtoscan INT ,
    @Lognumber TINYINT;

--Checking how many error log is retained in this instance by reading registry
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',  @maxerrorlogtoscan OUTPUT; 

--If registry value is null  set it to 6
IF @maxerrorlogtoscan IS NULL
    SET @maxerrorlogtoscan = 6;

SET @reccount = 0;
SET @Lognumber = 0;
SET @sqlversion = RTRIM(LTRIM(SUBSTRING(@@version, 22, 5)));

IF OBJECT_ID('tempdb..#dbrestored') IS NOT NULL
    BEGIN
        DROP TABLE #dbrestored;
    END;

CREATE TABLE #dbrestored
    (
      RowID INT IDENTITY
                PRIMARY KEY ,
      logdate DATETIME ,
      dbname VARCHAR(40) ,
      infotext VARCHAR(4000)
    );

IF @@version LIKE '%2000%'
    OR @@version LIKE '%2005%'
    BEGIN
        PRINT 'SQL 2000 and 2005 are not supported';

    END; -- SQL 2000
ELSE -- SQL 2005 or higher
    BEGIN

        WHILE @Lognumber <= @maxerrorlogtoscan
            BEGIN
                INSERT  INTO #dbrestored
                        EXEC xp_readerrorlog @Lognumber, 1,
                            N'Restore is complete',
                            N'The database is now available.';
                SET @Lognumber = @Lognumber + 1;
            END;

        UPDATE  a
        SET     a.dbname = SUBSTRING(b.infotext, 34,
                                     ( CHARINDEX('''', b.infotext, 34) - 34 ))
        FROM    #dbrestored a
                JOIN #dbrestored b ON a.RowID = b.RowID;

    END;

SELECT  AA.destination_server_name ,
        AA.destination_database_name ,
        AA.restore_start_time ,
        AA.restore_finish_time ,
        LEFT(CONVERT(VARCHAR(20), AA.restore_finish_time
             - AA.restore_start_time, 108), 8) AS [RestoreDur hh:mm:ss] ,
        AA.source_server_name ,
        AA.source_database_name ,
        AA.backup_start_date ,
        AA.backup_finish_date
FROM    ( SELECT    @@ServerName AS destination_server_name ,
                    #dbrestored.[dbname] AS [destination_database_name] ,
                    rs.[restore_date] AS restore_start_time ,
                    #dbrestored.logdate AS restore_finish_time ,
                    bs.[server_name] AS [source_server_name] ,
                    bs.[database_name] AS [source_database_name] ,
                    bs.[backup_start_date] ,
                    bs.[backup_finish_date] ,
                    ROW_NUMBER() OVER ( PARTITION BY [destination_database_name] ORDER BY restore_date DESC ) ROWNUM
          FROM      [dbo].[restorehistory] rs
                    JOIN [dbo].[backupset] bs ON rs.[backup_set_id] = bs.[backup_set_id]
                    JOIN [dbo].[backupmediafamily] bmf ON bs.[media_set_id] = bmf.[media_set_id]
                    JOIN #dbrestored ON #dbrestored.dbname = rs.destination_database_name
          WHERE     bs.type = 'D'
                    AND rs.restore_date < #dbrestored.logdate
        ) AA
WHERE   ROWNUM = 1
ORDER BY restore_start_time DESC;