none
Downgrade Database from Sql Server 2014 to Sql Server 2008

    Question

  • hello Every One....

    I need to downgrade the database from SQL Server 2014 to SQL Server 2008.The database is having lots of tables and data in it. So i think creating script and taking backup from the created script will loose some data.

    So can you please guide me some other way or any tool which will downgrade the Big Size database from SQL Server 2014 to SQLServer 2008.

    Thank You

    Regards

    Monday, December 5, 2016 8:04 AM

All replies

  • There is a great tool named SQLCompare and SQLDataCompare from www.redgate.com

    If that is not an option, you can script out all objects on SQL Server 2014 database and run on SQL Server 2008, now that all objects have been created  you can generate a script of the data and run on the destination

    http://stackoverflow.com/questions/9429902/generate-script-in-sql-server-management-studio


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 5, 2016 8:59 AM
  • The size of the database is around 500 MB, so is it recommended to take the  backup through script or i should try using the above said tools.

    Thank You

    Monday, December 5, 2016 10:38 AM
  • You cannot using restore the backup 

    1) Use tools, I mentioned above

    2) Script out the objects + data and run on the destination serber


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 5, 2016 10:51 AM
  • SQL Server does not support "downgrading" a database.  As Uri has said, you would have to export all the data from SQL 2014 and import all the data into SQL 2008.

    Why are you doing this?  SQL 2008 is not longer supported.  You should go forward with SQL 2014, not backward.

    Monday, December 5, 2016 2:07 PM
  • hello Every One....

    I need to downgrade the database from SQL Server 2014 to SQL Server 2008.The database is having lots of tables and data in it. So i think creating script and taking backup from the created script will loose some data.

    So can you please guide me some other way or any tool which will downgrade the Big Size database from SQL Server 2014 to SQLServer 2008.

    Thank You

    Regards

    Hi:

    1) Here in the case the backup and restore will not work

    2) Export and Import will do for data only

    3) Best option script out every with data and indexes and execute the same on destination server

    4) After the above of step 3, if any issues needs to resolve

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Monday, December 5, 2016 2:28 PM
  • A wizard driven way to do this is to replicate the database from SQL 2014 to SQL 2008. This will replicate all objects except database users, the rowversion datatype and clr objects. Use transactional replication for this.
    Monday, December 5, 2016 3:26 PM
  • Why are you doing this?  SQL 2008 is not longer supported.  You should go forward with SQL 2014, not backward.

    To be exact, SQL 2008 is still in extended support.

    No matter what we think of it, there are still many sites out there using SQL 2012 - I guess the change in licensing model with SQL 2012 holds many back from an upgrade.

    Monday, December 5, 2016 10:40 PM
  • I know places still running SQL 2000 because they don't want to change their complicated DTS to SSIS.  Even SSAS 2000.

    I don't recommend running versions not in "mainstream support". You are just delaying the pain and making it worse in the long run.  Also, there are very few "breaking changes" between SQL 2008 and SQL 2016.  The upgrade is generally easy.


    Tuesday, December 6, 2016 1:11 PM
  • I don't recommend running versions not in "mainstream support". You are just delaying the pain and making it worse in the long run.  Also, there are very few "breaking changes" between SQL 2008 and SQL 2016.  The upgrade is generally easy.

    As long you are not the one paying the license bills!

    Tuesday, December 6, 2016 10:29 PM