SQL- Setup and Configure Backup Report – Step by Step Approach – All listed SQL Instances

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

1. Enable XP_CMDShell 
2. Mail Profile

Step by Step procedures to be done on centralized server is as follows

1. Enable XP_CMDShell 
2. List all SQL Instances in c:\Server.txt
3. Table Creation TDATA_BackupDetails
4. Copy and Paste T-SQL script in C:\BackupDetails.sql [Change @DatabaseName]
5. 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’
6. Data Validation                                                                                                                      select * from dbo.TDATA_BackupDetails where [DayssinceBackup]>1
7. Prepare HTML Formatted data – Automatc Email [Change MailProfileName and receipients details – Find the code below STEP 2]
8. 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], [USER Name] )
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