Migrating SQL Reporting Services 2005

I'm in the process of migrating our production report server and databases into a Virtual PC client to create a Demo machine. This took a lot more steps than I expected.

I installed SQL and Reporting Services. I then tried to back up and restore the report server database and was not having any luck. I later discovered that I need to move both the report server database and the report server temp database.

I used the a script found on MSDN titled Moving a Report Server Database to Another Computer . It looks like it would be found in the SQL Server 2005 Books Online.

This script only works on sql server 2005. There are some new keywords used that 2000 does not support. One key detail was not to use the GUI to back up and restore the databases. Either use a script or detach/reattach the files. The article explains this.

In the configure report server gui, I had to change the credentials type. The default was not working for me. I set it to windows and entered in the account info. connected and applied the changes. If you entered in the wrong password, you will not be alerted to the mistake.

Next step was to restore the encryption key. You will have to export it from the first report server by using the same tool. I was getting this error on the restore: "The encrypted value for the "LogonCred" configuration setting cannot be decrypted". My problem was leaving the password blank on the database set up section.

Once I placed a password in there and gave that account access to the databases, it worked for me.

Another issue I was having was getting the report server initialized. Adding the encryption key solved that.

I then moved over my content, fixed permitions, and changed the datascources. EXEC sp_change_users_login 'Update_One', 'username', 'username' can be an important command to fix logon accounts after a database migration.