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.
Example script to grant read-access.
#>
# 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.
“Despite all our achievements we owe our existence to a six-inch layer of topsoil and the fact that it rains” ~ anonymous
PowerShell backup check - Checks a list of file locations.