Getting the Full Path of a CMS folder from MSDB

Nice, even though I posted this at 12:25am CST on 8/1, blogger still gave me credit for a July post. It has been a busy month as we prepare for a DR exercise next month but I wanted to share this. We build CMS groups for different groups of servers that we need to run scripts against for the DR exercise: Availability Group servers on the production side who are primary, mirrored servers on the DR side, non-participating AGs, etc.

I was writing code to populate all these groups and found that when you query msdb to find the group name it only shows the name of that level and not the full path. I had to write a recursive CTE to get all the paths. This is the query I wrote:

USE msdb GO -- SELECT * FROM dbo.sysmanagement_shared_server_groups_internal AS sssgi; -- SELECT * FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]; WITH cte_groups (groupid, name, parentid, parentname, fullname) AS ( SELECT g1.server_group_id,     g1.name,     g1.parent_id,     CAST('' AS VARCHAR(100)) AS parentname,     CAST(g1.name AS VARCHAR(200)) AS fullname FROM dbo.sysmanagement_shared_server_groups_internal AS g1 WHERE g1.parent_id IS null UNION ALL SELECT g2.server_group_id,     g2.name,     g2.parent_id,     (gr.name AS VARCHAR(100)) AS parentname,     CAST( gr.fullname + '\' + g2.name AS VARCHAR(200)) AS fullname FROM dbo.sysmanagement_shared_server_groups_internal AS g2 INNER JOIN cte_groups AS gr ON gr.groupid = g2.parent_id ) SELECT * FROM cte_groups as cg

iiiiii