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?
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.
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.
The databases should (should!) work unmodified. However, for development it is preferable that you have sql2005 to test your scripts unless you assume all your clients would upgrade to 2008 as well, since 2008 has features that do not exist in SQL Server 2005.
In dev and test environments, having multiple database servers installed is not a problem and can reduce the number of test servers required.
In production, I wouldn't recommend it due to the fact that multiple buffer pools fight and kill your performance.
RESTORE DATABASE mydb FROM disk = 'c:\backupfile.bak' WITH MOVE 'maindatafile' to 'c:\newdatalocation.mdf', MOVE 'mainlogfile' to 'c:\newloglocation.ldf'
Hi the command is great however under what context does one execute it.
Meaning using the Master as focus in SQL 2008 management studio using SA credentials.
To me the important thing is will you have prod instances that are 2005 databases? Will you have to support reporting services reports that are on a prod server that only has the 2005 version of reporting servives, etc?
If so, you should have both the 2005 and the 2008 versions on your development machines. I've seen a lot of code that had to be thrown out because developers worked on 2008 when prod was 2005. ALways develop against the version of the software you will have in prod. If you are converting to 2008 but not there in prod yet, you need both, one for maintenance changes and one for future stuff.
Personally I have SQL server 2000, 2005 and 2008 on my machine because we haven't converted everything yet and I have some things which can only be done on the older version. We have found the key to maintaining multiple versions is to install them i nthe correct order. It seems to go badly if you put 2008 on first and then the older versions.
Sometimes you need to be able to test on multiple versions, or you may need 2005 for one thing and 2008 for another.
Sometimes you maintain several different apps, some of which are on one and some on the other, and you haven't updated everything yet. Sometimes you're upgrading, and need to test on both versions during the upgrade. Sometimes you support several different customers, some on one version and some on another. Sometimes you want to upgrade your internal apps, but you're using a software package that is only certified on an older version.
There's lots of reasons.
Please answer these 2 questions: