One of my Customers asked me to implement Azure PAAS database for PoC. I am not a SQL guru and when the database was set I went to google to see how to migrate data.
Microsoft recommends to use export/import data using bacpac file.
So we exported db to bacpac file (took 1.5 hour for a small 500MB DB). , copy it to azure VM and after that I tried to use SQL Management Studio to import the bacpac into the new Azure database as described here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate-compatible-import-bacpac-ssms
The attempt miserably failed with the following error:
“An Exception occured while executing a Transact-TSQL statement or batch (Microsoft.SqlServer.ConnectionInfo)
Additional Information:
The connection is broken and recovery is not possible. The client driver attempted to recover connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server)
Cannot open database “mydatabasename” requested by login. The login failed. Login failed for user ’myusername’. (Microsoft SQL Server, Error: 4060)
Unusual resolution: I tried to restore the bacpac to an existing database. This attempt naturally failed with error “database already exists. Try to restore into a new database”. After that I put a name of the saved database and this time bacpac was restored successfully (it took ~1 hour again )