T-SQL Solution:
Create Procedure dbo.Admin_Database_Restores_Report
@NoDays Int = -1
as
--v1
DECLARE @dbname sysname, @days int
SET @dbname = NULL --Target a database or NULL for all databases per instance
SET @days = @NoDays --# previous days to search
SELECT
@@SERVERNAME as 'Server',
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
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 AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To],
GetDate() as 'Checked Time'
into #TMP
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H4><Font Color=black><font face="arial">Delivered by DatabaseMail on ' + @@SERVERNAME +' :</H4>' +
N'<H5><Font Color=black><font face="arial">Report Title: Database Restores Report</H5>' +
N'<table border="1" cellpadding=5 style=”font-family:Calibri;color:black;font-size:10pt;”> ' +
N'
<tr align="left">
<th align="left">Server</th><th align="left">Database</th><th align="left">Restored By</th>
<th align="left">Restore Type</th><th align="left">Restore Started</th><th align="left">Restored From</th><th align="left">Restored To</th>
<th align="left">Checked Time</th>
</tr>' +
CAST ((
select
td =t1.[Server], '', td =t1.[Database], '', td =t1.[Restored By], '', td =t1.[Restore Type], '',
td =t1.[Restore Started], '', td =t1.[Restored From], '', td =t1.[Restored To], '', td =t1.[Checked Time], ''
from #TMP t1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
If exists (select 1 from #TMP)
Begin
If @tableHTML <> '' and @tableHTML IS NOT NULL
Begin
--Set @tableHTML = @tableHTML + '</br></br>' + 'Log Time: ' + convert(varchar(150),@LastLogTime)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'PRODBMail',
@recipients='xyz@ops.com',
@subject = 'Database Report - PRD - Database Restored Report',
@body = @tableHTML,
@body_format = 'HTML' ;
End
End
Drop Table #TMP
T-SQL used:
DECLARE @dbname sysname, @days int
SET @dbname = NULL --Target a database or NULL for all databases per instance
SET @days = -30 --# previous days to search
SELECT
@@SERVERNAME as 'Server',
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
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 AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To],
GetDate() as 'Checked Time'
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
|
|
|
|
|
|