none
Not able to restore sql2016 db to sql2012.

    Question

  • Hi,

    I want to restore a database from sql server 2016 to sql server 2012. I have changed compatibility level to sql server 2012 (110) while taking db backup from slq server 2016. When i tried to restore db in sql 2012 which i had taken from sql 2016 it says "No backup set selected to be restored".  Could someone help me out on this issue.

    Note: I don't want to generate scripts from sql 2016 and do it in sql 2012.


    Tuesday, September 12, 2017 6:26 AM

Answers

  • Hi Rangarao Darla,

    >>compatibility means, it should support for lower versions as well as for higher version right..?

    Nope, it’s mainly for back-ward compatibility, which allows your code to use deprecated/changed features on a higher version of SQL Server without make any change. For more information, please refer to this blog.

    The real blocker here is the internal database version(which is not related to compatibility level). That is why you cannot restore a database from SQL Server 2016 to SQL Server 2012 as the database version is higher than expected and there’s no way to reverse it.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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 Rangarao Darla Tuesday, September 19, 2017 7:33 AM
    Tuesday, September 19, 2017 7:05 AM
    Moderator

All replies

  • Hi,

    I want to restore a database from sql server 2016 to sql server 2012. I have changed compatibility level to sql server 2012 (110) while taking db backup from slq server 2016. When i tried to restore db in sql 2012 which i had taken from sql 2016 it says "No backup set selected to be restored".  Could someone help me out on this issue.

    Note: I don't want to generate scripts from sql 2016 and do it in sql 2012.

    Tuesday, September 12, 2017 6:27 AM
  • I want to restore a database from sql server 2016 to sql server 2012.

    That is not possible, database & backup file are not backward compatible, you can not restore 2016 backups to an older SQL Server Version like 2012.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 12, 2017 6:46 AM
  • Then why did they give compatibility levels in slq server.  

    compatibility means, it should support for lower versions as well as for higher version right..?


    Tuesday, September 12, 2017 7:00 AM
  • Hi Rangarao Darla,

    >>compatibility means, it should support for lower versions as well as for higher version right..?

    Nope, it’s mainly for back-ward compatibility, which allows your code to use deprecated/changed features on a higher version of SQL Server without make any change. For more information, please refer to this blog.

    The real blocker here is the internal database version(which is not related to compatibility level). That is why you cannot restore a database from SQL Server 2016 to SQL Server 2012 as the database version is higher than expected and there’s no way to reverse it.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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 Rangarao Darla Tuesday, September 19, 2017 7:33 AM
    Tuesday, September 19, 2017 7:05 AM
    Moderator
  • You cannot do this using the backup file. The option you have is to create empty DB using script of database and then populate the data with the help of scripts of Import/Export wizard.


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Tuesday, September 19, 2017 7:27 AM