locked
Sql Server 2005 to 2014 RRS feed

  • Question

  • User-373571878 posted

    Hello,

    This is probably not the place to ask this. But I was wondering if any of you have ever upgraded SQl Server 2005 to SQl Server 2014?

    IF so, I was reading about compatibility settings. There is a chart that shows a value of 90 for SQL Server 2005 and it says it Applies to Sql Server 2008 through 2012.

    Where is 2005 to 2014?   This is new to us.  Can't we just backup the 2005 Sql Database and restore it into the 2014 version?

    Any other situations we need to be concerned about? 

    Thanks!

    Tuesday, November 3, 2015 1:44 PM

Answers

  • User753101303 posted

    Hi,

    According to https://msdn.microsoft.com/en-us/library/ms178513.aspx you can still restore a SQL Server 2005 db to SQL Server 2014 (or 2016) - never tried yet (gone from 2005 to 2012 without any problem, AFAIK 2014 mainly have performance improvments over 2012 with in memory tables and other performance features).

    90 is the compatibility level. It allows to keep some behavior of the old engine when really needed. See https://msdn.microsoft.com/en-us/library/bb510680.aspx for details. I would suggest to NOT use them unless really needed.

    Also you may want to read at least once https://technet.microsoft.com/en-us/library/ms143532(v=sql.110).aspx to spot changes that may affect your app (you also have an upgrade advisor tool by the way though it can't spot anything).

    And then of course check also the "what's new section" to see which features you could take advantage of (though more likely it can wait after the migration).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 3, 2015 2:02 PM
  • User77042963 posted

    You can try. If it does not, you can restore the SQL Server 2005 backup to SQL Server 2008 R2 and you should be able to restore the backup take from SQL Server 2008 R2 to SQL Server 2014. After the migration, you need to check quick bit of settings to make sure you are up to speed with SQL Server 2014. You can find many references online in this topic.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 9:19 AM

All replies

  • User753101303 posted

    Hi,

    According to https://msdn.microsoft.com/en-us/library/ms178513.aspx you can still restore a SQL Server 2005 db to SQL Server 2014 (or 2016) - never tried yet (gone from 2005 to 2012 without any problem, AFAIK 2014 mainly have performance improvments over 2012 with in memory tables and other performance features).

    90 is the compatibility level. It allows to keep some behavior of the old engine when really needed. See https://msdn.microsoft.com/en-us/library/bb510680.aspx for details. I would suggest to NOT use them unless really needed.

    Also you may want to read at least once https://technet.microsoft.com/en-us/library/ms143532(v=sql.110).aspx to spot changes that may affect your app (you also have an upgrade advisor tool by the way though it can't spot anything).

    And then of course check also the "what's new section" to see which features you could take advantage of (though more likely it can wait after the migration).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 3, 2015 2:02 PM
  • User-373571878 posted

    Thanks PatriceSc

    Tuesday, November 3, 2015 2:44 PM
  • User-373571878 posted

    one area that has me a little confused.  Documentation reads:

    "SQL Server 2014 supports upgrade from the following versions of SQL Server

    - SQL Server 2005 SP4 or later" ...

    Are they referring to an edition upgrade from 2005 SP4 to 2014?
    Our SQL Server version is not SP4 or later, If I am restoring to 2014 from a backup taken from our 2005 standard, will this work?

    Tuesday, November 3, 2015 3:17 PM
  • User77042963 posted

    You can try. If it does not, you can restore the SQL Server 2005 backup to SQL Server 2008 R2 and you should be able to restore the backup take from SQL Server 2008 R2 to SQL Server 2014. After the migration, you need to check quick bit of settings to make sure you are up to speed with SQL Server 2014. You can find many references online in this topic.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 4, 2015 9:19 AM
  • User-373571878 posted

    thanks Limno

    Wednesday, November 4, 2015 1:38 PM