Good day all,
To import a BACPAC file exported from Azure for example follow the following steps:
To import a BACPAC file exported from Azure for example follow the following steps:
- Make sure you have a compatible version of SQL database installed, in this example, the BACPAC file has been imported from Azure, and I have MS SQL Server 2017 installed, so they are compatible.
- Open Microsoft SQL Server Management Studio, and login.
- Righ click on "Databases" and select "Import Data-tier Application"
- Click Next on "Introduction" screen.
- Specify the BACPAC to import and click next.
- Specify the settings for the new database information and click Next
- Review the information and click Finish.
Errors that may occur while importing:
- 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 .
That means the SQL Instance level, you have contained databases disabled (ie contained database authentication is not set to 1) and then the script is trying to set containment to partial.
You will need to set Contained database authentication to 1 by executing this statement on master database:EXEC sp_configure 'contained database authentication', 1 RECONFIGURE
More details are in the this blog
- 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)
Issue should be solved similar to the previous one, or by solving the previous one and try to import the BACPAC file againEXEC sp_configure 'contained database authentication', 1 RECONFIGURE
- Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service. (Microsoft.Data.Tools.Schema.Sql)
That means your version of SQL Management Studio is not compatible, try updating it.
- 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 2012.Warning SQL72012: The object [database] 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 ut 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: .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
That means your version of database is not compatible, ex: you are trying to import a BACPAC created on MS SQL Server 2017 into MS SQL Server 2012, one solution is to install MS SQL Server 2017 and do the import.
Hope that helps
Informative blog. Thank you for sharing with us..
ReplyDeleteAmazon Web Services Online Training
It was so nice article. I was really satisfied by seeing this article. SQL server dba Online Training Bangalore
ReplyDeleteNice blog
ReplyDeleteMicrosoft Windows Azure Training | Online Course | Certification in chennai | Microsoft Windows Azure Training | Online Course | Certification in bangalore | Microsoft Windows Azure Training | Online Course | Certification in hyderabad | Microsoft Windows Azure Training | Online Course | Certification in pune
This is good information and really helpful for the people who need information about this.Nice article I was impressed by seeing this blog, it was very interesting and it is s for sharing all the information with us all.very useful for me.
ReplyDeleteoracle training in chennai
oracle training institute in chennai
oracle training in bangalore
oracle training in hyderabad
oracle training
oracle online training
hadoop training in chennai
hadoop training in bangalore
Very Nice Blog…Thanks for sharing this information with us. Here am sharing some information about training institute.
ReplyDeletetableau training in hyderabad