Downgrade sql 2017 to 2014

  • pols

    SSC Eights!

    Points: 861

    Hi,

    I got a requirement to downgrade Standard 2017 to SQL 2014.

    What is the procedure to downgrade?

     

    Thanks,

    Jo

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    may we ask the reason for downgrading ? - could it be something settled by a compatibility mode setting?

    as far as I can see - a downgrade is a complete uninstall and re-install.. but there are other issues. you databases will be set to run on 2017 and if this is anything like when we moved from 2000 to 2005 then you can't restore to an older version (the database version has been upgraded)

    the only thing i can see (if you absolutely must downgrade) is to build a new server and use a data transfer tool to move the data to the new server... but again, why?

     

     

    MVDBA

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    MVDBA (Mike Vessey) wrote:

    may we ask the reason for downgrading ? - could it be something settled by a compatibility mode setting?

    as far as I can see - a downgrade is a complete uninstall and re-install.. but there are other issues. you databases will be set to run on 2017 and if this is anything like when we moved from 2000 to 2005 then you can't restore to an older version (the database version has been upgraded)

    the only thing i can see (if you absolutely must downgrade) is to build a new server and use a data transfer tool to move the data to the new server... but again, why?

    I must confess that I'm very curious here, why the downgrade requirement?

    😎

    Is it licensing, 3rd party requirements etc?

  • Nice Marmot

    SSCrazy

    Points: 2278

    I wonder if a .BACPAC export would be a possibility? According to https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/dac-support-for-sql-server-objects-and-versions?view=sql-server-2017 there are limitations on data types etc. The page says "A DAC package or export file built with one version of the DAC Framework cannot be processed by an earlier version of the DAC Framework." so you would presumably need to use the same version on both instances for export and import. You'd need to try it in a test environment and see if it works. Database size could be an issue.

    HTH

    PS  This would only work with user databases. You would need to recreate logins https://support.microsoft.com/en-gb/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server and jobs etc. by scripting them out.

    • This reply was modified 4 months, 3 weeks ago by  Nice Marmot.
  • Joie Andrew

    One Orange Chip

    Points: 27295

    You cannot do an in-place downgrade, you'll have to:

    •  Install a new SQL Server 2014 instance
    • Script out the databases you want to bring from 2017 to 2014 (with something like SSMS generate scripts)
    • Execute the scripts on your 2014 instance
    • Recreate any logins/jobs/alerts/etc.

    One thing to keep in mind is that SQL Server 2014 is no longer in mainstream support.

    Joie Andrew
    "Since 1982"

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply