This web page requires JavaScript to be enabled.

JavaScript is an object-oriented computer programming language commonly used to create interactive effects within web browsers.

How to enable JavaScript?

Cannot Import the *.bak to GCP Cloud SQL due to compatibility level

Blog August 21, 2021 Nam Le 0

This question is mine from Stackoverflow for long time. Now I want to re-post here for anyone who want to reference.

I workd with Google Cloud Platform a few months, when moving the Lad from local to the CLoud, this issue is one of them that I faced.

  • My local SQL Server 2014, version: 120
  • GCP SQL Cloud Instance, running SQL Server 2017 Standard/Enterprise

Issue:

At the time, May 25th 2021, When performing importing the *.BAK to the GCP SQL Cloud, the SQL Cloud Instance is running SQL Server 2017 Standard,
I’m getting this error:

ERROR: (gcloud.sql.import.bak) [ERROR SQL SERVER EXTERNAL WARNING] 120 is not a supported compatibility level. Supported: [140, 130, 120, 110, 100]

Although my exporting server running 120 , checked by this query:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'mydb-name';

and this query:

SELECT @@version

The response said:

Microsoft SQL Server 2014 (SP3-CU4) – 12.0.6329.1 (X64) Enterprise Edition

Here’re my steps:

  • Exporting the BAK file from my server (120 version)
  • Create the SQL instance with SQL Server 2017 Standard, also created the user login (same as my exporting server)
  • Perform the import command:

cloud beta sql import bak my-sql-instance gs://my-bucket/file.bak --database=mydb-name

I have also tried with the Cloud SQL Enterprise but no luckily. All of them shown the error:

Cannot Import the *.bak to GCP Cloud SQL due to compatibility level

Solution

Since no one answer my question on stackoverflow, may be no one faced this issue, so I would like to update the solution for anyone who want to reference.

  • Import the *.bak to the server which is running SQL Server version >120, ex SQL Server 2016 or newer.
  • Do the Upgrade Db compatible level to >120
  • After that, do the backup the Db from this server
  • Using the GCP command to import again/Perform the import from GCP Console.

gcloud beta sql import bak my-sql-instance gs://my-bucket/file.bak --database=mydb-name

Upgrade Db compatible level

Nam Le, https://nready.net


Last modified on August 24th, 2021 at 7:00 pm

Nam Le
lequocnam



0 responds

Leave a Reply

Your email address will not be published. Required fields are marked *