Error when importing snapshot to SQL Server 2019

Question

There is an Error when I want to import data from a snapshot(bacpac) file into SQL Server 2019.

It has occur twice on my two different PC.

Please help me if there is any solution about that.

Thank you very much!

Error :

Error Details :

Here are the error details:

===================================

Object reference not set to an instance of an object. (Microsoft.Data.Tools.Schema.Sql)

Program Location:

at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateFragment(Int32 operation, IModelElement element) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateSteps(Int32 operation, IModelElement element) at Microsoft.Data.Tools.Schema.Sql.Deployment.OrderedStepGenerator.Add(DeploymentScriptDomGenerator scriptDom, IModelElement element, Int32 operation) at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildDependencyOrderedSteps(Int32 operation, List1 classOrder, List1 operationOrder, Dictionary2 changes, Boolean preserveGraphs, Dictionary2& relating, Dictionary2& related) at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildOperations() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnGeneratePlan() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.GeneratePlan(List1 drops) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.CreatePlan() at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.b__1() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.b__0(Object operation, CancellationToken token) at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action3 reportPlanOperation, Boolean executePlan) at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable1 cancellationToken) at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacAzureDatabaseSpecification creationDefaults, Nullable`1 cancellationToken) at Microsoft.SqlServer.Management.Dac.DacWizard.ImportDatabase.DoWork() at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

Answer 1

I had the same issue and was able to resolve this with previous version of SSMS (SQL Server Management Studio) - not the SQL Server version. Previous SSMS versions can be downloaded here:

https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791

ATTOW, the current version is 18.5. It works for me at version 17.9.1 and 18.4.

This works for me on both SQL Server 2017 and 2019, it was the issue with SSMS, not the SQL Server version.

I also tried Azure Data Studio with SQL Dacpac extension (https://github.com/microsoft/azuredatastudio/tree/master/extensions/dacpac) and it failed with same error as latest SSMS version. There's an issue open for this: https://github.com/microsoft/azuredatastudio/issues/9522

Alternatively, instead of installing SSMS, install sqlpackage from https://learn.microsoft.com/en-us/sql/tools/release-notes-sqlpackage?view=sql-server-ver15#184-sqlpackage (version 18.4) and run the command manually. Something like:

sqlpackage.exe /a:Import /df:"import.log" /sf:"mybacpac-2020-04-26.bacpac" /tcs:"Server=.;Database=mybacpac;Integrated Security=true"

Answer 2

I ran into the exact same error message while attempting to import a BACPAC to my local SQL Server 2019. Exact same error on SQL Server 2017 through my testing. The import failed with the latest versions of SSMS and Azure Data Studio.

In my case the error was thrown due to the existence of an external contained user account in the database, principle type E. Type E is External user from Azure Active Directory.

You can check here for reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql?view=sql-server-ver15

After removing the user account from the database, I was able to import the database successfully.