Database backup file names

PowerShell Help
1

I’m looking for a way to determine the last Full, Diff and subsequent Log backups of a database using Powershell and SMO.
I want to simplify what I’ve been doing previously, see below for example, I want to find away of having all the file names returned that I’d need to do a Pit restore without having to sue tsql.

$Server = “srv\inst”
$Database = “db”
$StatementTimeout=0

$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 (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)))
"@

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
$conn.applicationName = “PowerShell SMO”
$conn.StatementTimeout = $StatementTimeout
$conn.Connect()
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)

$db = $smo.Databases[“msdb”]
$latestfull = $db.ExecuteWithResults($qry)
$latestfull.Tables[0].Rows[0].Item(‘backup’)

2

Have you looked into http://dbatools.io ? It’s a PowerShell Community project focussed on SQL technology.

3

I’d not been on that particular one but there are other similar Communities I’d looked at but not found the solution to this. I have looked at that one now and not seeing anything that addresses this.

4

Can you contact Chrissy LeMaire on Twitter ( https://twitter.com/cl ) ? She’s the founder of this project, and for sure she can help you out. If she doesn’t know it, she can put you in contact with someone else for sure.

5

Thanks!