DacFx regression error v14 vs v15. Version 15 is issuing "ALTER DATABASE" statements at DacServices.Deploy() time, but it should not

Question

I'm using the DACFx DLLs in a web project that can generate a dacpac and restore it somewhere else (new empty database in another server and perhaps a different SQL Server version).

A backup created via DacServices.Extract() with version 14 (e.g. 14.0.3917.1) or version 15 (e.g. 15.0.5084.2) can be restored via DacServices.Deploy() without any issues.

The exact same code (c# .csproj) referencing 15.0.5084.2 instead of 14.0.3917.1 at restore time fail with errors similar to this:

Error SQL72014: .Net SqlClient Data Provider: Msg 5170, Level 16, State 1, Line 1 Cannot create file 'X:\SQLServer\DATA\olddb.ldf' because it already exists. Change the file path or the file name, and retry the operation.
Error SQL72045: Script execution error. The executed script: ALTER DATABASE [$(DatabaseName)] ADD LOG FILE (NAME = [dnn9me_log], FILENAME = 'X:\SQLServer\DATA\olddb.ldf', SIZE = 2880 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %);

The results are the same running Extract() and Deploy() on a SQL Server 2014 or later database (e.g. 2019). I mean, a standard simple database NOT on Azure.

I'm using the following options to perform the Extract():

DacExtractOptions = 
{
ExtractAllTableData = true, 
IgnorePermissions = true, 
IgnoreUserLoginMappings = true, 
ExtractUsageProperties = false,
IgnoreExtendedProperties = false,
ExtractReferencedServerScopedElements = false, 
ExtractApplicationScopedObjectsOnly = false,
}

And I'm using the following options to perform the Deploy():

excludeObjectTypes = ServerAuditSpecifications, ServerRoleMembership, ServerRoles, 
ServerTriggers, LinkedServers, Filegroups,Logins, Users, Assemblies, AssemblyFiles, 
ApplicationRoles, Permissions, RoleMembership, DatabaseOptions, DatabaseRoles, 
DatabaseTriggers, PartitionSchemes, ExtendedProperties, Tables, Views, UserDefinedDataTypes, 
UserDefinedTableTypes, ScalarValuedFunctions, TableValuedFunctions, PartitionFunctions,
StoredProcedures, XmlSchemaCollections

doNotDropObjectTypes =  ServerAuditSpecifications, ServerRoleMembership, ServerRoles, 
ServerTriggers, LinkedServerLogins, LinkedServers, Filegroups, Logins, Users, Assemblies, 
AssemblyFiles, ApplicationRoles, Permissions, RoleMembership, DatabaseOptions, DatabaseRoles, 
DatabaseTriggers, PartitionSchemes, ExtendedProperties, Tables, Views, UserDefinedDataTypes, 
UserDefinedTableTypes, ScalarValuedFunctions, TableValuedFunctions, PartitionFunctions, 
StoredProcedures, XmlSchemaCollections


DacDeployOptions = 
{
IgnorePermissions = true,
IgnoreUserSettingsObjects =  true,
IgnoreLoginSids = true, 
IgnoreRoleMembership =  false,
DoNotDropObjectTypes = doNotDropObjectTypes.ToArray(),
ExcludeObjectTypes = excludeObjectTypes.ToArray(),
AllowDropBlockingAssemblies = false,
AllowIncompatiblePlatform = true, 
AllowUnsafeRowLevelSecurityDataMovement = false, 
BackupDatabaseBeforeChanges = false, 
BlockOnPossibleDataLoss = true, 
BlockWhenDriftDetected = true, 
CommentOutSetVarDeclarations = false, 
CompareUsingTargetCollation = false, 
CreateNewDatabase = false, 
DeployDatabaseInSingleUserMode = false, 
DisableAndReenableDdlTriggers = true, 
DoNotAlterChangeDataCaptureObjects = true, 
DoNotAlterReplicatedObjects = true, 
RegisterDataTierApplication = false, 
DropConstraintsNotInSource = true, 
DropDmlTriggersNotInSource = true, 
DropExtendedPropertiesNotInSource = true, 
DropIndexesNotInSource = true, 
DropObjectsNotInSource = false, 
DropPermissionsNotInSource = false, 
DropRoleMembersNotInSource = false, 
DropStatisticsNotInSource = false, 
GenerateSmartDefaults = false, 
IgnoreAnsiNulls = false, 
IgnoreAuthorizer = false,
IgnoreColumnCollation = false, 
IgnoreComments = false, 
IgnoreCryptographicProviderFilePath = true, 
IgnoreDdlTriggerOrder = false, 
IgnoreDdlTriggerState = false, 
IgnoreDefaultSchema = false, 
IgnoreFileAndLogFilePath = true, 
IgnoreDmlTriggerOrder = false, 
IgnoreDmlTriggerState = false, 
IgnoreExtendedProperties = false, 
IgnoreFileSize = true, 
IgnoreFilegroupPlacement = true, 
IgnoreFillFactor = true, 
IgnoreFullTextCatalogFilePath = true, 
IgnoreIdentitySeed = false, 
IgnoreIncrement = false, 
IgnoreIndexOptions = false, 
IgnoreIndexPadding = true, 
IgnoreKeywordCasing = true, 
IgnoreLockHintsOnIndexes = false, 
IgnoreNotForReplication = false, 
IgnoreObjectPlacementOnPartitionScheme = true,
IgnorePartitionSchemes = false, 
IgnoreQuotedIdentifiers = false, 
IgnoreRouteLifetime = true, 
IgnoreSemicolonBetweenStatements = true, 
IgnoreTableOptions = false, 
IgnoreWhitespace = true, 
IgnoreWithNocheckOnCheckConstraints = false, 
IgnoreWithNocheckOnForeignKeys = false, 
IncludeCompositeObjects = false, 
IncludeTransactionalScripts = false, 
NoAlterStatementsToChangeClrTypes = false, 
PopulateFilesOnFileGroups = false, 
RunDeploymentPlanExecutors = false, 
ScriptDatabaseCollation = false, 
ScriptDatabaseCompatibility = false, 
ScriptDatabaseOptions = true, 
ScriptDeployStateChecks = true, 
ScriptFileSize = false, 
ScriptNewConstraintValidation = true, 
ScriptRefreshModule = true, 
TreatVerificationErrorsAsWarnings = true, 
UnmodifiableObjectWarnings = false, 
VerifyCollationCompatibility = true, 
IgnoreColumnOrder = true,
}

How can I prevent these ALTER DATABASE statements from being created when issuing the Extract() and/or how can I prevent them from being executed when issuing the Deploy()?

As a workaround, I thought about using a Contributor (example contributors) to identify any ALTER DATABASE and prevent it from running.

The problem is, I couldn't make the application to see the contributor, which is a class in the same csproj.

Perhaps contributors only work when using SqlPackage.exe and the DLL where the contributor is defined is placed in a specific location outside the application's \bin folder. Is this true? Or there is a way to make a contributor work in my case (web app which can only see DLLs in its \bin folder).

Answer 1

To obtain the script for a specific deploy/publish action, either:

• take the shortcut with SqlPackage /a:Script
• use DacServices.GenerateCreateScript