SQL Restore From Azure to On Prem Fails - Version Mismatch

Question

I am trying to restore a database from an Azure Managed Instance. The restore fails because the .bak "cannot be restored because it was created by a different version of the server (16.00.4025) than this server (16.00.1050).

When I do @@version on the managed instance, it says "Microsoft SQL Azure (RTM) - 12.0.2000.8

1. What do I need to do to get the target compatible with the managed instance?
2. Why does the MI show 12.0.2000 and not 16.00.4025?

Thank you.

Answer 1

To address this issue, you have a few options:

Upgrade the Azure Managed Instance: You can contact Azure support and request an upgrade to a version that is compatible with the backup file you're trying to restore. This will ensure that the Managed Instance matches the version of the backup file.

Export and import data: If it's feasible, you can export the data from the source server using a compatible version and then import it into the Azure Managed Instance. This will ensure compatibility between the backup and the Managed Instance.

Regarding the discrepancy in version numbers between what you see in the Managed Instance (12.0.2000.8) and the backup file (16.00.4025), it's important to note that the version number displayed in the Managed Instance (12.0.2000.8) represents the compatibility level of the Managed Instance, not the actual SQL Server version. Azure SQL Managed Instance provides a managed platform that abstracts the underlying infrastructure and handles updates and patching on your behalf. The Managed Instance may have a different version number because it's based on a specific release and includes Azure-specific optimizations.

To ensure a successful restore, it's crucial to have a compatible version between the backup file and the target server. Consider one of the options mentioned above to address the compatibility issue and restore the database to the Azure Managed Instance.

Answer 2

One option you have is to create a bacpac of the database instead of a .BAK file and import it using sqlpackage (SqlPackage /Action:Import) or using SQL Server Management Studio.

sqlpackage.exe /Action:Import /TargetConnectionString:"Server=(localdb)\mssqllocaldb;Initial Catalog={DBNAME};Integrated Security=True;Connection Timeout=30;" /sf:C:\temp\{DBNAME}.bacpac

Another option, you can also restore the .BAK successfully on a SQL Server 2022 instance. Here you will find how to do it.

Answer 3

What do I need to do to get the target compatible with the managed instance?

Apply the most recent Cumulative Update for SQL 2022, which is CU5 released today. The reason backup fails is that you have not applied any CU at all.

Why does the MI show 12.0.2000 and not 16.00.4025?

Because that's the way things are in SQL Azure. They don't expose the actual version.

When it comes to Azure SQL Managed Instance, there are two options: one is to stay in par with SQL 2022 to be able to exactly what you want to do: restore a backup on-prem. The other is to be cutting-edge like in SQL Azure Database. That is, you can get new features that changes the database format, so you cannot restore locally.