Reporting Services 2005 Server Move

Note: Old Post which was taken from one of my older blogs


Backup Source Server
Backup reporting Services databases Reporting Services via normal SQL backup (db.bak). You have to take the two databases: ReportServer and ReportServerTempDB.
Backup Encryption Keys
Save Encryption Keys from the source server. This saves encrypted content from source server to destination server: snapshots or history data of reports that have been saved within the database and more importantly connections information from the report to the database.
Note Losted Keys: If you have lost your keys you can still install the databases, clear encrypted content, create new keys and continue as normal. You may get a bit unpopular if folk require the history of some key reports so backup your keys.
Use the configuration tool as this is much easier. Start  Programs  Microsoft SQL Server 2005  Configuration Tools  Reporting Services Configurations.
Go to Encryption Key menu option and click the backup button. Enter a filename and a password and your keys will be backed up to a “.snk” file that can be restored into the new server. You must use a strong password when creating the backup of keys or the tools will fail to backup the keys and give an ambiguous error.
If you wish to backup your keys as part of a backup strategy say every night you can always run a command file to backup the keys within your backup programs example below.
RSKeyMgmt –e –f c:\temp\MyKey –p ******
User RSKeyMgmt /? for more options
Remember you need a strong password or it will fail to backup the keys and keep the location secure or better still delete them after the backup.

Install Reporting Services on Target Server
Install reporting services the target service noting service pack levels on the source server. This can have an effect on database versions which upsets reporting services (more of this latter).
Stop Reporting Services Instance on Target Server
Stop the server I use admin tools, but you could use command prompt :
Net stop “SQL Server Reporting Services (MSSQLSERVER)”
Note: (MSSQLSLSERVER) is the instance name of the SQL server your
You will very likely have to stop IIS/WebSite on the server as well as it may keep open connections stopping you restoring the databases.
Stop with command line
iisreset /stop
Restore the databases
Restore the databases, not much else to be said, except to check the security of the users since the dbs came from a different servers and this is a general issue with SQL backups anyway.
Note: You may have to kill of some user processes still attached to this database prior to restore.
Management  Activity Monitor  Double Click : Sort by database and kill processes on databases.
Set Permissions on Databases
You may choose to do this another way, but I created a SQL user “Reporting” rights on ReportServer and ReportServerTempSB databases, you may already have a Domain account for this; so check your old configuration.
Configure New Server
Use the configuration tool as this is much easier. Start  Programs  Microsoft SQL Server 2005  Configuration Tools  Reporting Services Configurations.
Start Report Server and IIS Web Site
This can be done in the config tool under “Server Status”.
Start IIS
Management tools or command line
iisreset /start

Check DataBase Version
If the db version are off sync then you really can’t really do much until its sync. I found this to be a particular issue as I was moving from a “flaky” server without service pack 1 to a server pack 1 install. You usually get errors like:
The found version is 'C.0.8.40'. The expected version is 'C.0.8.43'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)
To fix this go into the “Database Setup” on the config tool and click the upgrade button and this will sync the database version with what’s expected.
Sometimes this causes other issues with restoring keys

Restore Encryption Key
Use your .snk file form the source server and restore this on Target Server.
Go to “Encryption Keys” and hit restore button
Or you can use a command line
rskeymgmt -a -f c:\temp\mykey.snk -p password
There were a few issues with this as the database versions differed and I have to manually hack stored procedure GetDBVersion in ReportSever database to reflect the database version mentioned by the key
ALTER PROCEDURE [dbo].[GetDBVersion]
@DBVersion nvarchar(32) OUTPUT
AS
set @DBVersion = 'C.0.8.43'
Remember to change it back once you get the key installed.
That should be it !