I’ve got the following powershell script to export specific tables from a database using dbatools and Export-DbaDbTableData. I do this as part of some automation.

# Define your parameters
...

#SQL Auth
$username = ""
$password = ""
$credential = New-Object System.Management.Automation.PSCredential($username,$password)


Set-DbatoolsInsecureConnection -SessionOnly

# Loop through each table
foreach ($table in $tables) {

Write-Host "Starting Table $table."

    # Get the table data
    $tableData = Get-DbaDbTable -SqlInstance $sqlInstance -Database $database -SqlCredential $credential -Table $table
    
    # Define the output file path with the table name
    $filePath = Join-Path -Path $outputPath -ChildPath "$table.sql"
    
    # Export the table data to the file
    $tableData | Export-DbaDbTableData -FilePath $filePath
    
}

Write-Host "Data export completed."

It works and exports to a SQL file as expected. However, I’m having an issue where it will not escape single quotes in data. So I end up with a SQL insert statement that may break the eventual import, so something like this;

INSERT INTO MyTable (MyColumn) VALUES (‘O’Reilly’);

which can turn up whereever in the full SQL export, then it fails on import, Whereas I believe I need this

INSERT INTO MyTable (MyColumn) VALUES (‘O”Reilly’);

I’ve had a look and I think it’s because dbatools doesn’t ‘parameterize’ the values. I can export manually from SSMS using the generate scripts for each table and that does escape it as expected – but I can’t automate this. Any suggestions on what I can do with dbatools or other scripts to get around this and keep it automated?

thanks in advanced.