PowerSQL – Database Backup Report across all Servers – Centralized Approach

This topic provides and describes the quickest way to find when and what databases are being backed up recently.Backups are most important part of a recovery strategy. In this case, I’m explaining this process in three steps
1) Instance names are stored in a table [SQLInstances] and Database [PowerSQL], The result will be stored in staging table [TLOG_DatabaseBackup] and output is formatted HTML report.
Make sure that the centralized server(Server01) has DB Mail configured with a profilename(Test).

You are doing the below steps in centralized instance i.e is Server01

CREATE TABLE [dbo].[SQLInstances](
[InstanceName] [varchar](128) NULL,
[Status] [varchar](50) NULL

/*Inserted two instances (Default and Named Instances)*/

INSERT INTO TABLE dbo.SQLInstances values(‘Server05′,’Yes’)
INSERT INTO TABLE dbo.SQLInstances values(‘Server02\TMG’,’Yes’)

CREATE TABLE [dbo].[TLOG_DatabaseBackup](
[SERVERNAME] [varchar](50) NULL,
[DatabaseName] [varchar](128) NULL,
[LastFullBackupDate] [datetime] NULL,
[LastDifferentialBackupDate] [datetime] NULL,
[LastAbsoluteBackupDate] [datetime] NULL,
[LastAbsoluteBackupType] [char](4) NOT NULL,
[BackupDelta] [int] NOT NULL

2) Job Creation has two steps
a) In the First Step Select Powershell as its Type (Select PowerShell Under Type drop down option)
b) copy the below code and paste in Job command place.
Note: Change ServerName and Database Parameters under $params

Function Get-DBBackupToDatabase ($SQLInstance)


#Check whether or not a named instance and set the location accordingly

if ($SQLInstance -ilike “*\*”) {$location = “SQLSERVER:\SQL\$SQLInstance\Databases”}

else {$location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases”}

$DBStats = dir -force $location | where-object {$_.Name -ne “tempdb”; $_.Refresh()}

foreach ($DB in $DBStats)


$DBName = $DB.Name

$LastFull = $DB.LastBackupDate

IF ($DB.LastDifferentialBackupDate -eq “01/01/0001 00:00:00”) {$LastDiff = $NULL} ELSE {$LastDiff = $DB.LastDifferentialBackupDate}

#The last absolute backup will be the newer of the last full or last differential, we can also set the type using this

IF ($LastDiff -gt $LastFull) {$LastAbsolute = $LastDiff; $LastType = “DIFF”}

ELSEIF ($LastFull -eq “01/01/0001 00:00:00”){$LastAbsolute = $LastFull; $LastType = “NONE”}

ELSE {$LastAbsolute = $LastFull; $LastType = “FULL”}

#Quick calculation gives us the number of days since the last backup

$DaysSince = ((Get-Date) – $LastAbsolute).Days

#Because SQL cannot store the default date of 01/01/0001 we set it to null, which will store in sql as 1900-01-01

IF ($LastFull -eq “01/01/0001 00:00:00”) {$LastFull = $NULL}

IF ($LastAbsolute -eq “01/01/0001 00:00:00″) {$LastAbsolute = $NULL}

$InsertResults = @”

INSERT INTO dbo.TLOG_DatabaseBackup (ServerName, DatabaseName, LastFullBackupDate, LastDifferentialBackupDate, LastAbsoluteBackupDate, LastAbsoluteBackupType, BackupDelta)

VALUES (‘$SQLInstance’, ‘$DBName’, ‘$LastFull’, ‘$LastDiff’, ‘$LastAbsolute’, ‘$LastType’, ‘$DaysSince’)


invoke-sqlcmd @params -Query $InsertResults




#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside

$params = @{‘server’=$servername; ‘Database’=$DatabaseName}

#Grab our list of servers, iterate through them and call the function which rights to the database

$Srv = invoke-sqlcmd @params -Query “TRUNCATE TABLE dbo.TLOG_DatabaseBackup;SELECT InstanceName from SQLInstances where status=’Yes'”

foreach ($Instance in $srv)
Get-DBBackupToDatabase $Instance.InstanceName


3) New Step 2 Creation – Email Sending – I’ve already posted on how to send and format the table data into a HTML Format. I’m going to use the same technique to send email.
Note: Change the @ProfileName and @recipients list

@td1 VARCHAR(50),
@td2 VARCHAR(128),
@td3 VARCHAR(50),
@td4 VARCHAR(50),
@td5 VARCHAR(50),
@td6 VARCHAR(5),
@td7 VARCHAR(3),
@loopstatus int,
@RowId INT,
@subject VARCHAR(100),
@RowId1 INT,
@LoopStaus1 INT,
@ProfileName NVARCHAR(50),
@recipients NVARCHAR(100)

SET @ProfileName=’TEST’
SET @recipients=’ABC@MAIL.COM;def@mail.com’

ID int identity(1,1),
SERVERNAME varchar(50) NULL,
DatabaseName varchar(128) NULL,
LastFullBackupDate varchar(50) NULL,
LastDifferentialBackupDate varchar(50) NULL,
LastAbsoluteBackupDate varchar(50) NULL,
LastAbsoluteBackupType char(4) NOT NULL,
BackupDelta char(3) NOT NULL

INSERT INTO #temp(SERVERNAME,DatabaseName,LastFullBackupDate,LastDifferentialBackupDate,LastAbsoluteBackupDate,LastAbsoluteBackupType,BackupDelta)
FROM dbo.TLOG_DatabaseBackup where BackupDelta>0

select * from #temp

set @html1='<html>
<STYLE TYPE=text/css>
<table width=”100%” border=1>
<tr bgcolor=”#CCCCCC”>
<td colspan=”7” height=”25” align=”center”>
<font face=”tahoma” color=”#003399” size=”4”>
<strong>Backup Report</strong></font>
<table width=”100%” border=1 ><tbody>
<tr bgcolor=”#CCCCCC”>
<td width=”100%” align=”center” colSpan=7><font face=”tahoma” color=”#003399” size=”2”>
<strong>Production DB Servers Backup Report </strong></font></td>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>DatabaseName</td>
<td width=”10%” align=”center”>LastFullBackupDate</td>
<td width=”10%” align=”center”>LastDifferentialBackupDate</td>
<td width=”10%” align=”center”>LastAbsoluteBackupDate</td>
<td width=”10%” align=”center”>LastAbsoluteBackupType</td>
<td width=”10%” align=”center”>BackupDelta</td>

SET @loopstatus=1
SET @RowId=1
SET @dml2=”
SET @dml1=”

WHILE @Loopstatus<>0
@td1 = ServerName,
@td2 = DatabaseName,
@td3 = LastFullBackupDate,
@td4 = LastDifferentialBackupDate,
@td5 = LastAbsoluteBackupDate,
@td6 = LastAbsoluteBackupType,
@td7= BackupDelta
FROM #temp WHERE id=@RowId

SET @Loopstatus=0
SET @dml1= @DML2+N'<tr><td>’+@td1+N'</td><td>’+@td2+'</td><td>’+@td3+'</td><td>’+@td4+'</td><td>’+@td5+'</td><td>’+@td6+'</td><td>’+@td7+'</td></tr>’
SET @DMl2=@dml1
SET @dml1=”

SET @RowId=@RowId+1


SET @html2=@html1+@Dml2+'</table>’

PRINT @html2

SET @subject=’Production Servers DB Backup Report ‘+ CONVERT(varchar(10),getdate(),110)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @recipients,–‘abc@mail.com;def@mail.com’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;


Note:- Make sure that you do a double check on single and double quotes

About Prashanth Jayaram

I’m a Database technologist having 9+ 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 My Technet Profile https://social.technet.microsoft.com/profile/prashanth jayaram/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/
This entry was posted in PowerShell, SQL. Bookmark the permalink.

3 Responses to PowerSQL – Database Backup Report across all Servers – Centralized Approach

  1. Shailendra Vernekar says:

    Nice Prashanth.

  2. adinarayana says:

    please help me sir
    i am facing many issues

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