locked
Restoring a SQL Server 2005 DB to a SQL Server 2014 install, is there any way to prevent it from being upgraded RRS feed

  • Question

  • Does anyone know if there is a flag I can use to prevent a SQL Server 2005 database from being upgraded to SQL 2014 when it is restored to a 2014 instance?

    Don't ask why, I just need to know if this is possible or if 2005/8 DB's are required to be upgraded if they are going to run on SQL 2012/2014 instances. The documentation seems to say this is so, I just want to verify that.

    Wednesday, April 23, 2014 3:54 PM

Answers

  •  to prevent a SQL Server 2005 database from being upgraded to SQL 2014 when it is restored to a 2014 instance?

    First: You cannot directly restore a 2005 database to 2014. MS supports migration over 3 SQL Server version steps; means you can restore 2005 to 2008/2008R2/2012, but not to 2014. You have to do a migration step between.

    And no. As soon as you restore or attach a older database version to newer SQL Server version the database format will be automatically migrated to the new format; but the database will stay in older version compatibly level.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Wednesday, April 23, 2014 5:34 PM
    • Marked as answer by tracycai Tuesday, April 29, 2014 12:27 PM
    Wednesday, April 23, 2014 4:53 PM
  • No what you are asking is not possible.Moment you restore or attach/restore database  to SQL server 2012( you cannot do it directly) its is upgraded

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by tracycai Tuesday, April 29, 2014 12:27 PM
    Wednesday, April 23, 2014 5:35 PM

All replies

  •  to prevent a SQL Server 2005 database from being upgraded to SQL 2014 when it is restored to a 2014 instance?

    First: You cannot directly restore a 2005 database to 2014. MS supports migration over 3 SQL Server version steps; means you can restore 2005 to 2008/2008R2/2012, but not to 2014. You have to do a migration step between.

    And no. As soon as you restore or attach a older database version to newer SQL Server version the database format will be automatically migrated to the new format; but the database will stay in older version compatibly level.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Wednesday, April 23, 2014 5:34 PM
    • Marked as answer by tracycai Tuesday, April 29, 2014 12:27 PM
    Wednesday, April 23, 2014 4:53 PM
  • No what you are asking is not possible.Moment you restore or attach/restore database  to SQL server 2012( you cannot do it directly) its is upgraded

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by tracycai Tuesday, April 29, 2014 12:27 PM
    Wednesday, April 23, 2014 5:35 PM
  • First: You cannot directly restore a 2005 database to 2014. MS supports migration over 3 SQL Server version steps; means you can restore 2005 to 2008/2008R2/2012, but not to 2014. You have to do a migration step between.

    Olaf I was looking for this today, because I'm working with a new customer that have SQL 2005 and I have SQL 2014. And needed to restore a backup form the production server in my test server.

    When I read what you write I think, oh no!! I have to install SQL 2012 in my server. But that it's not an option for me, and I say let me try to restore the DB first in 2014 and if not work install 2012. You can restore a 2005 database to 2014 directly, and it works. For reference the version number in the servers 2005 (9.0.4035 SP3) and 2014 (12.0.2000 RTM)

    This is not related in any way to the original question that you answered (the file format will be upgraded to the new motor, that is sure).

    I put this lines because in the future there will be some people searching about if possible to restore a 2005 DB to 2014, and like me will read your lines and think it's not possible and that can make a decision to buy 2012 and not 2014 to that new server they will migrate.


    Don't forget to mark the best replies as answers!

    Friday, October 10, 2014 3:50 PM
  • Hi ,

    SQL 2014 Backward compatibility  level is upto SQL 2008.

    Wednesday, November 19, 2014 1:09 PM
  •  to prevent a SQL Server 2005 database from being upgraded to SQL 2014 when it is restored to a 2014 instance?

    First: You cannot directly restore a 2005 database to 2014. MS supports migration over 3 SQL Server version steps; means you can restore 2005 to 2008/2008R2/2012, but not to 2014. You have to do a migration step between.

    And no. As soon as you restore or attach a older database version to newer SQL Server version the database format will be automatically migrated to the new format; but the database will stay in older version compatibly level.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Reopening this old thread for correctness. Above is not true and SQL 2005 can be restored to SQL 2014.

    taken from http://msdn.microsoft.com/en-us/library/ms178513.aspx

    Friday, November 21, 2014 7:04 PM
  • Agree with Chirag here I somehow saw this as 2012, and I pointed same in my answer.

    Thanks for pointing Chirag


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, November 21, 2014 8:17 PM