Import Data-tier Application from Azure to local Sql Server 2014 fails

1
votes

Exporting an azure sql database and importing to a local instance of SQL Server 2014 fails using the process below. How can I do this successfully?

  1. Export Sql database from within Azure Portal to blob storage account as bacpac file.
  2. Go into SSMS v17.6 > Right/click Databases > Select 'Import Data-tier Application'
  3. Either import bacpack file from azure blob storage or local disk

enter image description here

enter image description here

enter image description here

2
azure-sql-databasessmssql-server-2014

2 Answers

1
votes

Please consider dropping the database scoped credential before exporting as bacpac as shown below:

DROP DATABASE SCOPED CREDENTIAL CredentialName

If that scoped credential was created to create external data sources, then you will have to drop them first.

DROP EXTERNAL DATA SOURCE SourceName
0
votes

Importing to your local 2014 instance. Importer doesn't like the DATA SOURCE or the SCOPED CREDENTIAL in your db, particularly the SCOPED CREDENTIAL which is giving you the error. You can avoid this error by removing the SCOPED CREDENTIAL, and the datasource from your database in AZURE

View external datasources on azure db -> SELECT * FROM sys.external_data_sources;

Delete external datasources on azure db -> DROP EXTERNAL DATA SOURCE ds_name

View scoped credentials on azure db -> select * from sys.database_scoped_credentials

Delete scoped credentials on azure db -> DROP DATABASE SCOPED CREDENTIAL cred_name

Then export data-tier application and import locally.