How can we improve SQL Managed Instance?

Make backups portable to on-premise SQL

Get this message when attempting to restore an SMI backup to on-premise SQL:

The database was backed up on a server running version 15.00.0700. That version is incompatible with this server, which is running version xx.xx.xxxx. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Lock in is risky for customers for a number of reasons. A workaround is using BACPAC but getting an established database into a state where BACPAC doesn't spew errors is difficult in our experience.

47 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Mitch Galehouse shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Thanks for the feedback.

We assume that you are talking about ability to restore native backup (.bak files) taken from SQL MI to SQL Server 2017/2019.

It’s a tough one unfortunately. Native backup contains binary data, and never been backwards compatible. E.g. you can’t restore backup from SQL 2012 on SQL 2008. Same logic applies here.

SQL MI gets updates faster SQL Server, as soon we check-in code, it gets deployed with the next wave of updates on Azure. Same as SQL DB.
Theoretically if you take backup of SQL MI now, and wait until SQL Server version release catch up, then you will be able to restore it.

Can you please let us know what kind of issues with BACPAC you are encountering?

Also, you can consider making transaction replication from SQL MI on on-prem as a way to move data around.

3 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Lukas commented  ·   ·  Flag as inappropriate

    We got the information that DMS should do the job. The feature should be implemented some time in Q2/Q3. Can you confirm this?

  • Steve commented  ·   ·  Flag as inappropriate

    Or the option or opportunity to upgrade on premise version to match the MI version. Some off MI platform development, POCing, and testing is useful.

  • Brad commented  ·   ·  Flag as inappropriate

    Our use case for this request would be to be able to restore an archived backup of a database from within the past 3-7 years to be able to answer auditing questions. We would prefer not to have to create a new managed instance for this. Transaction replication would not work, BACPAC might work but is not ideal.

Feedback and Knowledge Base