In December 2010, I started a little series called SQL Confessions. The idea of this series is as a learning exercise when I come across something that I either did wrong or couldn’t find a better way of doing it at the time. In that first episode, I admitted to the use of a cursor (drat). I should go back and update that posting because I got a recommendation of an alternative way to do that. As of yet, I haven’t tested and it was a one time run (phwew).
This time around, the consequences were a bit bigger. This time it was a failure and it is related to SSRS. I came away from the experience with a few options that I want to test and see which method will work best in the event the same thing happens.
In this case, the ReportServer databases were backed up and the backups were good (I used them to restore a secondary database). We even had SSRS installed on the failover server. From that one can deduce that a recent failover was required for SSRS – and this was not a test. Well, if you have the database backed up and a standby server ready to go – what is missing?
What was missing was the encryption key backup. Life in recovery is a lot easier if that backup key exists. I could not find it. This did not prevent me from being able to recover SSRS though – it just meant a little more work.
If you lose your encryption key, then you have an issue with any data that may be encrypted. Some of the things that are encrypted in SSRS 2008 are: Connections and Subscriptions.
When you lose the encryption key you lose those items too. If you have good documentation you can most likely recover them. In my case I was able to recreate the Connections (DSNs) and bring the reports back up. I don’t want to reveal the process I used just yet because I do want to test this a bunch more and find the better method to use.
As for the recommended method, here are some articles for reference:
1. Move to a new Server SSRS 2005 (useful even for 2008).
2. MSDN – Backup the Encryption Key
I hope you find this useful, and the followup should be useful.
