Add the -ScriptingOptionsObject parameter to Export-DbaDbTableData for parity with Export-DbaScript. #10778

Activity

1. charring

   added

   feature

   triage requiredNew issue that has not been reviewed by maintainersNew issue that has not been reviewed by maintainers

   on Oct 8, 2026
2. andreasjordan commented on Oct 11, 2026

   

   andreasjordan

   on Oct 11, 2026

   Collaborator

   More actions

   Thanks for the request, @charring. I checked this against the current development code and in a lab against SQL Server 2019.

   Why the USE is always there

   Export-DbaDbTableData is a thin wrapper around Export-DbaScript. It builds its own scripting options with IncludeDatabaseContext = $true hard-coded (Export-DbaDbTableData.ps1#L128-L131), and you cannot override them. -NoPrefix only drops the /* Created by ... */ comment header, exactly as it does in Export-DbaScript. The help for -NoPrefix says it also removes the USE statement. That is wrong, and it explains why you expected it to work. The wrong sentence came in with a help rewrite in #9810.

   Workaround until this is fixed

   Call Export-DbaScript directly with your own options. That is all Export-DbaDbTableData does internally:

   $options = New-DbaScriptingOption
   $options.ScriptSchema = $false
   $options.ScriptData = $true
   $options.IncludeDatabaseContext = $false
   
   Get-DbaDbTable -SqlInstance sql01 -Database MyDb -Table dbo.Table1, dbo.Table2 |
       Export-DbaScript -ScriptingOptionsObject $options -FilePath C:\temp\data.sql -NoPrefix

   This writes the INSERT statements of both tables into one file, with no USE and a GO after each statement. Add -BatchSeparator "" if you do not want the GO.

   Proposal

   I agree that -ScriptingOptionsObject is the better fix, and that -NoPrefix should stay as it is. In Export-DbaScript and Export-DbaSysDbUserObject, -NoPrefix means "no comment header". Making it also drop the USE here would silently change the scripts of everyone who uses it today. The new parameter would work as it does in Export-DbaDbRole and Export-DbaServerRole: without it you get the current defaults, and an object you pass is used exactly as given. So you set ScriptData = $true and ScriptSchema = $false yourself, as in the example above, or keep ScriptSchema = $true if you want the table definition as well.

   Two more problems I found while testing

   • If you pipe several tables into Export-DbaDbTableData -FilePath x.sql without -Append, only the last table ends up in the file. Each table overwrites the one before, because the command calls Export-DbaScript once per pipeline object. This is the problem reported in export-dbaDbTableData generates files with same timestamp #9044. That issue was closed by changing the examples to use -Append, but the behaviour itself was never fixed.
   • -BatchSeparator is declared with a default of "", but that default is never passed on to Export-DbaScript. Files therefore get the GO from the Formatting.BatchSeparator setting. The remark in the second example that the script "does not include Batch Separator" is wrong for the same reason.

   I will open one pull request for Export-DbaDbTableData that fixes all of this.

   ---

   This text was created by Claude and reviewed by Andreas Jordan.
3. andreasjordan

   added

   bugs life

   and removed

   triage requiredNew issue that has not been reviewed by maintainersNew issue that has not been reviewed by maintainers

   on Oct 11, 2026