/************************
Script Purpose: To Keep Database Restore Log
Script By : Aasim Abdullah for https://connectsql.blogspot.com
************************/

USE master
GO
CREATE TABLE DatabaseRestoreLog 
(DatabaseName VARCHAR(50), RestoreDate DATETIME, RestoredFrom VARCHAR(500))

GO
/****** Start: Job step script *****/
-- Table variable to hold intermediate data
DECLARE @ReportSQLErrorLogs TABLE
    (
      [log_date] [datetime] NULL,
      [processinfo] [varchar](255) NULL,
      [processtext] [text] NULL
    ) 

DECLARE @NumErrorLogs INT,
    @CurrentLogNum INT

SET @CurrentLogNum = 0

-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
    @NumErrorLogs OUTPUT
				
SELECT  @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
 -- NULL in registry entry for Error Log files mean default of 6 value

WHILE @CurrentLogNum < @NumErrorLogs 
    BEGIN
        insert  into @ReportSQLErrorLogs
                exec master..xp_readerrorlog @CurrentLogNum
        PRINT @CurrentLogNum
        SELECT  @CurrentLogNum = @CurrentLogNum + 1
    END

INSERT INTO DatabaseRestoreLog
SELECT  SUBSTRING(processtext, CHARINDEX('base:', processtext, 1) + 5,
                  CHARINDEX(',', processtext, 0) - ( CHARINDEX('base:', processtext, 0) )
                  - 5), log_date,
            SUBSTRING(processtext, CHARINDEX(': {''', processtext, 1) + 4,
                  CHARINDEX('''})', processtext, 0) - ( CHARINDEX(': {''', processtext, 0) )- 4)      
FROM    @ReportSQLErrorLogs
WHERE   processtext LIKE 'Database was restored%'
--For last 24 hours
AND DATEDIFF(HH,log_date,GETDATE()) <=24
ORDER BY log_date DESC

/****** End: Job step script *****/