3

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?

  • I don't think it's tracked, but have a look at this post - dba.stackexchange.com/a/101312/60085 – Scott Hodgin Apr 12 '17 at 14:18
  • Thx @ScottHodgin, I searched and didn't see those – MikeC Apr 12 '17 at 17:53
  • @MikeC did you try the code I pasted in answer section? – SqlWorldWide Apr 13 '17 at 11:31
  • @SqlWorldWide, sorry, was checking out the code Scott linked and thought I posted a dupe; I just tried and works great – MikeC Apr 13 '17 at 15:12
3

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;
  • since this is a test server I wanted to see how the restore went from my offshore storage team via Netbackup. Works great – MikeC Apr 13 '17 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.