Move SQL server from 2005 to 2008 R2

Question

I'm planning on moving a couple of our databases from 2005 standard to 2008 R2 Express. Can I just detach the DB's move them over to the new server and reattache them? Is there anything I should do different, i.e. import or something so that the DB file gets upgraded or anything? Or will this be sufficient?

Thanks.

Answer 1

Assuming you're not using any features that are not available with Express you should be fine. The database will get upgraded during the reattach process. Note, though, that the database compatability will still be set to 2005 (90). You'll need to manually change that in order to use some of the new features of 2008 R2. It won't be relevant for a lot of features as this is Express, but will still hold true for the common features such as the new data types.

To change the compatability level:

• Right-click your database in SQL Server Management Studio and get Properties
• Click the Options item in the Select a Page menu
• Use the Compatability drop-down menu to set the database to the appropriate level

Or

• Open a query window in SQL Server Management Studio and execute the following statement

  ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 100

The different compatability levels are:

• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008 (and 2008 R2)