In almost any Exchange Server 2010 environment there will be a good reason for adding some additional monitoring of backups.

Perhaps the environment is large enough that the backups are managed by a separate team to the Exchange administrators, and so the Exchange admins just want to keep an eye on their database backups. Or even in a smaller environment the administrators may just want to be sure that their backup software isn’t reporting successful backups when in fact they may be failing.

Note: the script code below has a few bugs and hasn’t been maintained. For monitoring your Exchange database backups I recommend using the Get-DailyBackupAlerts.ps1 script instead.

In this post I will demonstrate a PowerShell script that you can run in an Exchange Server 2010 environment to report on any databases that have not successfully backed up in the last 48 hours. Let’s take a look at how this works.

Firstly, backup time stamps are retrievable using the Get-MailboxDatabase cmdlet for mailbox databases, or Get-PublicFolderDatabase for public folder databases. Here is an example:

PS C:Scripts> Get-MailboxDatabase -Status | ft name,last* -auto

Name     LastFullBackup       LastIncrementalBackup LastDifferentialBackup LastCopyBackup
----     --------------       --------------------- ---------------------- --------------
MB-HO-01 11/6/2011 1:40:19 PM
MB-HO-02
MB-HO-03
MB-BR-01

You can see that one of the databases above has been backed up, but the others have not. In a script that checks both mailbox and public folder databases we could use this code to generate the report.

$dbs = Get-MailboxDatabase -Status
$dbs = $dbs += Get-PublicFolderDatabase -Status
$dbs | ft name,last*

Now we can see both the mailbox and the public folder databases.

Name                    LastFullBackup          LastIncrementalBackup   LastDifferentialBackup  LastCopyBackup
----                    --------------          ---------------------   ----------------------  --------------
MB-HO-01                11/6/2011 1:40:19 PM
MB-HO-02
MB-HO-03
MB-BR-01
PF-HO-01                11/6/2011 1:40:20 PM
PF-BR-01

It would be a simple matter to run that command and read the output every single day, but that would not be the most efficient way of checking database backups. There could be dozens or even hundreds of databases in that output, and we’d have to mentally perform the calculations to determine whether the backup time stamp is within the last 48 hours or not.

Instead we want to only to see databases that have not been backed up within the 48 hour threshold. To do this we can use some date maths. Because we’ve already retrieved the database objects into a collection called $dbs we can loop through them to perform the date calculations like this. Note that in this example only Full and Incremental backups are being considered.

foreach ($db in $dbs)
{
	Write-Host -ForegroundColor Gray "Checking" $db.name"..."

	$lastbackup = @{}
	$ago = @()

	if ( $db.LastFullBackup -eq $nul -and $db.LastIncrementalBackup -eq $nul)
	{
		$lastbackup.time = "Never"
		$lastbackup.type = "Never"
		[string]$ago = "Never"
	}
	elseif ( $db.LastFullBackup -lt $db.LastIncrementalBackup )
	{
		$lastbackup.time = $db.LastIncrementalBackup
		$lastbackup.type = "Incremental"
		[int]$ago = ($now - $lastbackup.time).TotalHours
		$ago = "{0:N0}" -f $ago
	}
	elseif ( $db.LastIncrementalBackup -lt $db.LastFullBackup )
	{
		$lastbackup.time = $db.LastFullBackup
		$lastbackup.type = "Full"
		[int]$ago = ($now - $lastbackup.time).TotalHours
		$ago = "{0:N0}" -f $ago
	}
}

One of three possible scenarios will be true:

  • If the database has not been backed up at all then both Full and Incremental time stamps will be nul, so all of the resulting values are set to “Never”
  • If the most recent backup was an Incremental then the type is set to “Incremental” and the $ago variable is set to the number of hours that has passed since that Incremental backup finished
  • If the most recent backup was a Full then the type is set to “Full” and the $ago variable is set to the number of hours that has passed since that Full backup finished

Now, because we’re only interested in databases that haven’t backed up in the last 48 hours (or have never been backed up at all) we can compare the $ago variable to our threshold of 48 hours, and if an alert needs to be raised set the alert flag to True and create an object with all of the desired information for our report.

if ( $ago -gt $threshold -or $ago -eq "Never")
	{
		$alertflag = $true
		$dbObj = New-Object PSObject
		$dbObj | Add-Member NoteProperty -Name "Server/DAG" -Value $db.MasterServerOrAvailabilityGroup
		$dbObj | Add-Member NoteProperty -Name "Database" -Value $db.name
		$dbObj | Add-Member NoteProperty -Name "Database Type" -Value $dbtype
		$dbObj | Add-Member NoteProperty -Name "Last Backup Type" -Value $lastbackup.type
		$dbObj | Add-Member NoteProperty -Name "Hrs Ago" -Value $ago
		$dbObj | Add-Member NoteProperty -Name "Time Stamp" -Value $lastbackup.time
		$alerts = $alerts += $dbObj
	}

That code actually goes inside the ForEach loop for the $dbs collection.

The final piece of the script is the report itself. We output it based on whether the alert flag is set to True or False.

if ($alertflag = $true )
{
	Write-Host "The following databases have not been backed up in" $threshold "hours."
	$alerts | ft -AutoSize
}
else
{
	Write-Host "No backup alerts required."
}

This example outputs the report to the PowerShell window but you could write your PowerShell script to send an email report instead if you wish.

Here is an example report:

PS C:Scripts> .Check-DatabaseBackups.ps1
Getting database list...
Checking MB-HO-01 ...
Checking MB-HO-02 ...
Checking MB-HO-03 ...
Checking MB-BR-01 ...
Checking PF-HO-01 ...
Checking PF-BR-01 ...
The following databases have not been backed up in 48 hours.

Server/DAG     Database Database Type Last Backup Type Hrs Ago Time Stamp
----------     -------- ------------- ---------------- ------- ----------
dag-headoffice MB-HO-02 Mailbox       Never            Never   Never
dag-headoffice MB-HO-03 Mailbox       Never            Never   Never
BR-EX2010-MB   MB-BR-01 Mailbox       Never            Never   Never
BR-EX2010-MB   PF-BR-01 Public Folder Never            Never   Never

And here is the complete script.

#
#.SYNOPSIS
#Checks the backup timestamps for the servers
#and alerts if a database hasn't been backed up
#in the last 48 hours
#
#.EXAMPLE
#.Check-DatabaseBackups.ps1
#

#...................................
# Variables
#...................................

$ErrorActionPreference = "SilentlyContinue"
$WarningPreference = "SilentlyContinue"

$alerts = @()
[int]$threshold = 48
[bool]$alertflag = $false
$now = [DateTime]::Now

#...................................
# Script
#...................................

#Get all Mailbox and Public Folder databases
Write-Host -ForegroundColor Gray "Getting database list..."
$dbs = Get-MailboxDatabase -Status
$dbs = $dbs += Get-PublicFolderDatabase -Status

#Check each database for most recent backup
foreach ($db in $dbs)
{
	Write-Host -ForegroundColor Gray "Checking" $db.name"..."

	$lastbackup = @{}
	$ago = @()

	if ( $db.LastFullBackup -eq $null -and $db.LastIncrementalBackup -eq $nul)
	{
		$lastbackup.time = "Never"
		$lastbackup.type = "Never"
		[string]$ago = "Never"
	}
	elseif ( $db.LastFullBackup -lt $db.LastIncrementalBackup )
	{
		$lastbackup.time = $db.LastIncrementalBackup
		$lastbackup.type = "Incremental"
		[int]$ago = ($now - $lastbackup.time).TotalHours
		$ago = "{0:N0}" -f $ago
	}
	elseif ( $db.LastIncrementalBackup -lt $db.LastFullBackup )
	{
		$lastbackup.time = $db.LastFullBackup
		$lastbackup.type = "Full"
		[int]$ago = ($now - $lastbackup.time).TotalHours
		$ago = "{0:N0}" -f $ago
	}

	if ($db.IsMailboxDatabase -eq $true) {$dbtype = "Mailbox"}
	if ($db.IsPublicFolderDatabase -eq $true) {$dbtype = "Public Folder"}

	#If backup time stamp older than threshold set alert flag and create object for alerting
	if ( $ago -gt $threshold -or $ago -eq "Never")
	{
		$alertflag = $true
		$dbObj = New-Object PSObject
		$dbObj | Add-Member NoteProperty -Name "Server/DAG" -Value $db.MasterServerOrAvailabilityGroup
		$dbObj | Add-Member NoteProperty -Name "Database" -Value $db.name
		$dbObj | Add-Member NoteProperty -Name "Database Type" -Value $dbtype
		$dbObj | Add-Member NoteProperty -Name "Last Backup Type" -Value $lastbackup.type
		$dbObj | Add-Member NoteProperty -Name "Hrs Ago" -Value $ago
		$dbObj | Add-Member NoteProperty -Name "Time Stamp" -Value $lastbackup.time
		$alerts = $alerts += $dbObj
	}
}

#If alert flag is true output the report
if ($alertflag -eq $true )
{
	Write-Host "The following databases have not been backed up in" $threshold "hours."
	$alerts | ft -AutoSize
}
else
{
	Write-Host "No backup alerts required."
}

If you’d like to schedule this report to run each day and alert you by email when databases are not being backed up then click here

About the Author

Paul Cunningham

Paul is a former Microsoft MVP for Office Apps and Services. He works as a consultant, writer, and trainer specializing in Office 365 and Exchange Server. Paul no longer writes for Practical365.com.

Comments

  1. subbareddy

    Hi Paul,

    I have number of servers in a file. how to get a particular file backup for every day and in case i did’t get file backup how to find out the server.

  2. Jay

    Dear sir,

    Thanks for cool script, I have a question could you explain about how to configure database reporting service at my outlook mail box.

    I want to receive mails automatically at my mail box. is it possible? if yes what would be necessary changes kindly explain.

    Regards
    Mritunjay

  3. Dinesh

    Hi Paul,

    In my Exchange environment we have some legacy exchange server which is going to decommission very soon, when I’m running this script it is connecting all servers and throwing error, is it possible to ignore legacy servers or give last of server for backup report.

  4. George

    Also please fix “$alerts = $alerts += $dbObj” to “$alerts += $dbObj”

  5. George

    The current code does not provide public folders status due to a small error:

    $mbdbs = Get-MailboxDatabase -Status
    $pfdbs = Get-PublicFolderDatabase -Status
    $dbs = $mbdbs,$pfdbs

    This will work.

  6. Tom

    Is there any way to run this script and exclude databases that you know aren’t needed to be backed up?

  7. AUSSUpport

    I get this msg?

    PS C:scripts> ./Get-DailyBackupAlerts.ps1
    File C:scriptsGet-DailyBackupAlerts.ps1 cannot be loaded. The file C:scriptsGet-DailyBackupAlerts.ps1 is not digita
    lly signed. The script will not execute on the system. Please see “get-help about_signing” for more details..
    At line:1 char:28
    + ./Get-DailyBackupAlerts.ps1 <<<<
    + CategoryInfo : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException

  8. Peter Weghofer

    Hi,

    cool script !

    you should probably initialize the Variable “$alertflag” only once outside of the foreach loop:

    ##

    [bool]$alertflag=$false

    #Check each database for most recent backup
    foreach (db in $dbs)
    { ….
    ##

    otherwise an email will only be generated if the last Database has a Backup outside the threshold.

  9. Steven

    Great article, can see how this will be very usefull!!

  10. Turbomcp

    Thanks, cool script
    just one question,according to an article you posted few days ago the time stamp doesnt update on passive copies?
    so how can this script give you accurate results if probably any organization will backup passive copies?
    (if im misunderstanding something let me know)

    Thanks in advance
    Turbo

    1. Avatar photo

      Hi Turbo, good question, and it highlights how this stuff can quickly become complex.

      So firstly, people who don’t run DAGs (or don’t run them everywhere) won’t need to worry about that so much. The script will work fine for them.

      For those with DAGs they need to think strategically about the best way to backup their databases, and the best way to monitor those backups (eg with scripts like this), and any other supporting tasks that need to tie the two together.

      Examples of how you can “solve” this:
      – backup every DAG member in full (yes this means you’re backing up the same database multiple times, costing more in backup storage. If the DAG members are in separate physical locations there is some merit to that approach)
      – map out your backups so that each server only backs up the databases it is the first activation preference for (this would mean rebalancing your DAG before each backup, or perhaps just before each weekend backup)
      – dynamically backup databases based on where they are active at the time (this would be a bit of a scripting exercise but completely feasible)

      The first one is easy but costs more storage. The other two need a bit of work. They’d make good articles so I’ll see about writing them up 🙂

      Thanks again for a great question.

      1. Vijay K Kannojiya

        Hi Paul,

        I have once questions if our back team is scheduled the backup for exchange so how I can verify that backup is complete or pending on exchange server

Leave a Reply