Generate upgrade script in Reporting Services with WMI
Recently I had to test some of the upgrade scenarios for Reporting Services and in some scenarios I hit the infamous error message "The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.9.45'. The expected version is '147'. (rsInvalidReportServerDatabase)"
In a regular environment this is due the RSExecRole in the SQL Server database is not an owner of the Reporting Services Databases
Reporting Services perform an auto databse upgrade applying upgrade scripts to the databases and it uses the RSExecRole to run them if it doesn't have the right permissions it will not be able to upgrade the database and the steps to fix that are detailed on https://msdn.microsoft.com/en-us/library/cc879332.aspx.
But besides the error I wanted to double check manually why the ugprade scripts were not executing in my environment, I had the option of copy the script from the sources and execute it (the advantange of the source code access), but I want an approach more close to the things that Reporting Services does, so I made some research and found that we expose to public the methods to get the script in WMI (https://msdn.microsoft.com/en-us/library/ms154641.aspx)
The method is called GenerateDatabaseUpgradeScript and you can create a C# program to consume it, but I need something quicker so I made a powershell script to get the script directly from the Server
$RSAdmin = gwmi -namespace "root/Microsoft/SqlServer/ReportServer/RS_MSSQLSERVER/v10/Admin" MSReportServer_ConfigurationSetting
$RSAdmin.GenerateDatabaseUpgradeScript("ReportServer","C.0.9.45'").Script | Out-File upgradescript.sql
Now I have the upgradescript.sql that can be run manually
In conclusion I was able to quickly generate the script I needed directly from the product and execute it with different credentials to see how it worked.
Comments
- Anonymous
June 10, 2011
Interesting thanks for sharing is a good start, regards.