functions/update-d365bacpacmodelfilesingletable.ps1
|
<# .SYNOPSIS Update the "model.xml" from the bacpac file to a single table .DESCRIPTION Update the "model.xml" file from inside the bacpac file to only handle a single table This can be used to restore a single table as fast as possible to a new data The table will be created like ordinary bacpac restore, expect it will only have the raw table definition and indexes, all other objects are dropped The output can be used directly with the Import-D365Bacpac cmdlet and its ModelFile parameter, see the example sections for more details .PARAMETER Path Path to the bacpac file that you want to work against It can also be a zip file .PARAMETER Table Name of the table that you want to be kept inside the model file when the update is done .PARAMETER Schema Schema where the table that you want to work against exists The default value is "dbo" .PARAMETER OutputPath Path to where you want the updated bacpac model file to be saved Default value is: "c:\temp\d365fo.tools" .PARAMETER Force Switch to instruct the cmdlet to overwrite the bacpac model file specified in the OutputPath .EXAMPLE PS C:\> Update-D365BacpacModelFileSingleTable -Path "c:\temp\d365fo.tools\bacpac.model.xml" -Table "SalesTable" This will create an updated bacpac.model.xml file with only the SalesTable to be imported. It will read the "c:\temp\d365fo.tools\bacpac.model.xml" file. It will use the default "dbo" as the Schema parameter. It will use the "SalesTable" as the Table parameter. It will use the "c:\temp\d365fo.tools\dbo.salestable.model.xml" as the default path for OutputPath parameter. .EXAMPLE PS C:\> Update-D365BacpacModelFileSingleTable -Path "c:\temp\d365fo.tools\bacpac.model.xml" -Table "CommissionSalesGroup" -Schema "AX" This will create an updated bacpac.model.xml file with only the "CommissionSalesGroup", from the "AX" schema, to be imported. It will read the "c:\temp\d365fo.tools\bacpac.model.xml" file. It will use the "AX" as the Schema for the table. It will use the "CommissionSalesGroup" as the Table parameter. It will use the "c:\temp\d365fo.tools\ax.CommissionSalesGroup.model.xml" as the default path for OutputPath parameter. .EXAMPLE PS C:\> Update-D365BacpacModelFileSingleTable -Path "c:\temp\d365fo.tools\bacpac.model.xml" -Table "SalesTable" -OutputPath "c:\temp\troubleshoot.xml" This will create an updated bacpac.model.xml file with only the SalesTable to be imported. It will read the "c:\temp\d365fo.tools\bacpac.model.xml" file. It will use the default "dbo" as the Schema parameter. It will use the "SalesTable" as the Table parameter. It will use the "c:\temp\troubleshoot.xml" as the path for OutputPath parameter. .EXAMPLE PS C:\> Export-D365BacpacModelFile -Path "c:\Temp\AxDB.bacpac" | Update-D365BacpacModelFileSingleTable -Table SalesTable This will create an updated bacpac.model.xml file with only the SalesTable to be imported. It will read the bacpac model file generated from the Export-D365BacpacModelFile cmdlet. It will use the default "dbo" as the Schema parameter. It will use the "SalesTable" as the Table parameter. It will use the "c:\temp\d365fo.tools\dbo.salestable.model.xml" as the default path for OutputPath parameter. .EXAMPLE PS C:\> Update-D365BacpacModelFileSingleTable -Path "c:\temp\d365fo.tools\bacpac.model.xml" -Table "SalesTable" -Force This will create an updated bacpac.model.xml file with only the SalesTable to be imported. It will read the "c:\temp\d365fo.tools\bacpac.model.xml" file. It will use the default "dbo" as the Schema parameter. It will use the "SalesTable" as the Table parameter. It will use the "c:\temp\d365fo.tools\dbo.salestable.model.xml" as the default path for OutputPath parameter. It will overwrite the "c:\temp\d365fo.tools\dbo.salestable.model.xml" if it already exists. .NOTES Tags: Bacpac, Servicing, Data, SqlPackage, Import, Table, Troubleshooting Author: Mötz Jensen (@Splaxi) #> function Update-D365BacpacModelFileSingleTable { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [CmdletBinding()] param ( [Parameter(ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [Alias('ModelFile')] [Alias('File')] [string] $Path, [Parameter(Mandatory = $true)] [string] $Table, [string] $Schema = "dbo", [string] $OutputPath = $Script:DefaultTempPath, [switch] $Force ) begin { Invoke-TimeSignal -Start if ([System.IO.File]::GetAttributes($OutputPath).HasFlag([System.IO.FileAttributes]::Directory)) { $OutputPath = Join-Path -Path $OutputPath -ChildPath "$Schema.$Table.model.xml" } if (-not $Force) { if ((-not (Test-PathExists -Path $OutputPath -Type Leaf -ShouldNotExist -ErrorAction SilentlyContinue -WarningAction SilentlyContinue))) { Write-PSFMessage -Level Host -Message "The <c='em'>$OutputPath</c> already exists. Consider changing the <c='em'>OutputPath</c> path or set the <c='em'>Force</c> parameter to overwrite the file." return } } if (Test-PSFFunctionInterrupt) { return } if ($Schema -NotLike "[*]") { $Schema = "[$Schema]" } if ($Table -NotLike "[*]") { $Table = "[$Table]" } } process { if (-not (Test-PathExists -Path $Path -Type Leaf)) { return } if (Test-PSFFunctionInterrupt) { return } $xmlFile = [System.Xml.XmlReader]::Create($Path) $settings = [System.Xml.XmlWriterSettings]::new() $settings.Indent = $true $settings.Encoding = [System.Text.UTF8Encoding]::new($false) $outFile = [System.Xml.XmlWriter]::Create($OutputPath, $settings) while ($xmlFile.Read()) { if ($xmlFile.NodeType -in "XmlDeclaration", "ProcessingInstruction") { $outFile.WriteProcessingInstruction($xmlFile.Name, $xmlFile.Value) } elseif ($xmlFile.NodeType -eq "Element" -and $xmlFile.Name -eq "DataSchemaModel") { $outFile.WriteStartElement($xmlFile.name, "http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02") if ($xmlFile.HasAttributes) { while ($xmlFile.MoveToNextAttribute()) { if ($xmlFile.name -ne "xmlns") { $outFile.WriteAttributeString($xmlFile.Name, $xmlFile.Value) } } } } elseif ($xmlFile.NodeType -eq "Element" -and $xmlFile.Name -eq "Model") { $outFile.WriteStartElement($xmlFile.name) } elseif ($xmlFile.NodeType -eq "Element" -and $xmlFile.Depth -eq 2) { $rawElement = $($xmlFile.ReadOuterXml() -replace 'xmlns=".*"', '') if (-not $($rawElement -match 'Type="(?<Type>.*?)".*?>')) { continue } if ($Matches.Type -NotIn "SqlSchema", "SqlTable", "SqlDatabaseOptions", "SqlGenericDatabaseScopedConfigurationOptions", "SqlIndex", "SqlPrimaryKeyConstraint") { continue } if ($Matches.Type -eq "SqlSchema") { if (-not $($rawElement -match 'Name="(?<Name>.*?)".*?>')) { continue } if ($Matches.Name -ne $Schema) { continue } Write-PSFMessage -Level Verbose -Message "SqlSchema found" -Target $rawElement } if ($Matches.Type -eq "SqlTable") { if (-not $($rawElement -match 'Name="(?<Name>.*?)".*?>')) { continue } if ($Matches.Name -ne "$Schema.$Table") { continue } Write-PSFMessage -Level Verbose -Message "SqlTable found" -Target $rawElement $rawElement = $rawElement -replace "\s*.*<AttachedAnnotation Disambiguator=`".*`".*/>", "" } if ($Matches.Type -eq "SqlIndex") { if (-not $($rawElement -match 'Name="(?<Name>.*?)".*?>')) { continue } if (-not $Matches.Name.StartsWith("$Schema.$Table", [System.StringComparison]::InvariantCultureIgnoreCase)) { continue } Write-PSFMessage -Level Verbose -Message "SqlIndex found" -Target $rawElement } if ($Matches.Type -eq "SqlPrimaryKeyConstraint") { if (-not $(([System.Xml.XmlDocument]$rawElement).SelectSingleNode("//Relationship[@Name='DefiningTable']/Entry/References/@Name")."#text").Equals("$Schema.$Table", [System.StringComparison]::InvariantCultureIgnoreCase)) { continue } Write-PSFMessage -Level Verbose -Message "SqlPrimaryKeyConstraint found" -Target $rawElement } $outFile.WriteRaw($rawElement) } else { if ($xmlFile.NodeType -eq "EndElement" -and $xmlFile.Name -in "Model", "DataSchemaModel") { $outFile.WriteEndElement() } } } [PSCustomObject]@{ File = $OutputPath Filename = $(Split-Path -Path $OutputPath -Leaf) } } end { if ($outFile) { $outFile.Flush() $outFile.Close() $outFile.Dispose() } if ($xmlFile) { $xmlFile.Close() $xmlFile.Dispose() } Invoke-TimeSignal -End } } |