Azure DevOps Server 2020本地实例退役前查询活动流水线及作者

前提说明 #

• 操作前务必备份数据库，避免误操作导致数据丢失
• 需具备数据库db_datareader角色权限
• 以下查询针对Azure DevOps Server 2020 Update 1.2的默认集合数据库Tfs_DefaultCollection

1. 活跃经典构建流水线查询 #

经典构建流水线存储于tbl_BuildDefinition表，关联项目与创建者信息：

SELECT
    p.ProjectName AS 项目名称,
    bd.Name AS 流水线名称,
    u.DisplayName AS 创建者,
    bd.LastUpdatedDate AS 最后更新时间,
    bd.IsDeleted AS 是否已删除
FROM
    tbl_BuildDefinition bd
JOIN
    tbl_Project p ON bd.ProjectId = p.ProjectId
JOIN
    tbl_Identity u ON bd.CreatedById = u.IdentityId
WHERE
    bd.IsDeleted = 0
    AND bd.DefinitionType = 0 -- 标记为经典构建类型
    AND EXISTS (
        SELECT 1 FROM tbl_Build b 
        WHERE b.DefinitionId = bd.DefinitionId 
        AND b.StartTime >= DATEADD(month, -3, GETDATE()) -- 近3个月有运行视为活跃，可调整时间范围
    )
ORDER BY
    p.ProjectName, bd.Name;

2. 活跃YAML构建流水线查询 #

YAML构建流水线同样在tbl_BuildDefinition表，但类型标记为2：

SELECT
    p.ProjectName AS 项目名称,
    bd.Name AS 流水线名称,
    u.DisplayName AS 创建者,
    bd.LastUpdatedDate AS 最后更新时间,
    bd.IsDeleted AS 是否已删除,
    bd.YamlFilePath AS YAML文件路径
FROM
    tbl_BuildDefinition bd
JOIN
    tbl_Project p ON bd.ProjectId = p.ProjectId
JOIN
    tbl_Identity u ON bd.CreatedById = u.IdentityId
WHERE
    bd.IsDeleted = 0
    AND bd.DefinitionType = 2 -- 标记为YAML构建类型
    AND EXISTS (
        SELECT 1 FROM tbl_Build b 
        WHERE b.DefinitionId = bd.DefinitionId 
        AND b.StartTime >= DATEADD(month, -3, GETDATE())
    )
ORDER BY
    p.ProjectName, bd.Name;

3. 活跃经典发布流水线查询 #

发布流水线存储于tbl_ReleaseDefinition表：

SELECT
    p.ProjectName AS 项目名称,
    rd.Name AS 发布流水线名称,
    u.DisplayName AS 创建者,
    rd.LastModifiedTime AS 最后更新时间,
    rd.IsDeleted AS 是否已删除
FROM
    tbl_ReleaseDefinition rd
JOIN
    tbl_Project p ON rd.ProjectId = p.ProjectId
JOIN
    tbl_Identity u ON rd.CreatedById = u.IdentityId
WHERE
    rd.IsDeleted = 0
    AND EXISTS (
        SELECT 1 FROM tbl_Release r 
        WHERE r.DefinitionId = rd.DefinitionId 
        AND r.CreationTime >= DATEADD(month, -3, GETDATE())
    )
ORDER BY
    p.ProjectName, rd.Name;

关键调整点 #

• 可修改DATEADD(month, -3, GETDATE())中的数值调整活跃流水线的时间范围（如改为-6表示近6个月）
• 若需过滤特定状态的流水线，可添加bd.Status（构建）或rd.Status（发布）字段的条件

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