Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Find the file-name of the most recent Full Database Backup for a SQL Server database
function Find-MostRecentFullBackup([string]$ServerInstance, $database="master"){
#$db = "Adventureworks"
$rs = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database msdb -Query "
WITH LatestBackupSet (database_name, BackupsAgo, backup_start_date, media_set_id)
SELECT database_name
, ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_start_date DESC) AS 'BackupsAgo'
, backup_start_date
, media_set_id
FROM msdb.dbo.backupset bs
WHERE type != 'L'
SELECT bf.physical_device_name, lbs.*
FROM dbo.backupmediafamily bf
JOIN LatestBackupSet lbs
ON lbs.media_set_id = bf.media_set_id
AND lbs.BackupsAgo = 1
AND family_sequence_number = 1
AND lbs.database_name = '$database'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment