SQL SERVER -Moving the Report Server Databases to Another Computer

Detaching and Attaching the Report Server Databases

You can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use the following steps to move the databases:

1.       Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.       Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.       Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.       Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.       Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.       In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.       Right-click the Databases node, and then click Attach.

8.       Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.       After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.    Start the Reporting Services Configuration tool and open a connection to the report server.

11.    On the Database page, select the new SQL Server instance, and then click Connect.

12.    Select the report server database that you just moved, and then click Apply.

13.    On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.    Restart the Report Server service.

Note Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.

Using RESTORE and MOVE to Relocate the Report Server Databases

When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

How to Configure the Report Server Database Connection

1.       Start the Reporting Services Configuration tool and open a connection to the report server.

2.       On the Database page, click Change Database. Click Next.

3.       Click Choose an existing report server database. Click Next.

4.       Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

5.       In Database Name, select the report server database that you want to use. Click Next.

6.       In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

7.       Click Next and then Finish.