For many years we could use this post to restore the Azure *.bacpac file with the backup of our user group web site. As I tried it today, I ended up with a long wait time (nearly 20 minutes) and multiple errors:
The error message of the first failed step showed the problem:
Could not import package.
Warning SQL72012: The object [dnugbernbdd_Data] exists in the target, but it will not be dropped even though you selected the ‘Generate drop statements for objects that are in the target database but that are not in the source’ check box.——————————
ADDITIONAL INFORMATION:Online index operations can only be performed in Enterprise edition of SQL Server. (Microsoft SQL Server, Error: 1712)
Since I only have the Standard Edition installed, I could not restore the *.bacpac file.
Use Docker for the *.bacpac file
If we can use Docker, we get a suitable edition from Microsoft without the need for a special license key. All we need to do is to run this Docker command:
1 |
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssword" -e "MSSQL_PID=Enterprise" -p 14333:1433 -v c:\temp:/disk -d mcr.microsoft.com/mssql/server:2019-latest |
This fetches the latest SQL Server 2019 image and runs it on port 14333. It also points the /disk folder inside the container to the c:\temp directory. If you have a SQL Server 2022 you can change the command above to get the 2022-latest image.
We can now point Microsoft SQL Server Management Studio to our container and use these values to connect:
- Server name: localhost,14333
- Authentication: SQL Server Authentication
- Username: sa
- Password: P@ssword
Inside the container, we can import the data-tier application and get the backup extracted within seconds. We can backup the database to the /disk folder and then access the created *.bak file though the c:\temp folder on the Windows host. From here on it is our usual backup restore and works without any problems.
Conclusion
The change by Microsoft gives us a lot more work to restore a *.bacpac file. Thanks to Docker, it is doable without spending additional money on a higher edition.
1 thought on “How to Restore a *.BACPAC File Without SQL Server Enterprise Edition?”