none
Restore SQL 2005 Backup in SQL 2008 R2

    Question

  • Hello,

    I have all my production data in SQL Server 2005 and we want to move to SQL 2008 R2.
    So if I backup the SQL 2005 DB and restore in SQL 2008 R2 will those DB will be upgraded to SQL 2008 R2?
    Or what is the best way to upgrade to 2008 R2?

    I think upgade the SQL 2005 instance will be good option, but the SQL box is on Win 2003 and now the MS does not support Win 2003. SO I am planing to get a new server with Win 2008 R2 and will install SQL 2008 R2 and want to restore/upgrade my SQL 2005 DB backups.

    Please advise.

    Thanks,
    Prabhat

    Friday, March 25, 2011 6:51 PM

Answers

  • Hi,You could backup the database and restore it to SQL 2008 R2 or detach database in SQL 2005 copy files to SQL 2008 R2 server and attach them.


    When you backup the database from SQL 2005 and restore  or use  attach detach method to SQL 2008 R2 your database version number will be upgrated, I think its 661 in 2008 R2.

    After the backup and restore I will rebuild fragmented indexes and/or update stats and run DBCC CHECKDB. Plus your app will have to update their connections strings etc.

    Thanks



     

    • Proposed as answer by Ahmed.Tobgy Saturday, March 26, 2011 5:32 PM
    • Marked as answer by Prabhat_IE Tuesday, March 29, 2011 5:18 PM
    Saturday, March 26, 2011 12:17 AM

All replies

  • Hi,You could backup the database and restore it to SQL 2008 R2 or detach database in SQL 2005 copy files to SQL 2008 R2 server and attach them.


    When you backup the database from SQL 2005 and restore  or use  attach detach method to SQL 2008 R2 your database version number will be upgrated, I think its 661 in 2008 R2.

    After the backup and restore I will rebuild fragmented indexes and/or update stats and run DBCC CHECKDB. Plus your app will have to update their connections strings etc.

    Thanks



     

    • Proposed as answer by Ahmed.Tobgy Saturday, March 26, 2011 5:32 PM
    • Marked as answer by Prabhat_IE Tuesday, March 29, 2011 5:18 PM
    Saturday, March 26, 2011 12:17 AM
  • Thanks for your reply jr81 :).
    I will try this.

    Thanks.
    Tuesday, March 29, 2011 5:19 PM
  • Dear Prabhat,

    This is Rahul. I want to do same activity i.e., Restore SQL 2005 Backup in SQL 2008 R2 on Win 2008 R2 std 64-bit. So you are did this activity successfully. If you are did this activity, Please share me what is the steps you did.

    Friday, March 21, 2014 6:39 AM