One of SQL enthusiast asked me to include the complete code and wanted to schedule a SQL job. I’m trying to give more information with this post also including Step by Step procedure to setup and configure backup report.
This is going to be a continuation of my previous post PART-2, data is pulled to centralized server table (TDATA_BackupDetails) from all SQL listed Instances. Now, you need to send an auto generated email to an intended administrators or scheduling a SQL job.
Pre-requisites are
- Enable XP_CMDShell
- Mail Profile
Step by Step procedures to be done on centralized server is as follows
- Enable XP_CMDShell
- List all SQL Instances in c:\Server.txt
- Table Creation TDATA_BackupDetails
- Copy and Paste T-SQL script in C:\BackupDetails.sql [Change @DatabaseName]
- Execute T-SQL’s [SSMS] – replace valid centralized server name Master..XP_CMDSHELL ‘for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql’ GO MASTER..XP_CMDSHELL ‘sqlcmd -S AQDBSQL01 -i c:\backup.sql -E’
- Data Validation select * from dbo.TDATA_BackupDetails where [DayssinceBackup]>1
- Prepare HTML Formatted data – Automatc Email [Change MailProfileName and receipients details - Find the code below STEP 2]
- SQL JOBS Steps ( If you think of creating a sql job)
- STEP 1 – Replace AQDBSQL01 in the below code
TRUNCATE TABLE dbo.TDATA_BackupDetails
GO
Master..xp_cmdshell ‘del c:\backup.sql’
Go
Master..XP_CMDSHELL ‘for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql’
GO
MASTER..XP_CMDSHELL ‘sqlcmd -S AQDBSQL01 -i c:\backup.sql -E’
- STEP 2: Change Profile and Receipients details
DECLARE @tableHTML NVARCHAR(MAX),
@td1 char(15),
@td2 char(15) ,
@td3 char(20) ,
@td4 char(15),
@td5 char(15),
@td6 char(15),
@td7 char(15),
@Loopstatus11 int,
@dmll nvarchar(max),
@dml2 nvarchar(max),
@Loopstatus1 int,
@RowId1 int,
@ProfileName varchar(100),
@receipients varchar(200)
/* Change Profile and Receipients details*/
SET @ProfileName =’Test’
SET @receipients=’pram@powersql.com;prasha.jayaram@PowerSQL.com’
CREATE TABLE #TLOG_BackupDetails(
[id] int identity(1,1),
[servername] [nvarchar](128) NULL,
[DB Name] [sysname] NOT NULL,
[Last BackUp Taken] varchar(50) NULL,
[Backup Size in MB] varchar(20) NULL,
[Days since Backup] varchar(20) NULL,
[User Name] [varchar](12) NULL
)
insert into #TLOG_BackupDetails( [servername], [DB Name], [Last BackUp Taken], [Backup Size in MB], [Days since Backup], )
SELECT
[servername], [DBName], [LastBackUpTaken],[BackupSizeinMB], [DayssinceBackup], [UserName]
from dbo.TDATA_BackupDetails where [DayssinceBackup]>0
SET @dmll=”
SET @dml2=”
SET @Loopstatus1=1
SET @RowId1=1
SET @tableHTML =
N’<H1>Database Backup Details </H1>’ +
N’<table border=”1″ cellpadding=”1″><tr>
[ServerName]
<th BGCOLOR=”RED”>[DB Name]</th>
<th BGCOLOR=”RED”>[Last BackUp Taken]</th>
<th BGCOLOR=”RED”>[Backup Size in MB]</th>
<th BGCOLOR=”RED”>[Days since Backup]</th>
<th BGCOLOR=”RED”>[User Name]</th>
</tr>’
While @Loopstatus1<>0
begin
select
@td1 =[ServerName],
@td2 =[DB Name],
@td3 =[Last BackUp Taken],
@td4 =[Backup Size in MB],
@td5 =[Days since Backup],
@td6 =[User Name]
from #TLOG_BackupDetails where id=@RowId1
if @@ROWCOUNT=0
begin
set @Loopstatus1=0
end
else
begin
set @dmll= @dml2+N”+@td1+N”+@td2+”+@td3+”+@td4+”+@td5+”+@td6+”
set @dml2=@dmll
set @dmll=”
end
set @RowId1=@RowId1+1
end
SET @tableHTML=@tableHTML+@dml2+’</table>’
print @tableHTML
if (exists (select * from #TLOG_BackupDetails))
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients=@receipients,
@subject = ‘Backup Details Report’,
@body = @tableHTML,
@body_format = ‘HTML’;
end
DROP TABLE #TLOG_BackupDetails
Complete code is here SQL- AutoEmail – BackupReport
Output – Auto generated email below
