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.
<code># 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."
</code>
<code># 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."
</code>
# 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.