We recently had the misfortune of losing one of our management servers, and it happened to be the management server that also ran reporting server. Of course, reporting services was the one component that was not backed up.
As we had built up a fair history, and didn’t want to lose reporting, we needed some way of restoring reporting services without wiping out the history.
We set up a lab to test all our theories – because you do not want to try this stuff on live first!
The steps describes assumes the following:
- Your reporting databases reside on a separate SQL server – either the same one hosting the OnePoint database or a completely different one, but a server separate from your management servers.
- You know the credentials your reporting was installed with previously.
- You know all the usernames and passwords for your MOM environment
Step 1: On your SQL server, detach the ReportServer and ReportServerTemp databases. Move the MDF and LDF to a different location for safe keeping.
Step 2: On the reporting server, install reporting services, connecting back to your SQL server. The install process will create blank databases.
Step 3: On the SQL server, stop the SQL Server service. Replace the new MDF and LDF files created with the ones you backed up in step 1. Start the SQL Server service (and the SQL Agent service, if it is required.)
Now, things become a little tricky.
On the reporting server, you need do the following:
- In the command prompt, navigate your way to %install drive%\Program Files\Microsoft SQL Server\80\Tools\Binn
- run the following command:
This will delete all the encrypted information out of the database, like the connection string details.
- Now, run the following command:
rsconfig –c –s [sql server name] –d [report database name] –u [username] –p [password] –a [authentication method]
and replace the values between the [brackets] with the values relevant to your environment.
- run iisreset
Your reporting will now be back up and running. The last step is now to go into the reporting web console, and update the connection settings in the SCDW connection.