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

}

}

$servername=’Server01′
$DatabaseName=’PowerSQL’

#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

*********************************************************

DECLARE

@html1 NVARCHAR(MAX),

@html2 NVARCHAR(MAX),

@dml1 NVARCHAR(MAX),

@dml2 NVARCHAR(MAX),

@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’

CREATE TABLE #temp

(

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)

SELECT

SERVERNAME,

DatabaseName,

LastFullBackupDate,

LastDifferentialBackupDate,

LastAbsoluteBackupDate,

LastAbsoluteBackupType,

BackupDelta

FROM dbo.TLOG_DatabaseBackup where BackupDelta>0

select * from #temp

set @html1=’<html>

<head>

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

</td>

</tr>

</table>

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

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

</tr>

‘

SET @loopstatus=1

SET @RowId=1

SET @dml2=”

SET @dml1=”

WHILE @Loopstatus<>0

BEGIN

SELECT

@td1 = ServerName,

@td2 = DatabaseName,

@td3 = LastFullBackupDate,

@td4 = LastDifferentialBackupDate,

@td5 = LastAbsoluteBackupDate,

@td6 = LastAbsoluteBackupType,

@td7= BackupDelta

FROM #temp WHERE id=@RowId

IF @@ROWCOUNT=0

BEGIN

SET @Loopstatus=0

END

ELSE

BEGIN

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=”

END

SET @RowId=@RowId+1

END

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’;

DROP TABLE #Temp

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