部署.dacpac至SQL Server 2017 Docker容器时文件组路径错误

我使用Microsoft的DacFx NuGet包从本地SQL Server生成.dacpac，计划部署到SQL Server 2017 Ubuntu Docker容器中做集成测试。目标是提取不含FILEGROUP和依赖.mdf文件的架构，不需要挂载Docker卷（数据存于内存，容器停止即销毁）。此前本地数据库无FILEGROUP时部署成功，但现在本地库包含FILEGROUP，即便设置了IgnoreFilegroupPlacement等选项，部署仍报文件组路径无效的错误。

提取代码 #

DacServices dacServices = new(targetDacpacDbExtract.ConnectionString);

DacExtractOptions extractOptions = new()
{
    ExtractTarget = DacExtractTarget.DacPac,
    IgnorePermissions = true,
    IgnoreUserLoginMappings = true,
    ExtractAllTableData = false,
    Storage = DacSchemaModelStorageType.Memory
};

using MemoryStream stream = new();

dacServices.Extract(stream,
            targetDacpacDbExtract.DbName,
            "MyDatabase",
            new Version(1, 0, 0),
            extractOptions: extractOptions);

stream.Seek(0, SeekOrigin.Begin);

byte[] dacpacStream = stream.ToArray();

this.logger.LogInformation("Finished extracting schema.");

本地提取连接字符串 #

Server=localhost;Database=MyDatabase;Integrated Security=true

Docker连接字符串 #

SQLConnectionStringDocker:Server=127.0.0.1, 47782;Integrated Security=false;User ID=sa;Password=$Trong12!;

部署代码 #

this.logger.LogInformation("Starting Deploy extracted dacpac to Docker SQL container.");

DacDeployOptions options = new()
{
    AllowIncompatiblePlatform = true,
    CreateNewDatabase = false,
    ExcludeObjectTypes = new ObjectType[]
    {
        ObjectType.Permissions,
        ObjectType.RoleMembership,
        ObjectType.Logins,
    },
    IgnorePermissions = true,
    DropObjectsNotInSource = false,
    IgnoreUserSettingsObjects = true,
    IgnoreLoginSids = true,
    IgnoreRoleMembership = true,
    PopulateFilesOnFileGroups = false,
    IgnoreFilegroupPlacement = true
};

DacServices dacService = new(targetDeployDb.ConnectionStringDocker);

using Stream dacpacStream = new MemoryStream(dacBuffer);
using DacPackage dacPackage = DacPackage.Load(dacpacStream);

var deployScript = dacService.GenerateDeployScript(dacPackage, "Kf", options);

dacService.Deploy(
    dacPackage,
    targetDeployDb.DbName,
    upgradeExisting: true,
    options);

this.logger.LogInformation("Finished deploying dacpac.");

return Task.CompletedTask;

错误信息 #

Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5121, Level 16, State 2, Line 1 The path specified by "MyDatabase.CacheItem_FG_195A905.mdf" is not in a valid directory.
Error SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [CacheItem_FG_195A905], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_CacheItem_FG_195A905.mdf') TO FILEGROUP [CacheItem_FG];


Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5009, Level 16, State 14, Line 1 One or more files listed in the statement could not be found or could not be initialized.
Error SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [CacheItem_FG_195A905], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_CacheItem_FG_195A905.mdf') TO FILEGROUP [CacheItem_FG];

1. 提取阶段直接排除文件组相关对象 #

在生成.dacpac时，明确排除文件组和数据库文件对象，避免这些内容被打包：
修改提取代码的DacExtractOptions：

DacExtractOptions extractOptions = new()
{
    ExtractTarget = DacExtractTarget.DacPac,
    IgnorePermissions = true,
    IgnoreUserLoginMappings = true,
    ExtractAllTableData = false,
    Storage = DacSchemaModelStorageType.Memory,
    // 新增：排除文件组和数据库文件
    ExcludeObjectTypes = new ObjectType[]
    {
        ObjectType.Filegroups,
        ObjectType.DatabaseFiles
    }
};

2. 部署阶段强化忽略规则 #

如果提取阶段仍有残留，在部署选项中补充排除规则，并启用数据库文件路径忽略：
修改部署代码的DacDeployOptions：

DacDeployOptions options = new()
{
    AllowIncompatiblePlatform = true,
    CreateNewDatabase = false,
    ExcludeObjectTypes = new ObjectType[]
    {
        ObjectType.Permissions,
        ObjectType.RoleMembership,
        ObjectType.Logins,
        // 新增：排除文件组和数据库文件
        ObjectType.Filegroups,
        ObjectType.DatabaseFiles
    },
    IgnorePermissions = true,
    DropObjectsNotInSource = false,
    IgnoreUserSettingsObjects = true,
    IgnoreLoginSids = true,
    IgnoreRoleMembership = true,
    PopulateFilesOnFileGroups = false,
    IgnoreFilegroupPlacement = true,
    // 新增：忽略数据库文件路径配置
    IgnoreDatabaseFilePlacement = true
};

3. 验证部署脚本内容 #

在调用Deploy前，检查deployScript的内容，确认是否还包含ADD FILE或ALTER FILEGROUP相关语句。如果仍存在，说明排除规则未覆盖全，需补充对应对象类型到排除列表。

4. （可选）确认Docker容器默认路径有效性 #

若必须保留文件组，需确保容器内SQL Server的默认数据路径存在。可通过以下SQL查询容器内路径：

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath;

若路径不存在，手动创建或修改部署脚本中的路径为容器内有效路径（如/var/opt/mssql/data/），但此方案不符合“内存存储、容器销毁即删数据”的目标，更推荐前两种排除文件组的方案。

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