SQL Server 2008 upgrading the Report server database

The report server database can provide internal storage for one or more report server instances. Because the report server database schema may change due to the introduction of a new Reporting Services version, the database version is required to match the version of the report server instance that is used. In most cases, the report server database can be upgraded automatically, and you do not need to perform any specific actions.

How to upgrade a report server database

The following list indicates all the conditions for upgrading the report server database:

Setup upgrades a single instance of the report server. The database schema is automatically upgraded after the service starts and the report server determines that the database schema version does not match the server version.

When the service starts, the report server checks the database schema version to verify that it matches the server version. If the database schema version is low, the schema is automatically upgraded to the schema version required by the report server. The automatic upgrade feature is especially useful if you are restoring or attaching a lower report server database. A message is entered in the report server trace log file indicating that the database schema version has been upgraded.

When you select an older version to use with a new report server instance, the Reporting Services Configuration tool upgrades the local or Remote report server database. In this case, you must confirm the upgrade operation before this action occurs.

The Reporting Services Configuration tool no longer provides separate upgrade buttons or upgrade scripts. These features are obsolete in SQL Server 2008, given the automatic upgrade feature of the Report Server service.

After schema update, the upgrade cannot be rolled back to a previous version. Make sure that you back up the report server database in case you need to recreate the previous installation.

How to update schema, meta data, and report server content

There are three steps to upgrading a report server database:

The schema will be upgraded automatically after the service is installed and started, or when the SQL Server 2005 or 2000 Report server database is selected in the Reporting Services Configuration tool. In addition, the Report Server service checks the database version at startup. If the report server is connected to an earlier version of the database, the report server updates the database during the startup process.

Upgrade the security descriptor when the report server database is used for the first time after the schema is updated.

When you first use, upgrade published reports and compiled report snapshots.

The report server also uses the staging database in addition to the report server database. When you upgrade the report server database, the staging database is automatically upgraded.

Upgrading the Report Server database

If the Reporting Services installation that you are upgrading contains a report server database, you may experience problems upgrading your database schema. By default, Setup uses the security token of the user running Setup to connect to the remote SQL Server instance and update the schema. If you have SQL Server sysadmin permissions on the database server that hosts the report server database, the database will be upgraded successfully. Similarly, if you run Setup from the command prompt and specify rsupgradedatabaseaccount and Rsupgradepassword parameters for the account with sysadmin permissions to modify the schema on the remote computer, the database will also be upgraded successfully.

However, if you do not have permission to the database on the remote computer, the connection is rejected and the following error occurs:

"Setup was unable to upgrade the report server database schema. You must manually update the database schema after Setup completes. To update the schema, run the Reporting Services Configuration tool, open the Database Setup page, select the database again, and then click Apply. The database will be upgraded automatically. ”

The report server program file will be upgraded, but the report server database will be in the format of the earlier version. The report server will not be available until the upgrade process is completed by manually upgrading the database.

Manually upgrading a Database

If the installer does not automatically upgrade the report server database, only partial report server installations can be upgraded. To fully upgrade, you must run the Reporting Services Configuration tool and manually upgrade the report server database after Setup completes.

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 the server, see how to start the Reporting Services configuration.

Open the Database Setup page, and then click Connect to connect to an instance of SQL Server that hosts the report server database.

Select the report server database that you want to upgrade.

Click Apply to upgrade the database schema. After you complete the above steps, you can use the report server.