Database backup file names

This topic contains 4 replies, has 2 voices, and was last updated by  iain Barnetson 2 months, 3 weeks ago.

  • Author
    Posts
  • #71330

    iain Barnetson
    Participant

    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')

  • #71335

    Richard Diphoorn
    Participant

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

    • #71363

      iain Barnetson
      Participant

      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.

  • #71386

    Richard Diphoorn
    Participant

    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.

You must be logged in to reply to this topic.