Lesson Learned #146: Restoring a backup from Azure SQL Managed Instance to OnPremises

This article is contributed. See the original author and article here.

We saw several service request where our customer want to restore a backup taken in Azure SQL Managed Instance to SQL Server OnPremise and they are getting the following error: Msg 3169, Level 16, State 1, Line 1 The database was backed up on a server running version xx.xx.xxxx. 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.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

That error came because Native COPY_ONLY backups taken from managed instance cannot be restored to SQL Server because managed instance has a higher database version compared to SQL Server. For more details, see Copy-only backup.

Due to this limitation, I would like to suggest to use bacpac method or if you need to have updated both environments at the same time use transactional replication. 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.