none
Convert sqlserver2008 .bak file into sqlserver2005 .bak file

    Question

  • Hi,

          I am working on the project which requires directly conversion of .bak file,created in 2008,and want to convert in 2005 version ,is there any method like conversion in script or anything using procedure and we get .bak file as an output.....One thing is important that script of 2008 is not fully runs on 2005 means some of the tables or default values always get left...so leave a reply keeping these things in mind... waiting for a post.........


    vivek
    • Moved by Bob BeaucheminMVP Monday, November 22, 2010 5:36 PM Moved to a relevent forum (From:.NET Framework inside SQL Server)
    Monday, November 22, 2010 5:35 AM

Answers

  • Hi Vivek,

     

    As far as I know, there are no tools to "convert" the SQL Sever 2008 .bak file to SQL Server 2005 .bak file, actually the only operation on the .bak files we could do is restore, but we cannot restore a SQL server 2008 database on SQL Server 2005.

     

    If your purpose is move the SQL Server 2008 databases to SQL Server2005, then I would like to suggest you to generate the scripts , but just as you mentioned, there might be some problems since some features in SQL Server 2008 are not supported in SQL Server 2005.

     

    Here are the steps to use Generate Scripts:
     

    1.       Run "Generate SQL Server Scripts" wizard in SQL Server Management Studio by right clicking on database and selecting "Tasks –> Generate Scripts."

    2.       On “Choose Objects” panel, Click "Script all objects in selected database", and then click "Next."

    4.       On “Set scripting options”, Click “Advanced”, change script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True".

    5.       Finish the wizard.

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao

    Tuesday, November 23, 2010 10:59 AM

All replies

  • Hi Vivek,

     

    As far as I know, there are no tools to "convert" the SQL Sever 2008 .bak file to SQL Server 2005 .bak file, actually the only operation on the .bak files we could do is restore, but we cannot restore a SQL server 2008 database on SQL Server 2005.

     

    If your purpose is move the SQL Server 2008 databases to SQL Server2005, then I would like to suggest you to generate the scripts , but just as you mentioned, there might be some problems since some features in SQL Server 2008 are not supported in SQL Server 2005.

     

    Here are the steps to use Generate Scripts:
     

    1.       Run "Generate SQL Server Scripts" wizard in SQL Server Management Studio by right clicking on database and selecting "Tasks –> Generate Scripts."

    2.       On “Choose Objects” panel, Click "Script all objects in selected database", and then click "Next."

    4.       On “Set scripting options”, Click “Advanced”, change script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True".

    5.       Finish the wizard.

     

    Please feel free to ask if you have any questions.

     

    Thanks,
    Weilin Qiao

    Tuesday, November 23, 2010 10:59 AM
  • I did it few times by using red gate tools SQL Compare and SQL Data Compare , it took much less time rather than running scripts
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 23, 2010 12:40 PM
  • I did it few times by using red gate tools SQL Compare and SQL Data Compare , it took much less time rather than running scripts
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 23, 2010 12:41 PM
  • MS does not support downgrading any version of SQL Server.  There is no way to "convert" a SQL 2008 backup to SQL 2005.  They are different file formats and have internal differences and feature differences.

    What exactly are you trying to accomplish?  If you just want the tables and data in SQL 2005, you can export from 2008, using many tools, and import to SQL 2005.   If this is going to be an every day issue, you need to upgrade to the same version of SQL Server on both servers.

    Tuesday, November 23, 2010 1:42 PM
  • Hello,

    Microsoft does not allow you to downgrade a database from a higher version to a lower version. This is by design. Check the following link to know Why an SQL Server Database from a higher version cannot be restored onto a lower version?

    Hope that Helps!

    Thanks
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, November 23, 2010 7:09 PM