I am trying to import the database into an Azure SQL Server. I have a database .bacpac
file which I stored in an Azure storage container.
The import process failed in a few minutes with the below error.
"statusMessage": "{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceDeploymentFailure\",\"message\":\"The resource operation completed with terminal provisioning state 'Failed'.\",\"details\":[{\"code\":\"ImportExportJobError\",\"message\":\"The ImportExport operation with Request Id '813153f0-7cb1-47a7-a45c-53e3209a31ab' failed due to 'Could not import package.\\nWarning SQL72012: The object [data_0] 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.\\nWarning SQL72012: The object [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.\\nError SQL72014: Framework Mi'.\"}]}}"
Could not import package.
Warning SQL72012: The object
[data_0]
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
[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: The sp_configure value
'contained database authentication'
must be set to 1 in order to alter a contained database. You may need to useRECONFIGURE
to set thevalue_in_use
.
I searched online for the solution and found that 'contained database authentication' should be 1 on SQL Server.
I run the below SQL query to check and found 'value' and 'value_in_use' column has value 0.
select *
from sys.configurations
where name = 'contained database authentication'
In one online post, I found a solution to set this setting.
EXEC sp_configure 'contained database authentication', 1
RECONFIGURE
but I get this error:
Statement 'CONFIG' is not supported in this version of SQL Server.
It is not supported by the Azure SQL Server instance.
Should I run the update command to set these column's values to 1?
update sys.configurations
set value = 1, value_in_use = 1
where name = 'contained database authentication'
I am not sure about the setting impact. Please suggest.
CodePudding user response:
I tried with the premium tier and it works. Able to import with a local machine with CMD and directly import from Azure portal.
Steps:
Download SqlPackage for Windows
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16
Extract the zip in your local machine.
Open CMD as admin rights in your local machine.
Select the SQL package directory.
cd C:\Users\User\Downloads\sqlpackage-win7-x64-en-16.0.6161.0
Run the below command to import the database.
sqlpackage.exe /a:import /tcs:"Data Source=abc.database.windows.net;Initial Catalog=clientdbname;User Id=admin;Password=abc@123" /sf:"C:\Users\User\Downloads\clientdb.bacpac" /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6