Lesson Learned #501: Exporting Multiple Databases Simultaneously with SqlPackage
Published Jun 16 2024 07:52 AM 1,313 Views

This week, I've been working on a service request case where we need to export multiple databases using SqlPackage. Following, I would like to share my lesson learned to export  simultaneous several databases, saving the export files to the F:\sql folder and the logs of the operations to the F:\sql\log folder.

 

Few recommendations when performing these exports:

 

  1. Enable Accelerated Networking: This enhances data transfer performance.
  2. Virtual Machine:

 

# Define the path to the SqlPackage.exe file
$SqlPackagePath = "C:\Program Files\Microsoft SQL Server\XXX\DAC\bin\SqlPackage.exe"

# Define the server, user, and password
$serverName = "servername.database.windows.net"
$username = "username"
$password = "password"
$outputFolder = "f:\sql"
$logFolder = "f:\sql\logs"
$databaseList = @("db1", "db2")  

# Create the logs folder if it does not exist
if (-Not (Test-Path -Path $logFolder)) {
    New-Item -ItemType Directory -Path $logFolder
}

foreach ($database in $databaseList) {
    $outputFile = Join-Path -Path $outputFolder -ChildPath "$database.bacpac"
    $logFile = Join-Path -Path $logFolder -ChildPath "$database.log"
    $errorLogFile = Join-Path -Path $logFolder -ChildPath "$database-error.log"
    
    $args = @(
        "/Action:Export",
        "/ssn:$serverName",
        "/sdn:$database",
        "/su:$username",
        "/sp:$password",
        "/tf:$outputFile"
    )
    
    # Start the background job with log redirection
    Write-Host "Starting export of database $database to $outputFile in background"
    Start-Job -ScriptBlock {
        param($exePath, $arguments, $log, $errorLog)
        & $exePath @arguments *>$log 2>$errorLog
    } -ArgumentList $SqlPackagePath, $args, $logFile, $errorLogFile
}

Write-Host "Background export jobs started for all databases."

# Wait for all background jobs to complete
$jobs = Get-Job
foreach ($job in $jobs) {
    Wait-Job -Job $job
    $jobDetails = Receive-Job -Job $job
    Write-Host "Job ID $($job.Id) completed with state: $($job.State)"
    Remove-Job -Job $job
}

Write-Host "Export completed for all databases. Logs are located in the $logFolder folder."

 

Disclaimer

The script provided above is intended for illustrative purposes only. Before using it in a production environment, thoroughly review and test the script to ensure it meets your needs and adheres to your organization's security and operational policies. Always safeguard sensitive information such as credentials and server details.

 

Also, remember that sqlpackage exports the data but does not guarantee transactional consistency. You will find more details about here: Using data-tier applications (BACPAC) to migrate a database from Managed Instance to SQL Server - Mi...

Version history
Last update:
‎Jun 16 2024 07:52 AM
Updated by: