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).
To obtain the script for a specific deploy/publish action, either: