SQL Azure Export Data-Tier Application & import into local SQL server

1
votes

I have a SQL Azure database. I'm able to export the Database using Tasks > Export Data Tier Application. This is successful.

I then try to use Import Data Tier Application in my local SQL server and I get the following error:

Could not import package. Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2008. Warning SQL72012: The object [db_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. Warning SQL72012: The object [db_Log] 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 1 Incorrect syntax near 'CREDENTIAL'. Error SQL72045: Script execution error. The executed script: CREATE DATABASE SCOPED CREDENTIAL [databasenameAzureStorageCredential] WITH IDENTITY = N'SHARED ACCESS SIGNATURE';

I have SQL Server Management Studio 14.0.17289.0 and everything is up to date.

I have read different posts on Stack overflow and done some googling but unsure the best way to move forward. How can I solve this?

2
azure-sql-database

2 Answers

0
votes

It seems like there is a compatibility mode differences in your local SQL server DB and Azure SQL server DB. Check your compatibility level and if it is mismatched here is the resource to solve that. The error was because you use SSMS version 'X' to generate the bacpac against Azure SQL version 'Y'. Try to generate the same bacpac using SSMS version 'Y' and it works for me.

0
votes

Please download the latest version of SQL Server Management Studio from here to have the best user experience with Azure SQL Database. SSMS v14 is too old. The current version of SSMS is v17.9.

Remove (drop) the database scoped credential named "databasenameAzureStorageCredential" before exporting the database. The following query should give you a list of credentials created.

SELECT * FROM sys.database_scoped_credentials

In general, you need to remove references to external sources before exporting your database.