Powershell is a great tool to use for a multitude of SQL Server administrative functions. I frequently need to know or get the file name of the last backup of a database. Sometimes I just need it for a report or to pass it off to another function. I created a Powershell function to obtain the data for me and had been using it for some time. Unfortunately, it recently broke down.

After some debugging, I was able to find the cause of my problem and I thought it would be a good idea to post my new and improved function. The function had to be changed because of snapshots being taken by VEEAM backups. We recently built a new SQL Server on VMWare and I implemented my normal backup methodology. However, when I recently wanted a report of the latest backups, my Powershell function failed! VEEAM snapshots are reported as backups but there is no file name associated with it. The new function excludes all backups which are snapshots.

I’ll first post the T-SQL to obtain the latest backup for a database and then I’ll show you how to use it in a Powershell function.

DECLARE @dbname sysname
SET		@dbname = 'YOURDB'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7)
			AND (s.backup_finish_date = (SELECT     MAX(backup_finish_date)
FROM        msdb.dbo.backupset WITH (nolock)
WHERE	(database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))

And now the Powershell use of it.

Param(
	[Parameter(Mandatory=$true,Position=0)]
	[string]$server,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$database
)
Function New-SMOconnection {
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to SQL Server $server."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {Throw $Error}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-LastBackupFile {
	Param(
		[string]$server,
		[string]$database
	)
	<# 	Use a hereto to construct the T-SQL
		You will notice the query eliminates any snapshots. This is because we
		sometimes have VEEAM backups on some servers.
	#>
	$qry = @"
DECLARE @dbname sysname 
SET @dbname = '$database'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7) 
		AND (s.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM         msdb.dbo.backupset WITH (nolock)
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))
"@

	# Get an SMO Connection
	$smo = New-SMOconnection -server $server
	# most appropriate to use MSDB
	$db = $smo.Databases["msdb"]
	# Execute query with results
	$rs = $db.ExecuteWithResults($qry)
	# SMO connection is no longer needed
	$smo.ConnectionContext.Disconnect()
	# Return the result
	$rs.Tables[0].Rows[0].Item('backup')
}
# Load SMO Assemblies
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

# Call the function and trap any error
Try {$backup = Get-LastBackupFile -server $server -database $database}
Catch {
	$ex = $Error[0].Exception
	Write-Host $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Host $ex.Message
	}
}
<# Verify the file  
	NOTE: most developent and run-time is performed on remote servers
	so there may be a need to convert to UNC format
#>
$backup = Join-Path "\\$($server.split('\')[0])" $backup.replace(':','$')

if(!(Test-Path $backup)) {
	Throw "Database backup $backup not found"
}
Write-Host $backup