Upgrading a Report Server Database
The report server database schema can change with each new release of Reporting Services. This topic explains how to upgrade the database schema to use the latest format. Note that once you update the schema, you cannot rollback the upgrade to an earlier version. Always backup the report server database before upgrading in case you need to recreate a previous installation.
Note
You can upgrade a report server database that runs on a SQL Server 2000 Database Engine instance without having to upgrade the Database Engine to SQL Server 2005. You can also use a SQL Server 2000 Database Engine instance to host a new report server database that you create for a SQL Server 2005 Reporting Services installation.
The report server database is upgraded in three stages:
- The schema is upgraded automatically during setup or manually through the Reporting Services Configuration tool.
- Metadata and security descriptions are upgraded on first use of the report server database after the schema is updated.
- Published reports and compiled report snapshots are updated on first use. For more information, see Upgrading Reports.
In addition to the report server database, a report server also uses a temporary database. The temporary database is upgraded automatically when you upgrade the report server database.
Ways to Upgrade a Report Server Database
The report server database can be upgraded automatically or manually.
- Setup will upgrade a report server database automatically if you are running a default configuration and the report server database is installed locally.
- Setup can also upgrade a report server database automatically if you run Setup from the command prompt. If the report server database is installed on a remote SQL Server instance, or runs on a different named instance on the same computer, you can run Setup from the command prompt and use the RSUPGRADEDATABASEACCOUNT and RSUPGRADEPASSWORD arguments to specify credentials that have permission to update the schema.
- You can manually upgrade any report server database regardless of whether it runs locally or on a remote server. To upgrade a report server database manually, use the Reporting Services Configuration tool. An upgrade option on the Database Setup page runs a script that updates the schema to the most recent version.
Upgrading a Report Server Database on a Remote Server
If you are upgrading a Reporting Services installation that includes a remote report server database, you might encounter problems when upgrading the database schema. By default, Setup uses the security token of the user who is running the Setup program to connect to the remote SQL Server instance and update the schema. If you have administrator permissions on both the local and remote computer, the database upgrade will succeed. Similarly, if you run Setup from the command prompt and specify the RSUPGRADEDATABASEACCOUNT and RSUPGRADEPASSWORD arguments for an account that has permission to modify the schema on the remote computer, the database upgrade will succeed. However, if you do not have permission to update the schema on the remote computer, the connection will be refused with the following error:
"Setup was not able to upgrade the report server database schema. You must update the database schema manually after setup is finished. To update the schema, run the Reporting Services Configuration tool, open the Database Setup page, and click Upgrade. For more information, see article 91310 in the Microsoft Knowledge Base."
The report server program files will be upgraded, but the report server database will be in the format of the previous version. The report server will be unavailable while the report server database is in the older format.
To upgrade the database manually
If Setup did not upgrade the report server database automatically, your report server installation is only partially upgraded. To complete the upgrade, you must upgrade the report server database manually by running the Reporting Services Configuration tool after Setup is finished.
- Run the Reporting Services Configuration tool and connect to the report server that you just upgraded. For more information about how to start the tool and connect to a server, see How to: Start Reporting Services Configuration.
- Open the Database Setup page, and then click Connect to connect to the SQL Server instance that hosts the report server database.
- Select the report server database that you want to upgrade.
- Click Upgrade to update the database schema.
The report server will be available once these steps are complete.
Upgrading from SP1 to SP2
The type of credentials that are specified on the report server database connection will determine whether Setup can upgrade a report server database from an SP1 or CTP version of SP2 to the final release of SP2.
If the report server connects to the report server database using database credentials or a Windows user account that differs from the one used to run the Report Server Windows service, the database will not be upgraded when the rest of the software is upgraded. If this occurs, you must use the Reporting Services Configuration tool to upgrade the report server database manually. Follow the instructions in the previous section on how to upgrade the database manually.
See Also
Concepts
Upgrading Reporting Services
Upgrading a Default Installation of Reporting Services
Migrating Reporting Services
Other Resources
Creating a Report Server Database
How to: Start Reporting Services Configuration
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|