locked
Import BACPAC - Microsoft SQL Server Management Studio RRS feed

  • Question

  • We are trying to import a bacpac file to a local database – exporting the bacpac from the Azure sql database works fine, but when we try to import it to a local database, we get the error bellow:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Could not import package.

    Warning SQL72012: The object [DataBaseName_prod_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

    Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'RDBMS'.

    Error SQL72045: Script execution error.  The executed script:

    CREATE EXTERNAL DATA SOURCE [NT4DbSrc]

        WITH (

        TYPE = RDBMS,

        LOCATION = N'h4al4cx7yj.database.windows.net',

        DATABASE_NAME = N'databaseName-Prd01',

        CREDENTIAL = [NT4DWHElasticCredential]

        );

     (Microsoft.SqlServer.Dac)

    ------------------------------ BUTTONS: OK ------------------------------

    This problem only happens with DataBaseName-PRD database – the same process works fine with DatabaseName-DEV database, which is in a different subscription and has different Resource Configuration and Pricing configurations, maybe that has something to do with the problem.

    The version of the local database is this : Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 16299






    Tuesday, May 8, 2018 11:35 AM

Answers

  • Hello,

    It seems you have created external data sources on your Azure SQL Database. You need to remove them (NT4DbSrc), export the database as bacpac then try to import the database again. Elastic queries are not supported on SQL Server on-premise.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com







    Tuesday, May 8, 2018 11:53 AM

All replies

  • Hello,

    It seems you have created external data sources on your Azure SQL Database. You need to remove them (NT4DbSrc), export the database as bacpac then try to import the database again. Elastic queries are not supported on SQL Server on-premise.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com







    Tuesday, May 8, 2018 11:53 AM
  • That solved the problem, thank you!
    If anyone else needs, to delete the external datasource you have to run

    DROP EXTERNAL DATA SOURCE data_source_name

    Tuesday, May 8, 2018 4:55 PM
  • So you are saying that anytime I need to make a backup of a production system to bring down to a local environment I am to drop the external sources, backup the database to a bacpac file, and then restore the external data sources?  What do you do in the production environment during this time for queries that depend on those links?  I have read posts about editing the bacpac file locally, but there has to a better solution.
    Monday, October 15, 2018 6:45 PM