Backup from Azure SQL Managed Instance and restore on an On-Prem version

Question

As per my understanding Azure SQL MI runs SQL Server under the hood. So a backup from MI can be restored on latest version of SQL Server (2019). I have a task to find a rollback strategy if the move to MI is called off after the migration.

1. Took backup from MI

2. Tried restore to SQL Server 2019 (15.0.2070.41)

I expected it a 50/50 chance of success but it failed. The error however is a bit misleading :

Msg 3169, Level 16, State 1, Line 2 The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.2070. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally

If we can't go back smoothly to server we got the backup from then how do we plan the rollback. The bacpac/dacpac method is a it of pain when db size is 30GB and you get loads of errors.

1. Why is that error coming up? Need to know whats running in MI

2. Shall we reconsider moving to MI, with the pain of not having an easy/smooth rollback.

PS: AWS RDS provides versions which makes it much better as you have the option to just rollback if something goes wrong in the cutover.

.

Answer 1

Currently the only way to restore a database from Azure to OnPrem is the bacpac method.

Also FYI: I am currently looking at Azure MI migration but due to the fact you can't point a DNS CNAME at it I am unable to Migrate.

• Ramakant Dadhichi: Bacpac is a pain when the db size is of considerable size and then the bunch of errors. Voted in the MS site for the backup/restore feature. The version is such a misleading thing in that error..