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.

Answer 1

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)

Answer 2

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

Answer 3

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.