internal/Get-SqlFileStructure.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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
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 } |