First published on MSDN on Mar 22, 2018
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.
You could find the instructions in
Spanish
and
English
Enjoy!
