Importing BAK Files - Unknown Error

[jedihomer]

Is it possibly to get more extended logging information on importing BAK files to a Cloud SQL Server instance?

I'm currently trying to move our Dev DBs to a Cloud SQL Instance, however it always ends with an Unknown error after the 'Planning begins' step.

If I create a new DB, export it, upload and import it, then it all goes fine.

However our actual working databases don't.  It never gets passed the 'Planning begins' step in the logs via either the console or via

gcloud logging read "resource.type=cloudsql_database" --project={project} --limit=10 --format=json > ./logging.json

The local dev Dbs export and import fine locally, but not to cloud.

The development setup is a Docker image running SQL Server 2019.  Our cloud instance is 2019.  I've tried changing the compatibility level to ensure it's running 2019 but the same affect.

Again, if I create a brand new database, then it all goes fine.  If I use a backup from Staging/Live (which are both Windows VMs) then it all goes fine.

I'm just trying to figure out what is wrong with the dev DBs, that I can hopefully fix so that I can import our development environment into the cloud.

Thanks

[jedihomer · Accepted solution]

Well, that took too long for me to diagnose.  In this case it was down to a typo!!

We store the ldf files in a different location to the mdf files.  There was a typo in the restore database code moving the ldf file to the same name as the mdf file.  This causes a fail to restore if the files are to be stored in the same location, which I assume Cloud SQL does.

i.e.

DB
--- dbname - /var/opt/mssql/data/dbname.mdf
--- dbname_log - /var/opt/mssql/log/dbname.mdf

Will back up and restore fine in my Dev environment due to the directories being different.  I assume Cloud SQL is trying to do this

DB
--- dbname - /var/opt/mssql/data/dbname.mdf
--- dbname_log - /var/opt/mssql/data/dbname.mdf

Which is where the issue lies.  Backing up, restoring with the correct file names and backing up again fixed this.