internal/Get-XpDirTreeRestoreFile.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
function Get-XpDirTreeRestoreFile
{
<#
.SYNOPSIS
Internal Function to get SQL Server backfiles from a specified folder using xp_dirtree
.DESCRIPTION
Takes path, checks for validity. Scans for usual backup file
.PARAMETER Path
.PARAMETER
#>

    [CmdletBinding()]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]$Path,
        [parameter(Mandatory = $true)]
        [Alias("ServerInstance", "SqlInstance")]
        [object]$SqlServer,
        [System.Management.Automation.PSCredential]$SqlCredential
    )
       
        $FunctionName =(Get-PSCallstack)[0].Command
        
        Write-Verbose "$FunctionName - Starting"
        Write-Verbose "$FunctionName - Checking Path"
        Try 
        {
            $srv = Connect-SQLServer -SqlServer $SqlServer -SqlCredential $SqlCredential
        }
        Catch
        {
            Write-Warning "$FunctionName - Cannot connect to $sqlServer" -WarningAction stop
        }

        if ($Path[-1] -ne "\")
        {
            $Path = $Path + "\"
        }
        If (!(Test-SqlPath -SQLServer $sqlserver -SqlCredential $SqlCredential -path $path))
        {
            Write-warning "$FunctionName - SQLServer $sqlserver cannot access $path" -WarningAction stop
        }
        $query = "EXEC master.sys.xp_dirtree '$Path',1,1;"
        $queryResult = Invoke-Sqlcmd2 -ServerInstance $sqlServer -Credential $SqlCredential -Database tempdb -Query $query
        #$queryresult
        $dirs = $queryResult | where-object { $_.file -eq 0 }
        $Results = @()
              $Results += $queryResult | where-object { $_.file -eq 1 } | Select @{Name="FullName";Expression={$PATH+$_."Subdirectory"}}
  
        ForEach ($d in $dirs) 
        {
            $fullpath = "$path$($d.Subdirectory)"
            Write-Verbose "Enumerating subdirectory '$fullpath'"
            $Results += Get-XpDirTreeRestoreFile -path $fullpath -SqlServer $SqlServer -SqlCredential $SqlCredential
        }
        return $Results
    
}