Kendra Little

12 August 2020

Backup Portability for Azure SQL Database Managed Instances

We sometimes receive questions from customers who are moving to use Microsoft’s Azure SQL Managed Instances as to how Redgate can help manage backups.

This is a tricky question because Microsoft’s documentation in this area can be difficult to understand — much depends on how you interpret the word “backup”.

Here’s where things go wrong

Some folks interpret a “backup” to be a way to efficiently create a point in time representation of the database which can be restored to both an Azure SQL Managed Instance and an “ordinary” installation of an SQL Server — perhaps in a VM in the cloud, and perhaps on-prem.

This belief is reinforced by:

• The fact that the backup file looks a whole lot like an ordinary backup file
• You might find comments and posts on the internet saying that some people have successfully restored backups from Azure SQL Managed instances successfully to on-prem SQL Server instances

Beware! Reality is not so simple

One of the most useful things to read here isn’t the documentation — it’s a feature request for Azure SQL Managed Instance, titled “Make backups portable to on-premise SQL.” The request notes that the user tried to restore a backup from an Azure SQL Managed Instance to an on-prem SQL Server, and got the message:

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.

Feature request for Azure SQL Managed Instance from Mitch Galehouse, Sept 2018

Yup, that’s right — while some people were previously able to restore backups from Managed Instances sometimes, not everyone was able to, depending on timing.

This is because Azure SQL Databases (whether they are in an Managed Instance or not), may at times be running a higher version of the SQL Server Engine than is available to people using non-Azure SQL Server.

And SQL Server doesn’t let you restore a higher major version to a lower major version.

Since 2018, this has shifted even further — in the comments of the feature request you will note that now users are not able to restore backups from Azure SQL Managed Instances to non-Azure SQL Servers, even when there doesn’t appear to be a version conflict:

System.Data.SqlClient.SqlError: 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. (Microsoft.SqlServer.SmoExtended)

Comment from Ajay Prakash, February 26, 2020

My understanding is that Microsoft made a small change in SQL Server’s restore process so that that instead of customers sometimes being able to restore backups from Managed Instances — an unsupported action that might fail if the Managed Instance happened to be upgraded  — Microsoft now detects the type of instance which created the backup and simply does not allow a Managed Instance backup to be restored to an on-prem instance.

Can’t you hack around that?

When DBAs are told they can’t do something, naturally some of us respond with, “Watch me!”

Yes, this is potentially something you could hack around. However — is basing your Disaster Recovery scenario around an unsupported hack really something you want to do?

Yeah. Probably not.

Signs that backups for Managed Instances may become portable to on-prem instances

After 18 months, the feature requests to make Azure SQL Managed Instance backups portable to on-prem databases got an update from Microsoft. It shares two things:

1. Backup and restore from MI to on-premises SQL is being considered
2. Scenarios for distributed Availability Groups for Azure SQL Managed Instances between Azure and on-prem datacenters are being considered.

While the second item in this list isn’t the exact same as portable backups, it seems like you’d very likely have the option to take a backup from the on-prem SQL Server in some way. While that might be a “special” instance of SQL Server, possibly it would make backups more portable.

If you’d like to see this change, please consider voting up the feature request, and also taking the survey linked in the feedback from ‘Admin’.

How to work with backups for Azure SQL Managed Instances

For now, your best bet is to work with the automated backups provided by Microsoft rather than looking to a third party backup tool to take backups for you.

There are other ways that Redgate solutions can help you work well with backups for Azure SQL Managed Instances:

• To understand the timing of events, such as troubleshooting actions, deployments, or other actions on your part, consider using Annotations in SQL Monitor to record when events occur within SQL Monitor. If you need to perform a point-in-time restore in a critical situation, half the battle is understanding the exact time you need to restore the backups to
• Use version control and pipelines to automate and test code deployments to make changes to your database predictable and reliable– and capture and react to database drift before it causes problems. A sound workflow utilizing version control and automation will help prevent the need to use backups to check “what happened?”

But as always — do ensure that you practice using restores, because a backup is only as good as your ability to use it.