Any reason to have SQL Server 2005 and 2008 installed on same machine?

Question

I'm setting up a new development server and want to install the latest version of SQL Server 2008 Express.

Will our existing sql2005 databases work with 2008 without modification? If so is there any reason to install both versions on the same server?

Answer 1

I haven't actually tried migrating a 2005 database to 2008, but generally SQL handles this cleanly and without difficulty. The simplest way to do it would be to make a backup of your database from SQL 2005 and then restore that backup with SQL 2008.

If you want to keep the SQL 2005 copy around and online until you know that the 2008 copy is working, you might need to move the data/log files for your database when restoring the backup onto 2008, since the old data files will be in use by 2005. You can do this using the with move option of restore database, for example:

RESTORE DATABASE mydb FROM disk = 'c:\backupfile.bak'
WITH MOVE 'maindatafile' to 'c:\newdatalocation.mdf',
MOVE 'mainlogfile' to 'c:\newloglocation.ldf'

As to having both installed at the same time, one reason you might decide to do this would be to simplify the job of testing code against both versions, if you were intending to have your software support talking to both versions.

Answer 2

You can detatch a 2005 database and attach it to a 2008 server. I would recommend against installing both on the same machine unless you must (e.g. you're writing code for a third party and they only use 2005).

What I'd highly recommend is using windows server 2008 hyper-v to create 2 virtual machines one with the 2005 environment, the other with 2008. Hyper-v virtual machines are incredibly faster than Virtual server 2007.