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

Backup Report

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL and tagged , , , , , , . Bookmark the permalink.

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

  1. aditya says:

    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.

    • 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

  2. aditya says:

    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

  3. aditya says:

    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

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s