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], [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
Hello Sir,
i had configured this script.everything works fine but in [Last BackUp Taken] iam geting info about last t log back..i was looking for last full backup status.
LikeLike
Hi Aditya,
In that case you need to change the SQL .
Add the backup type column to the query. Check the output of the below query and ensure this is what you need.
SELECT
@@SERVERNAME [SERVERNAME],
T.NAME AS [DBNAME],
(COALESCE(CONVERT(DATETIME,MAX(U.BACKUP_FINISH_DATE),101),’NOTYETTAKEN’))AS [LASTBACKUPTAKEN],
(((COALESCE(CONVERT(REAL(256),MAX(U.BACKUP_SIZE),101),’NA’))/1024)/1024)AS [BACKUPSIZEINMB],
ABS((COALESCE(CONVERT(VARCHAR(10),MAX(DATEDIFF(D,GETDATE(),U.BACKUP_FINISH_DATE))),101)))AS [DAYSSINCEBACKUP],
(COALESCE(CONVERT(VARCHAR(12),MAX(U.USER_NAME),101),’NA’))AS [USERNAME]
FROM MASTER..SYSDATABASES T
INNER JOIN MSDB.dbo.BACKUPSET U
ON T.NAME=U.DATABASE_NAME and U.type=’D’
GROUP BY T.NAME
ORDER BY T.NAME
–Prashanth
LikeLike
Hi ,
Yes this is what I exactly need.when i replace this query in the Query it just gives me details about the local server (the centralized server). It does not provide output about the servers mentioned in servers.txt file
LikeLike
Hi Aditya,
Can you just run the below sql on SSMS?
Master..XP_CMDSHELL ‘for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\BackupDetails.sql -E >> c:\backup.sql’
LikeLike
Hi Sir,
yes after running the the SQL you shared,i just need to run the second step in the job ?
Iam getting desired results as of now, Iam just wondering how do we truncate the table TRUNCATE TABLE dbo.TDATA_BackupDetails
GO
as per the step 1 in SQL job
LikeLike
One more thing… I would prefer PowerShell to do this rather enabling extended procedures(xp_cmdshell)
LikeLike
Hi Aditya,
Are you getting desired result?
The backup.sql file has all the insert the statement that are generated from the output.
and then you are inserting the data into the tdata_backupdetails table.
What you can do is add a date column in the tdata_backupDetails table and query based on the date.
SELECT
[servername], [DBName], [LastBackUpTaken],[BackupSizeinMB], [DayssinceBackup], [UserName]
from dbo.TDATA_BackupDetails where [DayssinceBackup]>0 and [Example_Date_column]=convert(varchar(10),getdate(),110)
In this case you don’t have to truncate the table.
Try this and let me know.
–Prashanth
LikeLike