backup time and backup location, which will be useful to inform the storage admin or backup admin for
the location that needs to be restored from backup disk or tape.
/*Use Cases:
--1. In a busy SQL Server where we have 100's of databases taken using scripts,
maintenance plans, backup tape media etc to different locations and we don't know where the backup is located.
--2. Audit for previous backups. If backup is not taken in servers like QA, DEV, UAT etc.
--VIEW
--====
--Step 1: Create a view which is easy to manage.
*/
CREATE view [dbo].[Backup_Status]
as SELECT sdb.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NO BACKUP') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 101), 'NOT APPLICABLE') as LastBackupDate,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 108), 'NOT APPLICABLE') as LastBackupTime,
bkpfmly.physical_device_name as File_Location
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bkpst with (readpast) ON bkpst.database_name = sdb.name AND bkpst.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily bkpfmly with (readpast) ON bkpfmly.media_set_id = bkpst.media_set_id
GROUP BY sdb.Name,physical_device_name
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 1),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
--Note: This script provides the backup history for the past 24 hours. If you want more days than just 24 hours change the HAVING section.
/*
EX:- 3 days
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 3),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
In case you need the complete history of backup comment the HAVING section.
HTML Email:
===========
*/
go
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
[LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM Backup_Status ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF"> Database_Name </th> <th bgcolor="#66CCFF"> DaysSinceLastBackup </th> <th bgcolor="#66CCFF"> LastBackupDate </th> <th bgcolor="#66CCFF"> LastBackupTime </th> <th bgcolor="#66CCFF"> File_Location </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL DBA Team',
@body = @body,
@body_format ='HTML',
@recipients = 'chetan.kumar@driscolls.com',
@subject ='SQL Server Backup Status' ;
No comments:
Post a Comment