Clearing SSRS Objects

12 February 2010

While performing integration testing with my current project, I often have to clear all of the reports, folder, datasources, etc from Reporting Services. Here is a very simple SQL script to quickly get this done.

 1 -- This database name might be 
 2 -- different in your environment
 3 use reportserver 
 4 
 5 delete from datasource
 6 
 7 -- We need to keep the root node, so leaving
 8 -- one catalog row.
 9 while (select count(*) from [catalog])  > 1  
10 begin 
11   delete from [catalog] 
12   where parentid is not null 
13   and not (itemid  in 
14     (select distinct parentid from [catalog] 
15       where  parentid is not null))
16 end