internal/Get-SqlFileStructure.ps1

Function Get-SqlFileStructure
{
<#
.SYNOPSIS
Internal function. Returns custom object that contains file structures on destination paths (\\sqlserver\m$\mssql\etc\etc\file.mdf) for
source and destination servers.
#>

    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true, Position = 0)]
        [ValidateNotNullOrEmpty()]
        [object]$source,
        [Parameter(Mandatory = $true, Position = 1)]
        [ValidateNotNullOrEmpty()]
        [object]$destination,
        [Parameter(Mandatory = $false, Position = 2)]
        [bool]$ReuseSourceFolderStructure,
        [System.Management.Automation.PSCredential]$SourceSqlCredential,
        [System.Management.Automation.PSCredential]$DestinationSqlCredential
    )
    
    $sourceserver = Connect-SqlServer -SqlServer $Source -SqlCredential $SourceSqlCredential
    $source = $sourceserver.DomainInstanceName
    $destserver = Connect-SqlServer -SqlServer $Destination -SqlCredential $DestinationSqlCredential
    $destination = $destserver.DomainInstanceName
    
    $sourcenetbios = Resolve-NetBiosName $sourceserver
    $destnetbios = Resolve-NetBiosName $destserver
    
    $dbcollection = @{ };
    
    foreach ($db in $sourceserver.databases)
    {
        $dbstatus = $db.status.toString()
        if ($dbstatus.StartsWith("Normal") -eq $false) { continue }
        $destinationfiles = @{ }; $sourcefiles = @{ }
        
        # Data Files
        foreach ($filegroup in $db.filegroups)
        {
            foreach ($file in $filegroup.files)
            {
                # Destination File Structure
                $d = @{ }
                if ($ReuseSourceFolderStructure)
                {
                    $d.physical = $file.filename
                }
                else
                {
                    $directory = Get-SqlDefaultPaths $destserver data
                    $filename = Split-Path $($file.filename) -leaf
                    $d.physical = "$directory\$filename"
                }
                $d.logical = $file.name
                $d.remotefilename = Join-AdminUnc $destnetbios $d.physical
                $destinationfiles.add($file.name, $d)
                
                # Source File Structure
                $s = @{ }
                $s.logical = $file.name
                $s.physical = $file.filename
                $s.remotefilename = Join-AdminUnc $sourcenetbios $s.physical
                $sourcefiles.add($file.name, $s)
            }
        }
        
        # Add support for Full Text Catalogs in SQL Server 2005 and below
        if ($sourceserver.VersionMajor -lt 10)
        {
            foreach ($ftc in $db.FullTextCatalogs)
            {
                # Destination File Structure
                $d = @{ }
                $pre = "sysft_"
                $name = $ftc.name
                $physical = $ftc.RootPath
                $logical = "$pre$name"
                if ($ReuseSourceFolderStructure)
                {
                    $d.physical = $physical
                }
                else
                {
                    $directory = Get-SqlDefaultPaths $destserver data
                    if ($destserver.VersionMajor -lt 10) { $directory = "$directory\FTDATA" }
                    $filename = Split-Path($physical) -leaf
                    $d.physical = "$directory\$filename"
                }
                $d.logical = $logical
                $d.remotefilename = Join-AdminUnc $destnetbios $d.physical
                $destinationfiles.add($logical, $d)
                
                # Source File Structure
                $s = @{ }
                $pre = "sysft_"
                $name = $ftc.name
                $physical = $ftc.RootPath
                $logical = "$pre$name"
                
                $s.logical = $logical
                $s.physical = $physical
                $s.remotefilename = Join-AdminUnc $sourcenetbios $s.physical
                $sourcefiles.add($logical, $s)
            }
        }
        
        # Log Files
        foreach ($file in $db.logfiles)
        {
            $d = @{ }
            if ($ReuseSourceFolderStructure)
            {
                $d.physical = $file.filename
            }
            else
            {
                $directory = Get-SqlDefaultPaths $destserver log
                $filename = Split-Path $($file.filename) -leaf
                $d.physical = "$directory\$filename"
            }
            $d.logical = $file.name
            $d.remotefilename = Join-AdminUnc $destnetbios $d.physical
            $destinationfiles.add($file.name, $d)
            
            $s = @{ }
            $s.logical = $file.name
            $s.physical = $file.filename
            $s.remotefilename = Join-AdminUnc $sourcenetbios $s.physical
            $sourcefiles.add($file.name, $s)
        }
        
        $location = @{ }
        $location.add("Destination", $destinationfiles)
        $location.add("Source", $sourcefiles)
        $dbcollection.Add($($db.name), $location)
    }
    
    $filestructure = [pscustomobject]@{ "databases" = $dbcollection }
    return $filestructure
}