Import .bacpac to local SQL Server failed with SQL72014 & SQL72045

Question

I'm trying to create a copy from Azure SQL on my local server.

I created a .bacpac file on my pc with SSMS, following the site such as [https://medium.com/awesome-azure/copy-azure-sql-database-to-local-server-bff89fd06992

Then I tried to import the .bacpac file I created, after connecting the local server.

But every time I did, I got the same error as follows.

Could any person help me with this?

mizota

PS: I'm using SQL Server 2022 Express and the latest SSMS on my PS.

TITLEMicrosoft SQL Server Management Studio

Failed to import package.
Warning SQL72012: The object [ExacTest_Data] exists in the target, but it will not be dropped even if you select the Generate DROP statements for objects in target database but not in source check box.
Warning SQL72012: The object [ExacTest_Log] exists in the target, but it will not be dropped even though the Generate DROP statements for objects in the target database but not in the source check box is selected.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure CopyToTestDb, Line 12 Incorrect syntax near 'copy'.
Error SQL72045: Script execution error. Script executed:
CREATE PROCEDURE CopyToTestDb
AS
BEGIN
     SET NOCOUNT ON;
     DROP DATABASE IF EXISTS ExacTest;
     CREATE DATABASE ExacTest
         AS COPY OF ExacDb;
END
  (Microsoft.SqlServer.Dac)

Answer 1

Hi [@Kazuaki Mizota ] ,

Welcome to Microsoft Q&A platform and thanks for using Azure services.

As I understand from the error, you are facing Error SQL72014 and Error SQL72045 when trying to import .bacpac to Local server.

Error SQL72014 which is requesting to enable contained database authentication on the SQL Server instance.

In order to restore a contained database coming from Azure SQL Database into an on-premise SQL Server instance the "contained database authentication" property must be enabled.

Could you please to execute below command and see if this helps in your case:

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

Hope this helps. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 2

Getting SQL72014 & SQL72045 while trying to import a SQL Azure .bacpac to local SQL Server. Note: blob auditing is enabled on the SQL Azure database so master key exists.

The local database already had a master key (from a prior .bacpac) and the import was trying to recreate a master key. The old master key could not be decrypted. Either of these should help resolve, but be aware of what is using the master key before dropping.

DROP MASTER KEY

ALTER SERVICE MASTER KEY FORCE REGENERATE

Answer 3

Error SQL72014 can also be generated when the database contains classifications or masked columns. You can check this by running the following commands:

SELECT *

FROM sys.sensitivity_classifications

SELECT *

FROM sys.masked_columns

If you find rows in these tables, you need to remove the data before you can Export your data application.