What will be missed if report server 2008R2 database gets restored in SQL server 2014

Question

I'm planning to migrate all the SSRS reports from 2008R2 server to new sql server 2014 environment. As far as migration is concerned, I was asked to take the backup of 2008R2 report server database and restore it in 2014 server.
I was not convinced with this since there may be new tables available in 2014 Report server.
The new features of 2014 cant be utilized if the old report server database is restored.

Please let me know if this thought is correct.
Are there any new tables available in 2014 report server database?
What is the best option to migrate ssrs reports from 2008 r2 to 2014 server?.

Answer 1

Your question is a bit confusing, I will assume you want to move the database from a Server A with SQL Server 2008R2 + SSRS 2008R2 to a Server B with SQL Server 2014 + SSRS 2014.

Are there any new tables available in 2014 report server database?

There is no official communication on it.
If you really want to know it you could do a schema compare between the 2 versions.
But do not forget to compare everything, not only tables:

• Columns
• Stored Procedures, Functions
• ...

Database structure is not the only thing to take into account, what about:

• All the configuration files
• Encryption Keys
• ...

What is the best option to migrate ssrs reports from 2008 r2 to 2014 server?.

Short answer:
My recommended way of doing it would be to use RS Scripter and generate a script on Server A with all the objects (reports, datasources, subscriptions, ...) you want to move.
Then you can restore it on Server B.

---

Long answer:
If you really want to migrate the full database like you were asked to do, there is no officially supported way to move the database to another SQL instance and upgrade the version at the same time.

You could try to follow the steps to Backup and Restore Operations for Reporting Services, and apply it to a newer SQL Server instance with another SSRS version, but it will be at your own risk.

The supported ways to do would be to either:

1. Upgrade from SSRS 2008R2 to SSRS 2014 on Server A
2. Move from Server A to Server B

or

1. Move from Server A to Server B
2. Upgrade from SSRS 2008R2 to SSRS 2014 on Server B

Here are the related MSDN articles for these operations:
Migrate a Reporting Services Installation (Native Mode)
Upgrade to SQL Server 2014

Again, I would advise using a tool to migrate the reports and other items instead of trying to migrate the full database.

Answer 2

I do not think restore 2008R2 reportserver db on 2014 will work, because the report definition schema is totally different.

I did a migration task to move 2008R2 reports to 2012. Because there are hundreds of reports on the 2008R2 server, I found the easiest way is to write some codes to read report definition, and then create on 2012 server.

Answer 3

From: https://msdn.microsoft.com/en-us/library/ms143747.aspx

There are two general approaches to upgrading a Reporting Services deployment:

• Upgrade: You upgrade the Reporting Services components on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade. In-place upgrade is not supported from one mode of Reporting Services server to another. For example, you cannot upgrade a Native Mode report server to a SharePoint mode report server. You can migrate your report items from one mode to another. For more information, see the ‘Native to SharePoint Migration’ section later in this document.
• Migrate: You install and configure a new SharePoint environment, copy your report items and resources to the new environment, and configure the new environment to use existing content. A lower level form of migration is to copy the Reporting Services databases, configuration files, and if you are using SharePoint mode, the SharePoint content databases.

If you do an in-place upgrade from SQL Server 2008R2 to SQL Server 2014, then everything should work as expected.