How to move a Reporting Services database from a computer that is running Reporting Services to another computer (842425)

MORE INFORMATION

You may want to move a Reporting Services database from one computer to another computer for several reasons. For example, you use Reporting Services to create reports on a computer that is used in the development environment of an application. If you want to move these reports to a computer that is used in the production environment, you must move the Reporting Services database together with Reporting Services Windows service, and Reporting Services Web service. If you move the Reporting Services database and the Reporting Services service from one computer to another computer, you do not have to deploy the reports again.

SQL Server 2000 Reporting Services

To move a SQL Server 2000 Reporting Services database to another computer and to make Reporting Services point to that database, follow these steps:

1. Back up the Reporting Services database and the Reporting Services temporary database on the source computer.
   
   Note By default, the name of the Reporting Services database is ReportServer, and the name of the Reporting Services temporary database is ReportServerTempDB.
2. Restore the databases that you backed up in step 1 on the appropriate instance of SQL Server on the destination computer.
3. To make Reporting Services point to the database that you restored in step 2, run the following command at a command prompt on the computer that is configured as the report server:
   
   RSConfig -c -s Server name -d Reporting Services database name -a Sql|Windows -u User name -p User password
   
   Note For more information about the Rsconfig.exe command-line utility, run the following command at a command prompt:
   
   RSConfig /?

To move the Reporting Services database together with the Reporting Services Windows service, and Reporting Services Web service from one computer to another computer, you must first back up the encryption keys on the source computer. You can then move the Reporting Services database from the source computer to the destination computer and then restore the encryption keys on the destination computer. To do this, follow these steps:

1. On the source computer, use the Rskeymgmt.exe command-line utility to back up the symmetric encryption keys. To do this, run the following command at a command prompt:
   
   RSKeyMgmt.exe -e -f Path of file name -p Strong password
   
   Note For more information about the Rskeymgmt.exe command-line utility, run the following command at a command prompt:
   
   RSKeyMgmt /?
2. On the destination computer, follow these steps:
   1. Install Reporting Services.
   2. Stop the Report Server Windows service.
   3. Stop Microsoft Internet Information Services (IIS).
3. Move the Reporting Services database and the Reporting Services temporary database from the source computer to the appropriate instance of SQL Server on the destination computer.
   
   Note By default, the name of the Reporting Services database is ReportServer, and the name of the Reporting Services temporary database is ReportServerTempDB.
   
   For more information about how to move databases between computers that are running SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

   314546 How to move databases between computers that are running SQL Server

4. On the destination computer, follow these steps:
   1. Start the Report Server Windows service.
   2. Start IIS.
   3. Use the Rsconfig.exe command-line utility to configure the report server instance to use the Reporting Services database that you moved to the destination computer in step 3. To do this, run the following command at a command prompt:
      
      RSConfig -c -s Server name -d Reporting Services database name -a Sql|Windows -u User name -p User password
      
      Note For more information about the Rsconfig.exe command-line utility, run the following command at a command prompt:
      
      RSConfig /?
   4. Use the Rskeymgmt.exe command-line utility to apply the symmetric encryption keys that you backed up in step 1. To do this, run the following command at a command prompt
      
      Note Path of file name is the path of the file name and Strong password is the password that you used in step 1):
      
      RSKeyMgmt -a -f Path of file name -p Strong password

Note The solutions in this article do not account for problems that will occur if you are restoring a standard edition report server. The problem is that the row for the old instance will still be in the database after you follow the instructions. Standard edition will not start because it will think that the source computer and the destination computer have joined a Web farm.

To resolve this problem, manually run Delete from keys where client > -1 before you apply a backup key. Manually run Delete from keys where client > -1 only for Standard edition. If you have a Web farm, you must disable all the other nodes in the Web farm that are presumably still working.

SQL Server 2005 Reporting Services

Note Assume the name of the SQL Server 2005 reporting services database you will move is ReportServer.

To move a SQL Server 2005 Reporting Services database to another computer and to make Reporting Services point to that database, follow these steps:
Operations on the source server

1. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.
2. Back up the ReportServer database, the ReportServerTempDB database, and the rest of the databases that will be accessed by your reports.
   
   For more information about how to back up and restore a database, visit the following MSDN Web site:

   http://msdn2.microsoft.com/en-us/library/ms143724.aspx

3. Back up the encryption key by using the Reporting Services configuration Manager.
   
   For more information about how to back up the encryption key, visit the following MSDN Web site:

   http://msdn2.microsoft.com/en-us/library/ms157275.aspx

4. Copy the backup database files and the backup encryption key file to the destination server.

Operations on the destination server

1. Stop Internet Information Services (IIS) service by running the iisreset /stop command at the command prompt.
   
   For more information about how to start and stop IIS, visit the following Microsoft Web site:

   http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/95826e7a-bac4-4e1f-bcb6-c52d49c9d7f4.mspx?mfr=true

2. Open the Reporting Services Configuration Manager, and then connect to the instance of SQL Server 2005 Reporting Services.
3. To stop reporting services windows service, click Server Status, and then click Stop on the Report Server Status page . If Stop is unavailable, the service has already been stopped,
4. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.
5. Back up the ReportServer database and the ReportServerTempDB database if they exist.
6. Detach the ReportServer database and the ReportServerTempDB database if they exist.
7. Restore the backup databases from the source server.
8. In the Reporting Services Configuration Manager, make sure that the configurations in the following pages are identical to those in the source server:
   • Configurations in Report Server Virtual Directory
   • Configurations in Report Manager Virtual Directory
   • Type of service account configuration in Windows Service Identity
   • The Report Server configuration and the Report Manager configuration in Web Service Identity
9. To start reporting services windows service, click Server Status in the Reporting Services Configuration Manager, and then click Start on the Report Server Status page.
10. Start IIS service by running the iisreset /start command at the command prompt.
11. Remove the encryption key from the source server. If the SQL Server 2005 Reporting Services you are is included with SQL Server 2005 Enterprise Edition, you can perform this task directly in Reporting Services Configuration Manager. If not, you have to rely on the rskeymgmt utility to accomplish this.

    SQL Server 2005 Enterprise Edition environment

    1. In the Reporting Services Configuration Manager, click Initialization.
    2. Remove the encryption keys from the other instances of SQL Server 2005. Only keep the key that has the same server name as that of the destination server.
    3. In the Reporting Services Configuration Manager, click Encryption Keys, and then click Restore in the Encryption Key page.
    4. In the Encryption Key Information window, type the password you use to back up the encryption key from the source server, locate the backup encryption key from the source server, and then click OK.

    Other versions of SQL Server 2005 environment

    
    
    Note This approach also applies to SQL Server 2005 Enterprise Edition environment.
    1. Click Start, click Run, type cmd, and then click OK to open the command prompt window.
    2. Examine the encryption keys that exist. Based on the type of the instance of SQL Server 2005 installed on the destination server, you type the following in the command prompt window.
       
       Default instance
       Type rskeymgmt -l, and then press Enter
       
       Named instance
       Type rskeymgmt -l -i InstName, and then press Enter
       
       You will see two encryption keys listed. One is from the source server, and the other is from the destination server. The format of the encryption keys listed is as follows:
       ComputerName\InstNameEncryptionKeyID
       
       Note ComputerName is the name of the destination server.InstName is the placeholder for the name of the instance of SQL Server 2005 installed on the destination server. EncryptionKeyID is the placeholder for the ID of the encryption key.
    3. Delete the encryption key from the source server. Based on the type of the instance of SQL Server 2005 installed on the computer, you type the following in the command prompt window.
       
       Default instance
       Type rskeymgmt -r SourceEncryptionKeyID, and then press Enter
       
       Named instance
       Type rskeymgmt -i InstName -r SourceEncryptionKeyID, and then press Enter
       
       Note SourceEncryptionKeyID is the placeholder for the ID of the encryption key from the source server that is obtained in step 2.
    4. Repeat step 2. You will see that only the encryption key of the destination server exists now.
12. Restore the backup encryption key from the source server. To do this, follow these steps:
    1. In the Reporting Services Configuration Manager, click Encryption Keys, and then click Restore in the Encryption Key page.
    2. In the Encryption Key Information window, type the password you use to back up the encryption key from the source server, locate the backup encryption key from the source server, and then click OK.
13. Make sure that the same user account as the ASP.NET service account is set on the Rsreportserver.config file. To do this, follow these steps:
    1. In the Reporting Services Configuration Manager, click Web Service Identity. Notice the ASP.NET Service Account information.
    2. Open the SQLInstall:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer folder. Right-click the Rsreportserver.config file, and then click Properties.
       
       Note SQLInstall is the placeholder for the drive where SQL Server 2005 is installed. MSSQL.X is the placeholder for the Instance ID of the instance of SQL Server 2005 Report Services that is running on the destination server. To obtain the Instance ID, click Server Status in the Reporting Services Configuration Manager, and then notice the Instance ID information.
    3. In the Rsreportserver properties dialog box, click the Security tab.
    4. If the ASP.NET service account is not listed, add it. Make sure that the ASP.NET service account has Read and Read & Execute permissions.
    5. Click OK to close the Rsreportserver properties window.
14. Open the Web browser and view the reports on the destination server.

If you experience problems when you view the reports on the destination server, you have to examine the security settings for the components of the reports in SQL Server Management Studio. The error message on the Web page will show you where the problem is.