Restoring Reporting Services 2005 Databases

We were having applications that use Reporting Services 2005 as the core Report engine. Though in the initial application development phase the Reporting Services databases backup/restore processes werent an issue, later when we started deploying the application for various clients, it became obvious that we needed a formal procedure to do the moving of the databases between different environments without breaking anything. I wrote a procedure to facilitate this, and I am sharing this for your help.

Reporting Services 2005 uses two databases for storing its information and as workspace to process the reports. For a default installation they will be named “ReportServer” and “ReportServerTempDB”. It becomes necessary at times to copy the databases to recreate the Report Engine of an application on another instance of Reporting Services, mostly for testing purposes and analysis purposes. The following procedure explains the steps to be followed while copying the Reporting Services databases from one instance of Reporting services to another.

IMPORTANT: Ensure that both the source and Destination Reporting Services instances are of the same version and editions before proceeding with the changes.
Execute the following steps in the order to perform a Reporting Services databases from one instance to another.

§ Backup the Databases – Source Instance
1. Open “SQL Server Management Studio” from “Start” à “Programs” à “Microsoft SQL Server 2005” à “SQL Server Management Studio” and connect the Database Engine with the appropriate credentials.
2. Collapse the “Databases” node and select the “ReportServer” database. Click the right button of the mouse and select “Tasks” à “Backup”. This will open the “Backup Database” tool.
3. In the “General” page do the following:
· In the “Source” section, select the Database as “ReportServer” and Backup type as “Full”. · In the “Backup Set” section, ensure that the “Backup Set will expire” is set to “0 Days”.
· In the “Destination” section, select the radio button for “disk” and click the “Add” button. Browse the path to “E:\SigTools” (or any other standard backups location) and set the file name to “ReportServer_”, where is the current date.
4. Click the “OK” button to create the backup set.
5. Repeat Step 2 to 4 to backup “ReportServerTempDB” and save the backup set as “ReportServerTempDB_”, where is the current date.
§ Backup the Encryption Key – Source Instance
Follow the below procedure to backup the encryption key that connects the Report Server with the Reporting Services databases.
1. Open “Reporting Services Configuration” tool from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration” and connect to the appropriate Reporting Services instance.
2. Select the “Encryption Keys” link to open the “Encryption Key” page and do the following.
· Click the “Backup” button to open the “Encryption Key Information” box.
· In the “Password” box, put the standard complex password and in the “Key File” box, browse to “E:\SigTools” (or any other standard backups location) and set the file name to “EncKey_”, where is the current date.
· Click the “OK” button to backup the key. Ensure that the “Task Status” pane shows no error messages.

§ Copying the Backups to the Destination Instance
Physically copy the backup sets to the destination instance so as so make them available for the destination instance. If the backups were taken to a network shared location where the source and destination instances have access to, this step can be ignored.

§ Backup the Databases – Destination Instance
1. Open “SQL Server Management Studio” from “Start” à “Programs” à “Microsoft SQL Server 2005” à “SQL Server Management Studio” and connect the Database Engine with the appropriate credentials.
2. Collapse the “Databases” node and select the “ReportServer” database. Click the right button of the mouse and select “Tasks” à “Backup”. This will open the “Backup Database” tool. In the “General” page do the following:
· In the “Source” section, select the Database as “ReportServer” and Backup type as “Full”. · In the “Backup Set” section, ensure that the “Backup Set will expire” is set to “0 Days”.
· In the “Destination” section, select the radio button for “disk” and click the “Add” button. Browse the path to “E:\SigTools” (or any other standard backups location) and set the file name to “ReportServer_”, where is the current date.
· Click the “OK” button to create the backup set.
3. Repeat Step 3 and 4 to backup “ReportServerTempDB” to and save the backup set as “ReportServerTempDB_”, where is the current date.

§ Backup the Encryption Key – Destination Instance
Follow the below procedure to backup the encryption key that connects the Report server with the Reporting Services databases.
1. Open “Reporting Services Configuration” tool from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration” and connect to the appropriate Reporting Services instance.
2. Select the “Encryption Keys” link to open the “Encryption Key” page and do the following.
· Click the “Backup” button to open the “Encryption Key Information” box.
· In the “Password” box, put the standard complex password and in the “Key File” box, browse to “E:\SigTools” (or any other standard backups location) and set the file name to “EncKey_”, where is the current date.
· Click the “OK” button to backup the key. Ensure that the “Task Status” pane shows no errors.

§ Stopping IIS and Report Server Services
1. Open “Services” mmc console from “Start” à “Programs” à “Administrative Tools” à “Services”.
2. Select the “IISAdmin” service and stop the service. This will open a dialog box asking for stopping all IIS dependant services like “WWW Publishing”, “SMTP”, “FTP Publishing” etc. Click “Yes” to continue. This stops IIS and its dependant services.
3. Open “Reporting Services Configuration” tool from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration”. On the “Server Status” page, click the “Stop” button to stop Report Server.

§ Restoring the Databases – Destination Instance
1. Open “SQL Server Management Studio” from “Start” à “Programs” à “Microsoft SQL Server 2005” à “SQL Server Management Studio” and connect the Database Engine with the appropriate credentials.
2. Collapse the “Databases” node and select the “ReportServer” database. Click the right button of the mouse and select “Tasks” à “Restore” à “Database”. This will open the “Restore Database” tool.
3. Select the “General” page do the following:
· In the “Destination for restore” section, select the Database as “ReportServer”.
· In the “Source for Restore” section, select “From Device” and click the browse button. Select “File” as the “Backup Media Type” and click the “Add” button. Select the backup set for ReportServer database click the “OK” button.
· In the “Backup Sets to Restore” section, select the appropriate backup set.
4. Select the “Options” page, do the following
· In the “Restore Options” section, ensure that the correct file path is provided for the data files and log files.
5. Click the “OK” button to start the database restore process.
6. Ensure that the restore completed successfully.
7. Perform steps 2 to 6 to restore the ReportServerTempDB database.

§ Starting IIS and Report Server Services
1. Open “Services” mmc console from “Start” à “Programs” à “Administrative Tools” à “Services”.
2. Select the “IISAdmin” service and start the service. Start all the IIS dependant services like “WWW Publishing”, “SMTP”, “FTP Publishing” etc which were stopped following the “IIS Admin” service stoppage.
3. Open “Reporting Services Configuration” tool from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration”. On the “Server Status” page, click the “Start” button to start Report Server.

§ Verifying the Reporting Services Configurations
Follow the below procedure to verify that the Reporting Services configurations on both the source and destination instances are matching.
a. Open “Reporting Services Configuration” tool for the source and destination instances from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration” and connect to the appropriate Reporting Services instances.
2. Verify that the configurations are exactly matching for both the instances. This includes the following.
· Report Server virtual directory
· Report Manager virtual directory
· Windows Service Identity
· Web Service Identity
3. Click the “Apply” button to save the changes.

§ Restoring the Encryption Key – Destination Instance
Follow the below procedure to restore the Encryption Key backed up from the source server.
1. Open “Reporting Services Configuration” tool for the destination instances from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “Reporting Services Configuration” and connect to the appropriate Reporting Services instance.
2. Open the “Encryption Keys” page and click the “Delete” button to delete the already existing Encryption Key on the destination.
NOTE: Ensure that you have backed up the Encryption Key before you any deletions.
3. Click the “Restore” button to open up the “Encryption Key Information” box. Provide the password and click the browse button to locate the key file. Click the “OK” button to do the restoration.
4. Ensure that there are no error messages in the “Task Status” pane.
5. Open the “SQL Server Configuration Manager” from “Start” à “Programs” à “Microsoft SQL Server 2005” à “Configuration Tools” à “SQL Server Configuration Manager” and restart the service “SQL Server Reporting Services”

IMPORTANT: It is recommended that the destination server is rebooted after the changes.