How-to: Check Backup files

The script below can be used to check multiple locations for SQL Server backups that have completed in the last 24 hours.

<# check.ps1 Script to check SQL Server backups on multiple servers
   This script must be run with sqlps.exe (not generic PowerShell)

   Limitations: SQL 2000 is not supported, use for SQL Server 2005 and above only.
   The account running this script must have access to all the databases.
#> 


# Read all the databases to be checked into a hash table

$t = Import-Csv -Path C:\batch\databases.txt -Header "database","server"
$Hash = @{}
foreach($r in $t)
{
  Write-Host $r.database $r.server
  $Hash[$r.database] = $r.server
}
# $Hash


# Query to retrieve backups

# n.b  compressed_backup_size is only available in 2008 and later
# so for now we just use the uncompressed backup_size.

$query = "SELECT  sdb.Name AS DatabaseName,
 COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 103), '') AS LastBackUpTime,
 COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_size), 103), '') AS size
 FROM sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
 WHERE bus.backup_finish_date > getdate()-1
 GROUP BY sdb.Name
 HAVING  sdb.NAME IN ( '"
                         #$db' );"

$results = @{}
$grandTotal = 0
$countBackups = 0
$sqlcmd = 0

 # Loop through the hash table of databases/servers

$hash.keys | foreach {

   # Get the database and server names

   $db =$_
   $server =$($hash.item($_))
   # Write-Host "current server is: $server "
    Write-Host "Current db is: $db " 
   
   # complete the query string we started above
   $qry = "$query$db' );"
    # "$qry"
   # Reset the variable to clear last values out
   $sqlcmd = 0

   # Execute the query to get the last backup time/date
   $sqlcmd = Invoke-Sqlcmd -Query $qry -ServerInstance $server -database "master"

   # "$sqlcmd.LastBackUpTime"
   # $backup = $sqlcmd | Select -ExpandProperty LastBackUpTime

   # Right-pad database name to 17 characters

   [string]$name =  $_ + "                 "
   $name = $name.substring(0, 17)

   # Get the backup size

   [int]$megabytes = $sqlcmd.size/1mb
   [int]$grandTotal = $grandTotal + $megabytes
   [string]$size = "       $megabytes"
   $size = $size.substring($size.length - 7, 7) + " MB  $server"

   # Store the backup date/time + Size in another hash table called $results
   $results.add($name,$sqlcmd.LastBackUpTime + $size  )

   # Count the number of backups (this will change if/when the list of databases to check is changed.)
   $countBackups = $countBackups + 1
}

# Display results for interactive use

Write-Host "-- Display results"
$results.GetEnumerator() | sort name | ft -HideTableHeaders -autosize

# Store all the results into some text files

$results.GetEnumerator() | sort name | ft -HideTableHeaders -autosize > c:\batch\backups.txt
$grandTotal > c:\batch\total.txt
$countBackups > c:\batch\count.txt

To have these results emailed to a person or group use a standard PowerShell send-mailMessage script to read and email the text files.

"I pledge allegiance to my Flag and the Republic for which it stands, one nation, indivisible, with liberty and justice for all” ~ Francis Bellamy (1892)

Related commands

PowerShell backup check - Checks a list of file locations.