How to migrate SQL '05 instance (with databases) to existing SQL '08 installation on same server?
Posted on 2013-06-07
One of our clients has a server that is running SQL '05 Express and SQL '08 Express. I've been having a bit of difficulty getting scripted backups to work properly and I'd like to do a little housekeeping before continuing work on the backups by getting everything running on SQL '08.
Currently there is the following setup:
INSTANCE1 (contains several databases)
INSTANCE2 (contains several databases)
INSTANCE1 hosts data for PROGRAM SUITE 1 and INSTANCE2 hosts data for PROGRAM SUITE 2. Importing the databases from INSTANCE1 into INSTANCE2 is a no-brainer--detach the databases from SQL '05 and reattach them in SQL '08, but that will put those databases under INSTANCE2 and I'm concerned that PS1 expects to connect to INSTANCE1 and will not be able to find the databases if I import them into INSTANCE2.
What I'd like to end up with is this:
INSTANCE1 (with databases)
INSTANCE2 (with databases)
I'm confident that PS1 will be able to access its data as long as the instance stays named INSTANCE1, even when it has been moved to SQL '08. However, I am unsure how to go about getting INSTANCE1 into SQL '08. Do I need to:
1) Move the INSTANCE1 databases into INSTANCE2, delete INSTANCE1 from SQL '05, create a new instance in SQL '08 called INSTANCE1, and then move the databases there?
2) Import the entire INSTANCE1 into SQL '08? (Not sure this is even possible.)
3) Something different?
Please advise. Step-by-step instructions would be appreciated. Thanks in advance!