Skip to content

Commit d5da4b9

Browse files
andreasjordanandreasjordan
authored
Export-DbaDbTableData, Export-DbaScript - Add -ScriptingOptionsObject, keep every piped table and fix file output with batch terminators (#10782)
1 parent b6d1354 commit d5da4b9

4 files changed

Lines changed: 234 additions & 17 deletions

File tree

‎public/Export-DbaDbTableData.ps1‎

Lines changed: 63 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -4,12 +4,19 @@ function Export-DbaDbTableData {
44
Generates INSERT statements from table data for migration and deployment scripts
55
66
.DESCRIPTION
7-
Creates executable INSERT statements from existing table data, making it easy to move data between SQL Server instances or environments. This is particularly useful for migrating reference tables, lookup data, or configuration tables where you need the actual data values rather than just the table structure. The generated scripts include proper USE database context and can be saved to files or piped to other commands for further processing.
7+
Creates executable INSERT statements from existing table data, making it easy to move data between SQL Server instances or environments. This is particularly useful for migrating reference tables, lookup data, or configuration tables where you need the actual data values rather than just the table structure. By default the generated scripts start with a USE statement for the database of each table. They can be saved to files or piped to other commands for further processing.
8+
9+
Tables piped into one call that write to the same file all end up in that file, even without -Append.
810
911
.PARAMETER InputObject
1012
Accepts table objects from Get-DbaDbTable through the pipeline.
1113
Use this to process specific tables you've already identified rather than specifying table names again.
1214
15+
.PARAMETER ScriptingOptionsObject
16+
Accepts an SMO ScriptingOptions object created with New-DbaScriptingOption to control script generation.
17+
Without it, the command scripts the data only (ScriptData = $true, ScriptSchema = $false) and includes the USE statement (IncludeDatabaseContext = $true).
18+
An object you pass is used exactly as given, so set ScriptData = $true yourself. Set IncludeDatabaseContext = $false to omit the USE statement, or keep ScriptSchema = $true to script the table definition as well.
19+
1320
.PARAMETER Path
1421
Sets the directory where output files will be created when not using FilePath.
1522
Defaults to the dbatools export directory configured in module settings.
@@ -37,12 +44,12 @@ function Export-DbaDbTableData {
3744
Useful for reviewing the script content before execution or when piping to other commands.
3845
3946
.PARAMETER BatchSeparator
40-
Adds batch separators (like GO) between INSERT statements in the output script.
41-
Use this when creating deployment scripts that will be executed in SQL Server Management Studio or sqlcmd.
47+
Adds a batch separator (like GO) after every statement in the output file, and in the output of -Passthru.
48+
Defaults to the Formatting.BatchSeparator configuration value, which is GO. Pass an empty string to write the statements without a separator.
4249
4350
.PARAMETER NoPrefix
44-
Excludes the USE database statement and other prefixes from the generated script.
45-
Use this when combining output with other scripts or when the database context is already established.
51+
Excludes the comment header with the creator, timestamp, and dbatools version from the generated script.
52+
This does not remove the USE statement. To omit it, pass a ScriptingOptionsObject with IncludeDatabaseContext set to $false.
4653
4754
.PARAMETER NoClobber
4855
Prevents overwriting existing files at the specified FilePath.
@@ -89,7 +96,7 @@ function Export-DbaDbTableData {
8996
9097
System.String (when -Passthru is specified)
9198
92-
Returns the generated INSERT statements as string output. Multiple strings are returned for table data scripts, one per INSERT statement or batch. Use -BatchSeparator parameter to control statement separation with GO or other batch terminators.
99+
Returns the generated INSERT statements as string output. Multiple strings are returned for table data scripts, one per INSERT statement or batch. Each one ends with the batch separator of -BatchSeparator, as in the file.
93100
94101
.EXAMPLE
95102
PS C:\> Get-DbaDbTable -SqlInstance sql2017 -Database AdventureWorks2014 -Table EmployeePayHistory | Export-DbaDbTableData
@@ -100,23 +107,34 @@ function Export-DbaDbTableData {
100107
PS C:\> Get-DbaDbTable -SqlInstance sql2017 -Database AdventureWorks2014 -Table EmployeePayHistory | Export-DbaDbTableData -FilePath C:\temp\export.sql -Append
101108
102109
Exports data from EmployeePayHistory in AdventureWorks2014 in sql2017 using a trusted connection - Will append the output to the file C:\temp\export.sql if it already exists
103-
Script does not include Batch Separator and will not compile
110+
Every statement is followed by the batch separator from the Formatting.BatchSeparator configuration value, which is GO
104111
105112
.EXAMPLE
106113
PS C:\> Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase -Table 'dbo.Table1', 'dbo.Table2' -SqlCredential sqladmin | Export-DbaDbTableData -FilePath C:\temp\export.sql -Append
107114
108115
Exports only data from 'dbo.Table1' and 'dbo.Table2' in MyDatabase to C:\temp\export.sql and uses the SQL login "sqladmin" to login to sql2016
116+
117+
.EXAMPLE
118+
PS C:\> $options = New-DbaScriptingOption
119+
PS C:\> $options.ScriptSchema = $false
120+
PS C:\> $options.ScriptData = $true
121+
PS C:\> $options.IncludeDatabaseContext = $false
122+
PS C:\> Get-DbaDbTable -SqlInstance sql2017 -Database MyDatabase -Table dbo.Table1, dbo.Table2 | Export-DbaDbTableData -FilePath C:\temp\export.sql -ScriptingOptionsObject $options -NoPrefix
123+
124+
Exports the data of both tables to C:\temp\export.sql without the USE statement and without the comment header, so the script runs in whatever database it is executed in
109125
#>
110126
[CmdletBinding(SupportsShouldProcess)]
111127
param (
112128
[parameter(Mandatory, ValueFromPipeline)]
113129
[Microsoft.SqlServer.Management.Smo.Table[]]$InputObject,
130+
[Alias("ScriptingOptionObject")]
131+
[Microsoft.SqlServer.Management.Smo.ScriptingOptions]$ScriptingOptionsObject,
114132
[string]$Path = (Get-DbatoolsConfigValue -FullName 'Path.DbatoolsExport'),
115133
[Alias("OutFile", "FileName")]
116134
[string]$FilePath,
117135
[ValidateSet('ASCII', 'BigEndianUnicode', 'Byte', 'String', 'Unicode', 'UTF7', 'UTF8', 'Unknown')]
118136
[string]$Encoding = 'UTF8',
119-
[string]$BatchSeparator = '',
137+
[string]$BatchSeparator = (Get-DbatoolsConfigValue -FullName "Formatting.BatchSeparator"),
120138
[switch]$NoPrefix,
121139
[switch]$Passthru,
122140
[switch]$NoClobber,
@@ -125,15 +143,47 @@ function Export-DbaDbTableData {
125143
)
126144

127145
begin {
128-
$ScriptingOptionsObject = New-DbaScriptingOption
129-
$ScriptingOptionsObject.ScriptSchema = $false
130-
$ScriptingOptionsObject.ScriptData = $true
131-
$ScriptingOptionsObject.IncludeDatabaseContext = $true
146+
if (Test-Bound -Not -ParameterName ScriptingOptionsObject) {
147+
$ScriptingOptionsObject = New-DbaScriptingOption
148+
$ScriptingOptionsObject.ScriptSchema = $false
149+
$ScriptingOptionsObject.ScriptData = $true
150+
$ScriptingOptionsObject.IncludeDatabaseContext = $true
151+
}
152+
153+
# All tables go through one Export-DbaScript pipeline, so the tables after the first are appended
154+
# to the file instead of overwriting it. Calling Export-DbaScript once per table restarted its file handling every time.
155+
$splatExport = @{ }
156+
foreach ($boundName in $PSBoundParameters.Keys) {
157+
if ($boundName -ne "InputObject") {
158+
$splatExport[$boundName] = $PSBoundParameters[$boundName]
159+
}
160+
}
161+
$splatExport["ScriptingOptionsObject"] = $ScriptingOptionsObject
162+
$splatExport["BatchSeparator"] = $BatchSeparator
163+
$exportPipeline = $null
132164
}
133165

134166
process {
135167
if ($Pscmdlet.ShouldProcess($env:computername, "Exporting $InputObject")) {
136-
Export-DbaScript @PSBoundParameters -ScriptingOptionsObject $ScriptingOptionsObject
168+
# Started on the first table, so -WhatIf never reaches Export-DbaScript
169+
if ($null -eq $exportPipeline) {
170+
$exportCommand = { Export-DbaScript @splatExport }
171+
# Begin($true, $ExecutionContext) connects the pipeline to this command, so the warnings of Export-DbaScript reach
172+
# -WarningVariable, which Begin($true) does not. Begin($PSCmdlet) connects it too, but takes from this command
173+
# whether input is expected, and none is when the tables come through -InputObject. Every table goes in through
174+
# Process below.
175+
$exportPipeline = $exportCommand.GetSteppablePipeline($MyInvocation.CommandOrigin)
176+
$exportPipeline.Begin($true, $ExecutionContext)
177+
}
178+
foreach ($table in $InputObject) {
179+
$exportPipeline.Process($table)
180+
}
181+
}
182+
}
183+
184+
end {
185+
if ($null -ne $exportPipeline) {
186+
$exportPipeline.End()
137187
}
138188
}
139189
}

‎public/Export-DbaScript.ps1‎

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ function Export-DbaScript {
2525
Controls the character encoding used when writing script files to disk.
2626
Default is UTF8 which handles international characters and is widely supported.
2727
Use UTF8 for most scenarios, or ASCII if you need compatibility with older tools that don't support Unicode.
28+
This also applies to the statements SMO writes to the file when the ScriptingOptionsObject has ScriptBatchTerminator set, so the Encoding and AnsiFile settings of the ScriptingOptionsObject are not used for files.
2829
2930
.PARAMETER Passthru
3031
Returns the generated T-SQL script as string output instead of writing to a file.
@@ -34,7 +35,7 @@ function Export-DbaScript {
3435
.PARAMETER ScriptingOptionsObject
3536
Accepts a customized SMO ScriptingOptions object created with New-DbaScriptingOption to control script generation.
3637
Use this when you need specific formatting like including schema context, headers, or data along with object definitions.
37-
Settings in this object override other Export-DbaScript parameters like BatchSeparator.
38+
BatchSeparator still separates the statements, except in a file written with ScriptBatchTerminator set, where SMO writes its own batch terminator.
3839
3940
.PARAMETER BatchSeparator
4041
Sets the batch terminator added between SQL statements in the output script.
@@ -200,6 +201,18 @@ function Export-DbaScript {
200201
$soAppendToFile = $ScriptingOptionsObject.AppendToFile
201202
$soToFileOnly = $ScriptingOptionsObject.ToFileOnly
202203
$soFileName = $ScriptingOptionsObject.FileName
204+
$soEncoding = $ScriptingOptionsObject.Encoding
205+
206+
# With ScriptBatchTerminator, SMO appends to the file itself, in the encoding of the scripting options (UTF-16 unless changed).
207+
# Out-File has already written the prefix in $Encoding, so SMO has to use the same encoding, or the file mixes two.
208+
$smoEncoding = switch ($Encoding) {
209+
"ASCII" { [System.Text.Encoding]::ASCII }
210+
"BigEndianUnicode" { [System.Text.Encoding]::BigEndianUnicode }
211+
"UTF7" { [System.Text.Encoding]::UTF7 }
212+
# No byte order mark: Windows PowerShell has already written one with the prefix, PowerShell 7 writes none
213+
"UTF8" { New-Object System.Text.UTF8Encoding -ArgumentList $false }
214+
default { [System.Text.Encoding]::Unicode }
215+
}
203216
}
204217

205218
$eol = [System.Environment]::NewLine
@@ -310,8 +323,10 @@ function Export-DbaScript {
310323
if ($ScriptingOptionsObject) {
311324
$ScriptingOptionsObject.FileName = $null
312325
foreach ($scriptpart in @(if ($dagScript) { $dagScript } else { $scripter.EnumScript($object) })) {
313-
if ($scriptBatchTerminator) {
326+
if ($BatchSeparator) {
314327
$scriptpart = "$scriptpart$eol$BatchSeparator$eol"
328+
} else {
329+
$scriptpart = "$scriptpart$eol"
315330
}
316331
$scriptpart | Out-String
317332
}
@@ -331,13 +346,15 @@ function Export-DbaScript {
331346
# Option to script batch terminator via ScriptingOptionsObject needs to write to file only
332347
$ScriptingOptionsObject.AppendToFile = $true
333348
$ScriptingOptionsObject.ToFileOnly = $true
349+
$ScriptingOptionsObject.Encoding = $smoEncoding
334350
if (-not $ScriptingOptionsObject.FileName) {
335351
$ScriptingOptionsObject.FileName = $scriptPath
336352
}
337353
if ($dagScript) {
338354
"$dagScript$eol$BatchSeparator$eol" | Out-File -FilePath $ScriptingOptionsObject.FileName -Encoding $encoding -Append
339355
} else {
340-
$null = $object.Script($ScriptingOptionsObject)
356+
# The Script method of the object refuses ScriptData, the scripter writes the data too
357+
$null = $scripter.EnumScript($object)
341358
}
342359
} else {
343360
$ScriptingOptionsObject.FileName = $null
@@ -374,13 +391,15 @@ function Export-DbaScript {
374391
if (-not $message) {
375392
$message = $_.Exception
376393
}
377-
Stop-Function -Message "Failure on $($server.Name) | $message" -Target $server
394+
# Continue with the next object, so one object that cannot be scripted does not stop the export of the objects after it
395+
Stop-Function -Message "Failure on $($server.Name) | $message" -Target $server -Continue
378396
} finally {
379397
# Reset the changed values of the $ScriptingOptionsObject in case it is reused later
380398
if ($ScriptingOptionsObject) {
381399
$ScriptingOptionsObject.AppendToFile = $soAppendToFile
382400
$ScriptingOptionsObject.ToFileOnly = $soToFileOnly
383401
$ScriptingOptionsObject.FileName = $soFileName
402+
$ScriptingOptionsObject.Encoding = $soEncoding
384403
}
385404
}
386405
}

0 commit comments

Comments
 (0)