How to download a DB from Azure to local SQL server

LFDS 1

I want to download my DB in Azure to a local SQL server but I cant do it with the BACPAC option, is giving an error: "External references are not supported when creating a package from this platform".
Also, i think the bacpac option only export the tables, I also need to export the procedures, views, indexes...

4 answers

Anurag Sharma 17,576 Reputation points

2021-04-07T12:02:28.28+00:00
Hi @LFDS , welcome to Microsoft Q&A forum.

This error message comes because of schema verification. There are number of ways to work around it:

Remove the three part named objects to two. Example [database].[schema].[table] to [schema].[table]

Another way is to use SQLPackage.exe that does not do schema verification by default.

You can use VS and SSDT to extract the .dacpac file which can be imported through SSMS in local database.

Please refer to below article for more reference:

How to extract MSSQLServer database as .dacpac without VerifyExtraction?

Please let me know if this helps or else we can discuss further.

----------

If answer help, please mark it as 'Accept Answer'
Please sign in to rate this answer.
LFDS 1 Reputation point

2021-04-12T10:17:52.193+00:00

Hello,

Where do I remove the three part named objects as you mentioned?
SQLPackage and extrat to .dacpac file is not working either...
Sign in to comment
AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

2021-04-08T09:33:15.75+00:00

Hi LFDS-4457,

In addition, please refer to this article which might help.

i think the bacpac option only export the tables, I also need to export the procedures, views, indexes...

You can use the Generate and Publish Scripts Wizard in the SSMS to create scripts for the tables, procedures, views and indexes. Please refer to Generate and Publish Scripts Wizard which might help.

Best Regards,
Amelia
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
LFDS 1 Reputation point

2021-04-14T09:58:53.917+00:00

I tried all things mentioned before but it's still giving me errors...
Please sign in to rate this answer.
Anurag Sharma 17,576 Reputation points

2021-04-14T10:02:56.643+00:00

Hi @LFDS , are you still getting the same error message?

"External references are not supported when creating a package from this platform".

LFDS 1 Reputation point

2021-04-15T05:56:06.157+00:00

Hello, when i try to deploy de dacpac file it's giving this error:

Could not deploy package.
Warning SQL0: A project which specifies SQL Server 2019 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with SQL Server 2014.
Error SQL72014: .Net SqlClient Data Provider: Msg 534, Level 15, State 72, Line 2 Error en 'FILEGROUP ... CONTAINS MEMORY_OPTIMIZED_DATA' porque no se admite en la edición de esta instancia de SQL Server 'PC\SQLEXPRESS'. Vea los Libros en pantalla para obtener más detalles sobre la compatibilidad de características en diferentes ediciones de SQL Server.
Error SQL72045: Script execution error. The executed script:
ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [XTP] CONTAINS MEMORY_OPTIMIZED_DATA;

Anurag Sharma 17,576 Reputation points

2021-04-15T06:38:09.38+00:00

Hi @LFDS , thanks for replying back.

From the error message it looks like memory optimized data is not supported with this version of SQL Server. I came across similar query where it is mentioned to upgrade it to other version of SQL Server.

FILEGROUP ... CONTAINS MEMORY_OPTIMIZED_DATA' failed because it is not supported

As you are working with on-premise SQL server, below is the link from where you can download and install it.

download a free specialised edition

Please let me know if this works with you.

LFDS 1 Reputation point

2021-04-15T07:44:32.077+00:00

Thank you for all your attention.
I've installed this but it's still giving the same error.

I generated the .dacpac file from Azure with sqlpackage and then restored it in localhost with SSMS 2019, latest version.

Anurag Sharma 17,576 Reputation points

2021-04-15T08:51:06.247+00:00

Can you please try below things once:

1). Unpack the .dacpac file as mentioned in the thread:

How to unpack a DACPAC?

2). Remove the ALTER DATABASE statement which is giving error.
3). Import from .sql file directly.

LFDS 1 Reputation point

2021-04-15T10:54:28.533+00:00

I executed the sql directly and now it has created my db but without data...
What I need is all the data of the existing DB i'm trying to migrate.

Anurag Sharma 17,576 Reputation points

2021-04-15T12:39:51.637+00:00

It looks like while creating dacpac file through sqlpackage.exe, just schema was included.

We can extract the data script through the SSMS as mentioned in below screenshot:

This is like a wizard, please change the highlighted option to Data Only. Then we can save the file as .sql file and run it through SSMS

Please let me know if this works out.

LFDS 1 Reputation point

2021-04-16T06:33:07.567+00:00

This is the first thing i tried to do because this is how i was migrating DBs untill now but it's giving an error..

Anurag Sharma 17,576 Reputation points

2021-04-16T12:29:30.777+00:00

Thanks for replying back. The table which is giving error in above screenshot, is this external data source? Can you please also post the error message to understand it more?

LFDS 1 Reputation point

2021-04-19T12:10:42.007+00:00

No, all the tables are internal data, in the local DB.
And about the error message, there's no error, only the "click on help for know solutions".

LFDS 1 Reputation point

2021-04-29T05:20:23.24+00:00

I was testing this and i think this generation doesn't work cause of all the space the data occupies.
I tried with a small DB and it worked, I tried with only schema and it worked.
It only show this error when the DB is bigger and when i'm generating scrips with the "schema and data"option...
I'm kinda desperate because we need to extract this DB from the instance.

Navtej Singh Saini 4,221 Reputation points Microsoft Employee

2021-04-29T13:40:06.197+00:00

@LFDS - As this is very specific issue and needs the details of your workload, At this time we would suggest that you contact support regarding this.

Once you do that, Let us know the ticket num and we can try to get more traction for the same.

Regards
Navtej S

LFDS 1 Reputation point

2021-05-04T05:51:30.267+00:00

Where can I contact support? I tried via Azure but I don't find a good way..
Sign in to comment
Alberto Morillo 32,896 Reputation points MVP

2021-04-29T15:19:54.883+00:00

Instead of using SQL Server Express please install SQL Server Developer Edition, which is free, and supports memory optimized tables.

Please install SQL Server 2019 and select "Developer Edition" on the Product Key page of SQL Server setup. Please see this article about how to install Developer Edition. You can also perform an edition upgrade from SQL Server Express to Developer Edition read here for more information.

Please apply the latest cumulative update for SQL 2019 before trying to import the bacpac.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment