Below stored procedure will help you in determining the backup status of the SQL Server instance.
This procedure will provide the mail to with the backup status of all the databases. Deploy a weekly job to call the procedure to get the status and you can audit your backups just in a glance with the recent dates backups in place.
/*************************************************************************/
Use master
go
Create Proc sp_BackupAuditReport
-- SQL Server 2000/2005/2008/2012/2014/2016 Backup audit Script--------
As
set nocount on
go
Begin
DECLARE @counter SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @db_bkpdate_Full varchar(100)
DECLARE @db_bkpdate_Diff varchar(100)
DECLARE @db_bkpdate_TLog varchar(100)
DECLARE @Recovery_Model varchar(20)
DECLARE @svr_name varchar(100)
SELECT @counter=MAX(dbid) FROM
master..sysdatabases
CREATE TABLE #backup_details (ServerName varchar(100),DatabaseName varchar(100),
BkpDate_Full varchar(20)
NULL,BkpDate_Diff varchar(20)
NULL,BkpDate_Tlog varchar(20) NULL,
Recovery_Model Varchar(20))
select @svr_name = CAST(SERVERPROPERTY
('ServerName')AS sysname)
WHILE @counter > 0
BEGIN
/* Need to re-initialize all variables*/
Select @dbName = null , @db_bkpdate_Full= null ,@db_bkpdate_Diff = null,
@db_bkpdate_Tlog = null,@Recovery_Model = null
select @dbname = name ,@Recovery_Model=convert (varchar(100),
databasepropertyex(name, 'Recovery') )
from master.dbo.sysdatabases where dbid = @counter
select @db_bkpdate_Full = max (backup_start_date) from msdb..backupset
where database_name = @dbname and type='D'
select @db_bkpdate_Diff = max(backup_start_date) from msdb..backupset
where database_name = @dbname and type='I'
select @db_bkpdate_Tlog = max(backup_start_date) from msdb..backupset
where database_name = @dbname and type='L'
insert into #backup_details select @svr_name,@dbname,@db_bkpdate_Full,@db_bkpdate_Diff, @db_bkpdate_Tlog ,@Recovery_Model
--,@backupsize,@status,@filepath,@fileavailable
set @counter = @counter - 1
END
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( select [ServerName] as 'td','',[DatabaseName] As 'td','',
[BkpDate_Full] As 'td','',IsNull
([BkpDate_Diff],'') As 'td','',IsNull
([BkpDate_Tlog],'') As 'td','',
[Recovery_Model] As 'td','' from #backup_details where databasename not
in ('tempdb','northwind','pubs')
FOR XML PATH('tr'), ELEMENTS ) AS
NVARCHAR(MAX))
SET @body ='<html><body><H3>BackupAudit</H3>
<table border = "1">
<tr>
<th> ServerName </th>
<th> DatabaseName </th>
<th> BkpDate_Full </th>
<th> BkpDate_Diff </th>
<th> BkpDate_Tlog </th>
<th> Recovery_Model </th>
</tr>'
SET @body = @body + @xml+'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '_MAIL_PROFILE_',
--replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'example@my.com', --replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #backup_details
END
/*************************************************************************/
OUTPUT over the mail will be as below.
No comments:
Post a Comment