This topic contains 4 replies, has 2 voices, and was last updated by
May 23, 2017 at 8:11 pm #71330
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"
$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
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
$db = $smo.Databases["msdb"]
$latestfull = $db.ExecuteWithResults($qry)
May 23, 2017 at 8:27 pm #71335ParticipantPoints: 26Rank: Member
Have you looked into http://dbatools.io ? It's a PowerShell Community project focussed on SQL technology.
May 24, 2017 at 1:13 am #71363
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.
May 24, 2017 at 7:19 am #71386ParticipantPoints: 26Rank: Member
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.
May 24, 2017 at 6:47 pm #71453
The topic ‘Database backup file names’ is closed to new replies.