Yesterday I spent some time trying to write a script that deletes all schedules, subscriptions and related SQL Server agent jobs from a reporting services instance by using cursors and stored procedures in the msdb and Reportserver databases.
Turns out that the reportserver tables have the necessary triggers on delete, so all you need to do is a simple
DELETE FROM ReportSchedule
DELETE FROM Subscriptions
DELETE FROM Schedule
I just did a migration from a SSRS 2005 installation to SSRS 2008 instance on a different server. Microsoft has very nice guides about the migration process that I followed, but I encountered problems when trying to restore the encryption key on the new server. I tried to post the question to the msdn message board here, but got no response.
I’m currently trying to move a ssrs 2005 installtion to a sql server 2008 instance on another physical server. I’ve followed every step on the official migration guide, and the database is upgraded and all data is there. The problem is when I try to restore the encryption key on the 2008-server. The old server then shows up under “scale-out deployment”, and is impossible to remove. If I go to the 2008 report manager, I get “The feature: “Scale-out deployment” is not supported in this edition of Reporting Services”. If I try to remove the old server from the “scale-out deployment”-tab, I get: “Microsoft.ReportingServices.WmiProvider.WMIProviderException: No report servers were found. I tried to delete all encrypted data on the 2008 server, and that made the 2008-instance work, but I have 150 subscriptions on the old 2005-instance that I don’t want to recreate (understatement).
The solution was to use the rskeymgmt utility by command line interface when restoring the key:
rskeymgmt -i NEWINSTANCE -a -f FILEPATH -p PASSWORD
If you use the configuration tool, the restored key will be linked to the wrong instance, and the old server will show up as a scale-out deployment server.
Its easier to just just do the following after you restore the key using the configuration tool:
DELETE FROM Keys WHERE MachineName='OLDMACHINENAME'