Part 2 of 2: Upgrading a Report Server database used by older versions of Team Foundation Server
January 3, 2013 Leave a comment
If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2012 database, you must move it to another SQL Server 2012 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.
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 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.