Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Database Restore Log
/************************
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 *****/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment