To quickly get a copy of our live Azure SQL database up and running on my local development SQL Server, I use SqlPackage to do all the hardwork.
I was previously using my BACKUP and RESTORE SQL script that works seamlessly when working with on-premise SQL servers, but that’s not on option when going between cloud and on-premise. It looked like SqlPackage would to do it all in one go.
Here’s the Command Shell script I created that I then drag and drop onto a Windows Terminal:
cd "C:\Program Files\Microsoft SQL Server\150\DAC\bin"
SqlPackage.exe /a:export /scs:"Server=YourAzureSqlServerName.database.windows.net;Database=YourAzureDatabaseNameHere;User ID=YourAzureUserID;password=YourAzurePassword;" /tf:"C:\temp\YourAzureDatabaseNameHere.bacpac"
docker cp "C:\temp\YourAzureDatabaseNameHere.bacpac" SQL1:/var/opt/mssql/backup
SqlPackage.exe /a:import /tcs:"Server=127.0.0.1;Database=YourLocalDatabaseNameHere;User ID=YourLocalUserID;password=YourLocalPassword;" /sf:"C:\temp\YourAzureDatabaseNameHere.bacpac"
The target database, in this case YourLocalDatabaseNameHere, should not already exist on your local server, otherwise SqlPackage bugs out. In that case, either delete the local database or change the target database name.
The Gremlin 👹
However, as time progressed, I frequently came across an error when the final command was executing, complaining about FK errors: “The ALTER TABLE statement conflicted with the FOREIGN KEY constraint….”
Running the script a second or third time usually got me going, but I knew something was afoot, as if new records were being captured mid-flow.
I eventually found an explanation that tied in with my gut feeling:
“The bacpac file is not transactional, so new rows written to your target database while the bacpac is being generated will end up corrupting the index. The database either must have no other users connected making writes, or you can copy the database and make a bacpac from the copy.”
An upgrade was needed.