locked
Database backup in SQL Azure managed instance RRS feed

  • Question

  • Hello, 

    I want to back up a database in my SQL Azure Managed instance to bring it to my local computer for some tests but I don't find the option. 

    I have "SQLAzureMW.exe" to migrate databases but it does not work with this managed instance.

    How can I make the backup?

    Thanks a lot.  

    Thursday, June 25, 2020 8:38 AM

Answers

  • Hello, 

    After too much tests I realized that the latest version of SQL Server Management Studio allow me to export the Azure database but not import it because my SQL managed instance is still 2014 version so it isn't compatible. 

    How complicated is it to get a database from azure? If I want to take that database to a local physical server with another instance of SQL Server, other than 2019 version, what do I have to do? I'm desperate.

    BACPAC is the answer. Forget about backup and restore. Also, MI is *not* SQL server 2014, it just has a weird version number. This of it as a separate version branch of SQL Server that has nothing to do with your on-prem SQL Server. 

    Test producing a BACKPAC and deploy on a 2019 instance (dev ed, for instance) using the most recent version of SSMS. Does that work, then take it from there.

    If that doesn't work, then you have a problem with the BACPAC. Probably a weird bit-pattern in your data, which throws off the BACKPAC installer/decoder. If this is the case, then you need to open a support case with MS and see if they can track down the bug.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Naomi N Tuesday, August 11, 2020 5:25 PM
    • Marked as answer by LFDS Wednesday, August 12, 2020 12:15 PM
    Tuesday, August 11, 2020 5:05 PM

All replies

  • You can use Azure Blob storage to exchange backups between SQL Azure and on-premise, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 25, 2020 9:14 AM
  • But how can I make a .bak backup of my azure database ? 

    I don't see it clearly. 

    Thursday, June 25, 2020 12:18 PM
  • A backup won't work, since Azure MI is a higher version then your self-installed version. (There is a smal window in time after the release of a version where this work, but pretty soon after release Azure climbs to a higher version. This is why it isn't supported to restore a backup from Azure MI to on-premise.) This is discussed here, among other places. Use BACPAC instread.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, June 25, 2020 2:00 PM
  • Do you think it's always the case even if the SQL Server on premise uses the latest CU? That would be inconvenient (and I agree, that at least within the same version of SQL Server the backups should be compatible).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 25, 2020 2:04 PM
  • AFAIK, this "problem" applies also if you CU your on-prem SQL Server. I agree, I wish there was some better story here. But it is what it is...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, June 25, 2020 3:03 PM
  • Hi LFDS,

    Also please refer to following posts on this topic:

    Restore from SQL managed instance

    Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 26, 2020 6:21 AM
  • I've tried what is said on that posts (using BACPAC) and it does not work. Error of compatibility. I have SSMS 2014. 

    After what I've read, there is no way to export the data of a database hosted in Azure MI ? 




    • Edited by LFDS Friday, June 26, 2020 7:26 AM
    Friday, June 26, 2020 7:00 AM
  • Hi LFDS,

    Using BACPAC is a solution.

    Try to use the latest version of SQL Server Management Studio (SSMS).

    Also please comment or vote the feedback on this topic at this link https://feedback.azure.com/forums/915676-sql-managed-instance/suggestions/35414512-make-backups-portable-to-on-premise-sql 

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 26, 2020 7:47 AM
  • You need a current version os SSMS. Install the latest and it should allow you to do "Export Data ties application", which created a bacpac. and this allow you to save it to a storage account.

    BACPAC *is* the supported way to get the data from MI to on-prem.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, June 26, 2020 8:42 AM
  • I made it but when I'm running the option "import data-tier application" it gives me an error:

    ", hexadecimal value 0xFFFE, is an invalid character, Line 60388, position 28, (System.Xml)

    Friday, June 26, 2020 9:20 AM
  • Hi LFDS,

    Is there any update on this case? Was your issue resolved?

    Please check:
    Export a Data-tier Application

    Import a BACPAC File to Create a New User Database

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 29, 2020 12:57 AM
  • Hello, 

    I still getting the error I mentioned before.

    I'm not able to import my bacpac file to SQL (I have installed SQL latest version). 

    Thursday, July 30, 2020 1:00 PM
  • Hi LFDS,

    Did you use the latest version of SQL Server Management Studio (SSMS) to export and import the bacpac file?

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 31, 2020 1:14 AM
  • Yes I think so.

    I downloaded it from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15


    • Edited by LFDS Friday, July 31, 2020 5:45 AM
    Friday, July 31, 2020 5:45 AM
  • Can you explain your current setup one more time, please?

    Post the select @@VERSION from the SQL Server you're trying to restore your DB on.

    Also, is it possible to try one more time to create a bacpac?

    Are you using anything which was not available in the SQL Server version you're trying to restore to?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 31, 2020 2:39 PM
  • Hello, 

    After too much tests I realized that the latest version of SQL Server Management Studio allow me to export the Azure database but not import it because my SQL managed instance is still 2014 version so it isn't compatible. 

    How complicated is it to get a database from azure? If I want to take that database to a local physical server with another instance of SQL Server, other than 2019 version, what do I have to do? I'm desperate.

    Tuesday, August 11, 2020 10:38 AM
  • One possibility to try in the meantime - install free SQL 2019 Developer's version and try to import bacpac there and then inspect locally what may be the problem with it and try to set the compatibility version to SQL 2014 and also make sure to not use any new SQL 2016-2019 features.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 11, 2020 1:35 PM
  • Hello, 

    After too much tests I realized that the latest version of SQL Server Management Studio allow me to export the Azure database but not import it because my SQL managed instance is still 2014 version so it isn't compatible. 

    How complicated is it to get a database from azure? If I want to take that database to a local physical server with another instance of SQL Server, other than 2019 version, what do I have to do? I'm desperate.

    BACPAC is the answer. Forget about backup and restore. Also, MI is *not* SQL server 2014, it just has a weird version number. This of it as a separate version branch of SQL Server that has nothing to do with your on-prem SQL Server. 

    Test producing a BACKPAC and deploy on a 2019 instance (dev ed, for instance) using the most recent version of SSMS. Does that work, then take it from there.

    If that doesn't work, then you have a problem with the BACPAC. Probably a weird bit-pattern in your data, which throws off the BACKPAC installer/decoder. If this is the case, then you need to open a support case with MS and see if they can track down the bug.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Naomi N Tuesday, August 11, 2020 5:25 PM
    • Marked as answer by LFDS Wednesday, August 12, 2020 12:15 PM
    Tuesday, August 11, 2020 5:05 PM
  • Thank you all for the quick answers, 

    I'll try what you propose ! 

    Wednesday, August 12, 2020 12:17 PM