Blog Post

Azure Database Support Blog
1 MIN READ

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

Jose_Manuel_Jurado's avatar
Jose_Manuel_Jurado
Icon for Microsoft rankMicrosoft
Nov 23, 2020

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. 

 

Updated Nov 23, 2020
Version 2.0
  • NielsenBS's avatar
    NielsenBS
    Copper Contributor
    Feb 16, 2022

    Hello I'm using a managed instance of sql azure.

     

    I have 4 databases on the server. These 4 databeses are fully interconnected by views and procedures.

     

    How can I copy these databases to SQL on primise?

     

    BAK file = Version error.

     

     

    BacPac File = Schema Error

     

     

  • Arnold_Lieberman's avatar
    Arnold_Lieberman
    Copper Contributor
    May 24, 2022

    I have the same issue. Until SQL Server 2022 is released it is impossible to restore a database from SQL MI into SQL Server if it contains objects with three-part references. We are using them as part of an expand/contract deployment that will see the monolithic database be broken up into several smaller databases.

    Also, we need to use beefy SQL Server VMs to do data obfuscation. These can be deallocated when not in use. SQL MI does not permit the deallocation and re-allocation of an instance in the same way so would cost far too much to leave spun up for days whilst not being used.

     

  • Arnold_Lieberman's avatar
    Arnold_Lieberman
    Copper Contributor
    Jun 01, 2022

    As a follow-up to my comment above, I have now successfully tested restoring a BYOK TDE-encrypted native backup that was created by SQL MI into SQL Server 2022.

    The limitation of not being able to spin up/down SQL MI on demand still stands but SQL Server has become a viable route to bursty processing of data.