Database Restore Information with Restore Start and Completed Date Time

Following points to be accomplished –

Get all the databases restoration information
From all reporting, staging or testing environments
Information about when the database restoration started and completed
If the restoration is a full restore or differential
When the backup file was ready
Above said information is to be received through Email in a tabular format.

Well, it seems to be easy, and of course it’s easy once you knew it.

Below are the implementation steps.

Write a SQL code to retrieve the complete information about backups. This includes when the backup file was ready, the restoration type, restoration start date time and end date time. Data restoration completed date time is unavailable in backup tables hence so get the information from the logs.
Create a Power-Shell script to automate this process and fetch the data restoration information from across various reporting/staging/testing servers.
Store this information in a table to maintain a history. This will help in analyzing if the restoration time is increasing/decreasing over a period of time.
Fetch this information from the table and convert it into HTML tabular format. Send the information through Email using “db Mail“.

Certainly, there will be some other work-around, or smarter methods version to achieve this goal. But since this solution solves my purpose and is working perfectly, I didn’t dig deeper to find a shorter or better implementation.

Here is my table which will hold the data restoration information.

CREATE TABLE dbo.RestoreInfo(
[Server] [NVARCHAR](30) NULL,
[Database] [NVARCHAR](100) NULL,
[RestoredBy] [NVARCHAR](100) NULL,
[RestoreType] [VARCHAR](15) NULL,
[backup_finish_date] [DATETIME] NULL,
[RestoreStarted] [DATETIME] NULL,
[RestoreFinished] [DATETIME] NOT NULL,
[RestoredFrom] [NVARCHAR](200) NULL,
[dtUpdTime] [DATETIME] DEFAULT CURRENT_TIMESTAMP
)

Here is the code to retrieve restoration information with begin and restore completion time. This needs to be modified according to your environment or backup and restoration plan.

IF OBJECT_ID(‘tempdb.dbo.#RestoreInfo’, ‘U’) IS NOT NULL
DROP TABLE #RestoreInfo;

IF OBJECT_ID(‘tempdb.dbo.#TempLog’, ‘U’) IS NOT NULL
DROP TABLE #TempLog;

IF OBJECT_ID(‘tempdb.dbo.#DBRestoreTime’, ‘U’) IS NOT NULL
DROP TABLE #DBRestoreTime;

IF OBJECT_ID(‘tempdb.dbo.#FinalOutput’, ‘U’) IS NOT NULL
DROP TABLE #FinalOutput;

DECLARE @Getdate DATETIME, @EndDate DATETIME
IF DATEPART(WEEKDAY, GETDATE()) = 7
BEGIN
SET @Getdate = CONVERT(VARCHAR(10),GETDATE()-1,101)
SET @EndDate = DATEADD(HOUR,30,@Getdate)
–This is to run on Saturday to fetch Friday information.
–The restores whatever happens between Friday early hours to 30 hours
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) = 1
BEGIN
SET @Getdate = CONVERT(VARCHAR(10),GETDATE()-1,101)
SET @EndDate = DATEADD(HOUR,33,@Getdate)
END
ELSE IF DATEPART(WEEKDAY, GETDATE()) IN(2,3,4,5)
BEGIN
SELECT @Getdate = CONVERT(VARCHAR(10),GETDATE(),101)
SELECT @EndDate = DATEADD(HOUR,9,@Getdate)
–9 is because to check if there is any restoration in last 9 hours
END

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

CREATE TABLE #DBRestoreTime
(
ServerName VARCHAR(20),
LogDate DATETIME ,
ProcessInfo NVARCHAR(50) ,
[Text] NVARCHAR(MAX),
[RestoreText] NVARCHAR(MAX)
)

CREATE TABLE #FinalOutput(
[Server] [nvarchar](30) NULL,
[Database] [nvarchar](100) NULL,
[RestoredBy] [nvarchar](100) NULL,
[RestoreType] [varchar](15) NULL,
[backup_finish_date] [datetime] NULL,
[RestoreStarted] [datetime] NULL,
[RestoreFinished] [datetime] NOT NULL,
[RestoredFrom] [nvarchar](200) NULL,
[dtUpdTime] [datetime] NULL,
[MaxLogDate] [datetime] NULL
)

INSERT INTO dbo.RestoreInfo
SELECT
@@SERVERNAME,
rsh.destination_database_name,
rsh.user_name,
CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’
WHEN rsh.restore_type = ‘F’ THEN ‘File’
WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’
WHEN rsh.restore_type = ‘I’ THEN ‘Differential’
WHEN rsh.restore_type = ‘L’ THEN ‘Log’
WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’
WHEN rsh.restore_type = ‘R’ THEN ‘Revert’
ELSE rsh.restore_type
END,
bs.backup_finish_date,
rsh.restore_date,
”,
bmf.physical_device_name,
GETDATE()
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date BETWEEN @Getdate AND @EndDate
ORDER BY rsh.restore_date

— To retrieve restore time
INSERT INTO #TempLog
EXEC sp_readerrorlog

ALTER TABLE #TempLog ADD ServerName VARCHAR(20)

UPDATE #TempLog SET ServerName = @@SERVERNAME

DELETE FROM #TempLog WHERE LogDate NOT BETWEEN @Getdate AND @EndDate

DELETE FROM #TempLog WHERE
Text NOT LIKE ‘%Restore is complete on database%’ AND
Text NOT LIKE ‘%The database is now available%’ AND
— Text NOT LIKE ‘%restore database successfully processed%’ AND
Text NOT LIKE ‘%changes were restored%’ AND
Text NOT LIKE ‘%RESTORE DATABASE successfully%’ AND
Text NOT LIKE ‘%Database was restored%’

INSERT INTO #DBRestoreTime (
ServerName,
LogDate,
ProcessInfo,
[Text],
RestoreText )
SELECT ServerName,
LogDate,
ProcessInfo,
[Text],
LAG(Text)
OVER (ORDER BY LogDate)
FROM #TempLog a

INSERT INTO #FinalOutput
SELECT [Server],
[Database],
RestoredBy,
RestoreType,
backup_finish _date,
RestoreStarted,
RestoreFinished,
RestoredFrom,
dtUpdTime,
MAX(b.LogDate) MaxLogDate
FROM dbo.RestoreInfo a
JOIN #DBRestoreTime b ON a.Server = b.ServerName
AND RestoreText LIKE ‘%’+[Database]+’%’
WHERE RestoreStarted BETWEEN @Getdate AND @EndDate
GROUP BY [Server],
[Database],
RestoredBy,
RestoreType,
backup_finish_date,
RestoreStarted,
RestoreFinished,
RestoredFrom,
dtUpdTime

UPDATE a SET [RestoreFinished] = b.MaxLogDate FROM dbo.RestoreInfo a
JOIN #FinalOutput b ON a.Server = b.Server AND a.[Database] = b.[Database] AND
CONVERT(VARCHAR(10),a.RestoreStarted,101) = CONVERT(VARCHAR(10),b.RestoreStarted,101)

Upon execution you will get the complete data restore information from the reporting or staging or testing servers in which you had executed the code. However the requirement is to fetch the same information across various servers. Hence write a Power-shell script as mentioned below.

PS Script clear;
$Servers = Get-Content “D:\Monitoring_RestoreInfo\Computers.txt”;
foreach($computer in $Servers)
{ invoke-sqlcmd -inputfile “D:\Monitoring_RestoreInfo\Restore Information SQL Server.sql” -serverinstance $computer -database “msdb” -ErrorAction ‘Stop’ -Verbose -QueryTimeout 1800 # the parameter -database can be omitted based on what your sql script does.
}

Note: Computers.txt has all the names of the reporting/staging/testing servers.

Create a windows scheduled task in the server in where you have an access to other servers. The screenshots are given in the below slide show.

This slideshow requires JavaScript.

Arguments provided are as follows:

Program/Script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Add arguments (Optional): D:\Monitoring_RestoreInfo\DBRestoreInfo.ps1
Start In : D:\Monitoring_RestoreInfo\

Now let’s go back to SQL Server database in which you have created the table. Write a script to convert the data into HTML format.

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [Server] AS ‘td’,”,
[Database] AS ‘td’,”,
[RestoreType] AS ‘td’,”,
CONVERT(varchar(25), [backup_finish_date],21) AS ‘td’,”,
CONVERT(varchar(25), [RestoreStarted],21) AS ‘td’,”,
CONVERT(varchar(25), [RestoreFinished],21) AS ‘td’,”,
[RestoredFrom] AS ‘td’,”,
CONVERT(varchar(25), [dtUpdTime],21) AS ‘td’
FROM [dbTest].[dbo].[RestoreInfo] WITH (NOLOCK)
WHERE RestoreStarted >= DATEADD(HOUR,-24, GETDATE())
ORDER BY [Server]
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))

SET @Body = ‘<html><head>’
+ ‘<style>’
+ ‘th {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} ‘
+ ‘td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} ‘
+ ‘</style>’
+ ‘</head>’
+ ‘<body>’
+ ‘<h4>Data Restoration Info</h4>’
+ ‘<table border=”1″>’
+ ‘<tr>’
+ ‘<th>Server</th>’
+ ‘<th>Database</th>’
+ ‘<th>RestoreType</th>’
+ ‘<th>BackupFinished</th>’
+ ‘<th>RestoreStarted</th>’
+ ‘<th>RestoreFinished</th>’
+ ‘<th>RestoredFrom</th>’
+ ‘<th>dtUpdTime</th>’
+ ‘</tr>’

SET @Body = @Body + @xml +'</table></body></html>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’, — replace with your SQL Database Mail Profile
@body = @body,
@body_format =’HTML’,
@from_address = ‘RestoreInfo <shaikshafi.nabi@gmail.com>’,
@recipients = ‘shaikshafi.nabi@gmail.com’, — replace with your email address
@subject = ‘Database Restoration Info’ ;

Create an SQL Agent Job to automate this process and run it every morning after execution of the Power-shell script.

This slideshow requires JavaScript.

Thank you for visiting my blog. Please do let me know if you need more clarification in this implementation.