DacDeploy ScriptDatabaseOptions部署失效，如何忽略SQL恢复模式？

问题描述 #

我在DevOps CI/CD流水线中开发了一款多数据库更新工具，运行正常。当前正在迁移SaaS产品的宿主平台，需要将新平台上MS SQL数据库的恢复模式从DACPAC文件中的SIMPLE改为FULL，同时不改动旧平台的恢复模式。

我尝试设置DacDeployOptions.ScriptDatabaseOptions = false，但该配置在部署时被忽略：通过SSMS将目标库设置为FULL恢复模式后，部署DACPAC会将其还原为SIMPLE。我知道可以在流水线中修改DACPAC的RecoveryMode属性，但涉及大量流水线与阶段，操作繁琐。请问如何让部署正确忽略恢复模式设置？

附现有代码：

using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Dac;

public void ProcessDacpac(string dacpacFile)
{
    foreach (var sqlConnectionString in sqlConnectionStrings)
    {
        Logger.LogToFile($"Start DacPackage Deploy for: {sqlConnectionString.Key}",Logger.LogCondition.Verbose);

        //check if server and database is online
        Logger.LogToFile($"Testing Connection For: {sqlConnectionString.Key}",Logger.LogCondition.Verbose);

        if (!IsServerAndDatabaseOnline(sqlConnectionString.Value))
        {
            Logger.LogToFile($"Connection Failed: {sqlConnectionString.Key}", Logger.LogCondition.Critical);
            continue;
        }
        else
        {
            Logger.LogToFile($"Testing Connection For: {sqlConnectionString.Key} Success", Logger.LogCondition.Verbose);
        }

        var services = new Microsoft.SqlServer.Dac.DacServices(sqlConnectionString.Value);
        services.Message += (object sender, Microsoft.SqlServer.Dac.DacMessageEventArgs eventArgs) => Logger.LogToFile(eventArgs.Message.Message, Logger.LogCondition.Verbose);

        var dacpac = DacPackage.Load(dacpacFile);
        
        //publish options
        var dacDeployOptions = new DacDeployOptions()
        {
            ScriptDatabaseOptions = false, // <------- this is ignored!
            BlockOnPossibleDataLoss = false, //All these other options are respected. 
            IgnoreColumnOrder = true,
            AllowIncompatiblePlatform = true,
            ExcludeObjectTypes = new ObjectType[] { ObjectType.Users, ObjectType.RoleMembership, ObjectType.Permissions, ObjectType.Logins },
            TreatVerificationErrorsAsWarnings = true
        };

        try
        {
            services.Deploy(dacpac, sqlConnectionString.Key, true, dacDeployOptions);
        }
        catch (Exception ex)
        {
            Logger.LogToFile(ex.Message, ex, Logger.LogCondition.Critical);
            throw;
        }

        Logger.LogToFile($"END DacPackage Deploy for: {sqlConnectionString.Key}", Logger.LogCondition.Verbose);
    }
}

解决方案 #

1. 使用精准忽略配置：IgnoreRecoveryMode #

ScriptDatabaseOptions = false的作用是不生成修改数据库选项的脚本，但DACPAC部署引擎仍会对比源（DACPAC）和目标（数据库）的选项差异并强制对齐。正确的做法是直接指定忽略恢复模式的差异：

修改你的DacDeployOptions配置：

var dacDeployOptions = new DacDeployOptions()
{
    // 替换无效的ScriptDatabaseOptions，直接忽略恢复模式差异
    IgnoreRecoveryMode = true,
    BlockOnPossibleDataLoss = false,
    IgnoreColumnOrder = true,
    AllowIncompatiblePlatform = true,
    ExcludeObjectTypes = new ObjectType[] { ObjectType.Users, ObjectType.RoleMembership, ObjectType.Permissions, ObjectType.Logins },
    TreatVerificationErrorsAsWarnings = true
};

如果需要忽略所有数据库选项（不推荐，可能影响其他必要设置），也可以用：

IgnoreDatabaseOptions = true

2. 针对新平台做条件化配置 #

如果需要仅在新平台应用忽略规则、旧平台保持原有逻辑，可以通过连接字符串或环境变量判断目标环境，动态设置选项：

// 示例：通过连接字符串中的服务器标识判断是否为新平台
bool isNewPlatform = sqlConnectionString.Value.Contains("new-platform-sql");

var dacDeployOptions = new DacDeployOptions()
{
    // 仅新平台忽略恢复模式
    IgnoreRecoveryMode = isNewPlatform,
    BlockOnPossibleDataLoss = false,
    IgnoreColumnOrder = true,
    AllowIncompatiblePlatform = true,
    ExcludeObjectTypes = new ObjectType[] { ObjectType.Users, ObjectType.RoleMembership, ObjectType.Permissions, ObjectType.Logins },
    TreatVerificationErrorsAsWarnings = true
};

为什么ScriptDatabaseOptions无效？ #

ScriptDatabaseOptions控制的是是否生成修改数据库选项的脚本段，但DACPAC部署逻辑中，数据库选项（包括恢复模式）属于核心对比维度。即使不生成脚本，引擎仍会在预检查阶段检测到源与目标的差异，并强制对齐到DACPAC中的设置。而IgnoreRecoveryMode是直接告诉部署引擎跳过这类差异的对比与对齐操作。

内容的提问来源于stack exchange，提问作者Damo