Swapping the RS Catalog

Sometimes, you may need to swap the RS catalog database. For example, you may need to clone the catalog database from a production machine to troubleshoot an issue. One way to do so is to replicate the report items (report definitions, data sources, images, etc.). Another, and perhaps faster, way is to copy the report catalog database and redirect the Report Server temporarily to the new catalog by using the handy Reporting Services Configuration Manager (new with RS 2005). The following steps worked for me to redirect my local SSRS instance (SP1+hotfix) to a different catalog (from SSRS 2005 RTM).

1. Use the SQL Server Management Studio to create a new database, e.g. ReportServerTest.
2. Create a new database ReportServerTestTemp to serve as the Report Server Temp database.
3. Back up the production database you need to troubleshoot.
4. Restore the production database to ReportServerTest. Make sure to rename the database files by flipping to the Options tab (e.g. rename the files to ReportServerTempDB.mdf and ReportServerTempDB_log.mdf)
5. Back up the production instance of the ReportServerTemp database and restore it to ReportServerTestTemp (again rename the database files).

Note: As its name suggests, the ReportServerTemp database is just a temporary database used by the Report Server for session caching. Since you don't need its data, you can restore (copy) it from any RS temp database.

6. Open the Reporting Services Configuration Manager (Microsoft SQL Server 2005 program group -> Configuration Tools -> Reporting Services Configuration) and switch to the Database Setup tab (see attached screenshot). 
7. Enter the name of the new Report Server database (ReportServerTest) and hit Apply. If your SSRS instance is newer version of SSRS 2005 (e.g. SP1), you will be prompted to upgrade. If all is well and you don't see error and warning messages, the Report Server will be re-configured to use the new database. If at this point you try to connect to the Report Server (e.g. /reportserver/) you will get the rsReportServerNotActivated error. The reason for this is that the Report Server cannot decrypt the content of the new catalog with its encryption key.
8. In the Reporting Services Configuration Manager, flip to the Initialization tab. You should have two report two rows (see attached file) in the grid corresponding to your local computer name (initialized) and the computer which hosted the report catalog you restored. Let's assume you don't have connectivity to the remote machine and you cannot connect to it.
9. Open the command prompt and execute:

     "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RsKeyMgmt.exe" -d

This will effectively remove any encrypted content, including connection and subscription credentials.

Note: If you have a backup of the encryption keys from the source server, to prevent losing the encrypted content you can restore the encryption keys on the target server by executing the following commands in the order shown: rskeymgmt –e, rskeymgmt –r, and then rskeymgmt –a.   

10. At this point the Report Server should be initialized. You may need to go back to the Initialization tab and click Initialize (you should have only row with your computer name).
11. You can now open the Report Manager and change the connection strings for the data sources .
12. Once you done testing, go back to Reporting Services Configuration Manager and flip the catalog database to the original ReportServer database.