none
Move a database from a SQL 2005 to SQL 2008 server

    Question

  • I need to move a database from a SQL 2005 to SQL 2008 server. I did this by right clicking on Databases in SSMS and choosing Restore. Do I need to convert the database before I do this? Or is my method above sufficent? Thanks.

    Friday, July 27, 2012 8:16 AM

Answers

  • Hi Kevin,

    Hope you have tested your application properly with SQL 2008 if this is your production environment.

    The simplest thing you can do is detach and attach. You can detach the files from SQL 2005, copy the files to server where 2008 installed and then attach the files to that server.

    Backup and restore method will also work. You do not need to convert anything manually.

    In 2005, default database compatibility mode is 90 and on 2008, its 100. So, if you have tested application with 2008, you can change the compatibility of database to 100 after database is on 2008. This needs to be done manually.


    - Chintak (My Blog)


    Friday, July 27, 2012 8:22 AM

All replies

  • Hi Kevin,

    Hope you have tested your application properly with SQL 2008 if this is your production environment.

    The simplest thing you can do is detach and attach. You can detach the files from SQL 2005, copy the files to server where 2008 installed and then attach the files to that server.

    Backup and restore method will also work. You do not need to convert anything manually.

    In 2005, default database compatibility mode is 90 and on 2008, its 100. So, if you have tested application with 2008, you can change the compatibility of database to 100 after database is on 2008. This needs to be done manually.


    - Chintak (My Blog)


    Friday, July 27, 2012 8:22 AM
  • Take the Fulll Backup From the SQL Server 2005 Server ,copy the Backup set to the SQL Server 2008 machine and restore it,there after change the compatability mode of the database,


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by vr.babu Friday, August 03, 2012 12:13 PM
    Wednesday, August 01, 2012 11:13 AM
  • Kelvin,

    Please refer this post http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/4abf91a7-f5e8-438b-8b56-2daeb4482dde


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 01, 2012 11:31 AM