Ever upgaded a SQL Server Reporting Services instance after migrating to a new SQL Server, and everything looks good until you click on the Report Server URL to be greeted with:
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.8.54’. The expected version is ‘147’. (rsInvalidReportServerDatabase)
You dillegently read through all the google and bing results you can find. But there’s nothing useful in there, and all the guides say that connecting the Reporting Services instance should upgrade the database schemas to the correct versions. So why isn’t it for you?
By now you’ve started to delve into the depths of the Reporting Services logfiles (always a great place to look for any Reporting Services issues, can be quicker than googling). And you’ve spotted what looks like the smoking gun:
library!WindowsService_0!b84!07/23/2012-11:28:31:: i INFO: Current DB Version C.0.8.54, Instance Version 147. library!WindowsService_0!b84!07/23/2012-11:28:31:: i INFO: Starting upgrade DB version from C.0.8.54 to 147. library!WindowsService_0!b84!07/23/2012-11:28:32:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.; library!WindowsService_0!b84!07/23/2012-11:28:32:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Cannot find the object "dbo.SnapshotData" because it does not exist or you do not have permissions. Changed database context to 'ReportServerTempDB'.
So you dutifully go and check if dbo.SnapShotData exists, check the permissions, maybe even give you service user sysadmin rights as a last chance hope?
And still none of it works. So what is the problem then. Well, did you move over ReportServerTempDB as well as ReportServer? Wait a sec, why should you? Didn’t some guy on the internet say you didn’t need to backup that one, just like TempDB? Well they may have said it, and sometimes they might have been right. But when doing an uprade you need ReportServerTempDB as well. So now you need to find it, hopefully you’ve still got the original kicking around to make life easier. If not you’ll need to find one of the same original version and then sort the permissions out.
This is a very common issue we see when we get called in to look at Reporting services upgrades gone bad. For most instances, the space used by a ReportServerTempDB backup isn’t really a worthwhile reason for not taking one just to make your life easier.