Lesson Learned #501: Exporting Multiple Databases Simultaneously with SqlPackage

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:
   
   • Be in the same region of the server.
     
   • The virtual machine needs to have enough space in the [iCODE]%temp%[/iCODE] folder for the temporary files generated during the process. Lesson Learned #21: There is not enough space on the disk exporting BacPac using SSMS - Microsoft Community Hub
     
   • The machine should have sufficient CPU capacity, ideally between 4 to 8 CPUs, depending on the volume of operations.
     
   • Use SSD or Premium storage for better performance.
     

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

[HEADING=3]Disclaimer[/HEADING]

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 - Microsoft Community Hub

Continue reading...