Database backup file names

Welcome Forums General PowerShell Q&A Database backup file names

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
1 year, 6 months ago.

  • Author
    Posts
  • #71330

    Participant
    Points: 72
    Rank: Member

    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

    Participant
    Points: 26
    Rank: Member

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

    • #71363

      Participant
      Points: 72
      Rank: Member

      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

    Participant
    Points: 26
    Rank: 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.

    • #71453

      Participant
      Points: 72
      Rank: Member

      Thanks!

The topic ‘Database backup file names’ is closed to new replies.