locked
Can I restore a "Microsoft SQL Server 2008 R2" database to "Microsoft SQL Server 2016" RRS feed

  • Question

  • I am not a DBA but have the responsibility to upgrade our SSRS environment.

    We ran a migration tool for this but the subscriptions did not come over.

    I was told by a DBA friend that I "must restore the databases to get the subscriptions".

    Our current production ReportServer database is in a SQL Server 2008 R2 database server.

    The new ReportServer database will be on a Microsoft SQL Server 2016 database server.

    Can I restore a "Microsoft SQL Server 2008 R2" database to "Microsoft SQL Server 2016"

    Tuesday, March 5, 2019 6:01 PM

Answers

  • Hi,

    You cannot simply upgrade or convert from one Reporting Services server mode to another. Because the two modes use different database schemas, you cannot copy the report server databases between modes.

    To achieve your goal, you could use script to migrate content. Please refer to Native to SharePoint Migration (SSRS).  This approach supports either Native or SharePoint mode as the source or destination servers.


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Tom Stone 98 Thursday, March 7, 2019 9:07 PM
    Thursday, March 7, 2019 1:32 AM

All replies


  • Can I restore a "Microsoft SQL Server 2008 R2" database to "Microsoft SQL Server 2016"

    Yes, you can. However, the opposite (higher to lower) is not possible. You may have to work on a few other things since it's SSRS, such as copying over the encryption key, moving ReportServerTempDB as well etc. 

    https://docs.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-2017#backing-up-and-restoring-the-report-server-databases


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, March 5, 2019 6:30 PM
  • Mohsin,

    The article seems to assume you are moving the report server databases from one production environment to a new production environment.

    We are in test mode, and do want to copy the report server databases from THE production environment to a TEST environment in a new domain.  Then we will test all reports and subscriptions, and maybe a month later remove the old production environment and designate the TEST environment as the new production SSRS environment.

    Based on the above it seems like I need a different set of instructions.

    Tuesday, March 5, 2019 9:46 PM
  • Hi Tom,

    The best way to migrate your subscriptions would be take a full backup of the Reporting Server database and restore on your new SQL Server 2016 instance. You can follow the article posted by Mohsin to do it step by step. Then you can use Reporting Services configuration manager to point the reporting services instance at the database you have restored. 

    And about upgrading your SSRS environment, please refer to Upgrade and Migrate Reporting Services

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, March 6, 2019 9:21 AM
  • Also,  the current production environment is in SharePoint mode,

    and the new production environment will be in native mode.

    Will the specified article still be applicable?

    (sorry about not being a DBA, or having one to rely on)

    Wednesday, March 6, 2019 2:59 PM
  • Hi,

    You cannot simply upgrade or convert from one Reporting Services server mode to another. Because the two modes use different database schemas, you cannot copy the report server databases between modes.

    To achieve your goal, you could use script to migrate content. Please refer to Native to SharePoint Migration (SSRS).  This approach supports either Native or SharePoint mode as the source or destination servers.


    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Tom Stone 98 Thursday, March 7, 2019 9:07 PM
    Thursday, March 7, 2019 1:32 AM