Moving Reporting Services 2005 database from one SQL Server Instance to another

The development environment I’m currently working in uses two servers. IIS with Reporting Services 2005 on box1, and SQL Server 2005 on box2.

For whatever reason, I was unable to get Reporting Services to install correctly on box1 with box2 as the SQL Server instance. To save hassles I went ahead an installed an instance of SQL Server 2005 beta-2 on to box1 at the same time as Reporting Services.

Now that IIS has been configured, and Reporting Services has been tested that it works, I’m ready to move the databases from box1 to box2.

Moving the databases is a simple step. Stop the Reporting Services service by typing the following into command prompt:

net stop “reporting server (MSSQLSERVER)”

(or use the Services console from Start / Control Panel / Administrative Tools / Services).
Then using SQL Management Studio, select the ReportServer database, and detact. Detach the ReportServerTempDB also. Copy the database *.mdf and *.ldf files to box2, and attach. (I copied the files, incase I needed to go back for some reason).

Now that the databases are residing on the correct SQL Server, it’s time to reconfigure Reporting Services. Luckily Microsoft has thought of this already and provided us with the RSConfig command line configuration tool.

The rsconfig tool is pretty basic to use and really only has two uses: to change the connection info that a report server uses to connect to its database; or to change the account that report server uses to connect to a remote database server when other credentials are not available.

The syntax of the rsconfig command is:

rsconfig {-?}
{�c }
{�e }
[�m computername]
[�s servername]
{�d databasename}
{�a authmethod}
[-u username]
[-p password]
[-t ]

Before using the rsconfig tool however, you should take a backup of your server, or at least a copy of the rsreportserver.config file (which for me was located at c:/program files/Microsoft SQL Server/MSSQL.2/Reporting Services/ReportServer/rsreportserver.config)

So for me, the following configured Reporting Services just fine:

rsconfig -c -s box2 -d ReportServer -a Sql -u MSReportUser -p **************

Start the Reporting Services service again with:

net start “reporting server (MSSQLSERVER)”

(or use the Services console from Start / Control Panel / Administrative Tools / Services)

You may need to restart the IIS Application Pool that Report Server is a part of as well.

If you get a message that your server is not activated, use the

rsactivate Utility.

Tags: IIS, Management Studio, SQL Server, Tools