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 send you 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_b
kpdate_Diff, @db_bkpdate_Tlog ,
@Recovery_Model
--,@backupsize,@status,@filepath,@fileav
ailable
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>Backup
Audit</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