Today, I worked on a scenario where our customer needs to export around 100 databases from SQL Server to Azure SQL Database. After checking the compatibility of the database using Microsoft Data Migration Assistant we found that the migration process we are not able to export at the same time multiple databases.
We created a PowerShell Script that you could find here where for every database that you have in your SQL Server Instance we will have a bacpac (exported file). Please, follow the instructions below:
- Step 1: Create a folder in your local drive called SqlPackage
- Step 2: Create a subfolder from SqlPackage\Log
- Step 3: Create a subfolder from SqlPackage\Script
- Step 4: Create a subfolder from SqlPackage\Files
- Step 5: Download and Copy the PowerShell Script in the subfolder \SQlPackage\Script from github.
- Step 6: Download and Copy the Windows Command Batch in the subfolder \SQlPackage\Script from github.
- Step 7: Identify the location of SQLPackage.exe that will be the executable that the PowerShell will execute for every database. In this case as I have SQL Server 2017 will be C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin
- Step 8: Modify the content of the Windows Command Batch by the location of the SQlPackage.
- Step 9: Edit the PowerShell Script and modify the parameters:
- $server with the name of your server and instance.
- $user with the name of the user that has access to all databases for reading.
- $password with the password of the user.
- Step 10: Execute the PowerShell Script. You will have in the folder \SqlPackage\Files all bacpac of your databases.
Feel free to modify the content either PowerShell or Windows Command Batch file.