Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Could not import package. Warning SQL72012: The object exists in the target

Tags:

I exported my Azure database using Tasks > Export Data-tier Application in to a .bacpac file. Recently when I tried to import it into my local database server (Tasks > Import Data-tier Application), I encountered this error:

Could not import package. Warning SQL72012: The object [MyDatabase_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 [MyDatabase_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 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use. Error SQL72045: Script execution error.  The executed script: IF EXISTS (SELECT 1            FROM   [master].[dbo].[sysdatabases]            WHERE  [name] = N'$(DatabaseName)')     BEGIN         ALTER DATABASE [$(DatabaseName)]             SET CONTAINMENT = PARTIAL              WITH ROLLBACK IMMEDIATE;     END   Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed. Error SQL72045: Script execution error.  The executed script: IF EXISTS (SELECT 1            FROM   [master].[dbo].[sysdatabases]            WHERE  [name] = N'$(DatabaseName)')     BEGIN         ALTER DATABASE [$(DatabaseName)]             SET CONTAINMENT = PARTIAL              WITH ROLLBACK IMMEDIATE;     END   (Microsoft.SqlServer.Dac) 

I followed the advice on other posts and tried to run this on SQL Azure database:

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

However, it says

Could not find stored procedure 'sp_configure'. 

I understand the equivalent statement in Azure is: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-2017

What is the equivalent statement to "sp_configure 'contained database authentication', 1;"?

like image 405
Windhoek Avatar asked Aug 21 '19 16:08

Windhoek


People also ask

How do I import a Bacpac file to Azure database?

To import from a BACPAC file into a new single database using the Azure portal, open the appropriate server page and then, on the toolbar, select Import database. Select the storage account and the container for the BACPAC file and then select the BACPAC file from which to import.

What is BACPAC file?

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database, Azure SQL Managed Instance, or a SQL Server instance.


1 Answers

The solution is to execute this against the master database of your local/on-premise SQL Server:

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

Thank you to David Browne - Microsoft and Alberto Morillo for the quick solution.

like image 133
Windhoek Avatar answered Sep 26 '22 00:09

Windhoek