Export the SSRS Report Server's folder / item structure using TSQL

If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.

Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name

In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:

SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName

When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration