[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to migrate SQL '05 instance (with databases) to existing SQL '08 installation on same server?

Posted on 2013-06-07
8
Medium Priority
?
426 Views
Last Modified: 2013-06-10
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:
SQL '05
INSTANCE1 (contains several databases)

SQL '08
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:
SQL '05
No instances

SQL '08
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!
0
Comment
Question by:SINC_dmack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39231012
I would, personally, just setup an alias for the instance: http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

Unless you really want to keep it in a separate instance?
0
 
LVL 80

Expert Comment

by:arnold
ID: 39231017
How wide is your transition window?
You could use the GUI SSMSE to backup the db on sql2005 and restore on the sql2008.
There are the login export script

http://support.microsoft.com/kb/918992

This way you will have a fall back.  There powershell cmdlets that can be used to backup/restore DBs. Not sure you need it. IMHO, a transition has to be manually done a db/application at a time which provides for a straight forward way to verify the transition works. Rather than migrating all and then if trouble comes up, roll back everything rather than a single item that may have missed...
0
 
LVL 13

Accepted Solution

by:
Mark Damen earned 1000 total points
ID: 39232209
You should just be able to upgrade the instance as an in place upgrade.  I did this recently for a SQL 2005 instance.  Test using lab environment or take backups before upgrading as per Normal advice.

When you launch the 2008 express setup and choose upgrade, it will ask which instance.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:SINC_dmack
ID: 39233224
markusdamenous, I don't think I can do an inplace upgrade since SQL '08 is already installed.  When I try to run the SQL '08 Express installer, it says that all of the components are already installed and it won't let me continue as there is nothing new selected.  

didnthaveaname, I am not positive that I need to keep it in a separate instance, but I think that might be preferable since the existing SQL '08 instance has databases for a different program.  I am going to try the alias but have the alias point to a separate SQL '08 instance for the migrated databases.

arnold, backing up the databases in SQL '05 and then restoring them in SQL '08 will still leave me with the databases in an instance that is named differently than it is in SQL '05.  That won't help.  Please clarify, in case I misunderstood.

Thanks!
0
 
LVL 80

Assisted Solution

by:arnold
arnold earned 1000 total points
ID: 39233265
You can do an inplace upgrade of an instance.  Use advanced settings to get a listing of the existing instances on the system and then upgrade the 2005 instance.
You can have multiple instances installed.
/ /sqlexpress /test

You will have a single binary c:\program files\microsoft sql
but the data files for different instances can be where you want
mssql.x
0
 
LVL 1

Author Comment

by:SINC_dmack
ID: 39233279
A brief update--the existing SQL '08 installation is actually R2.  Just realized that.
0
 
LVL 80

Expert Comment

by:arnold
ID: 39233301
IT does not matter.  When you run the install of sql 2008 express, there is an option to select an existing instance using advanced settings.

Are you certain that your applications will support access to sql 2008? Consider that if you upgrade and the applications fail, you will be in a difficult situation.

Using a new instance provides for incompatibilities that were not thought of resolved by reverting the configuration of the application to point back to the old instance.
0
 
LVL 1

Author Comment

by:SINC_dmack
ID: 39233403
Well, that was so easy, I feel silly for having asked in the first place.  I downloaded SQL '08 R2 Express.  At the first prompt, I selected "Upgrade from SQL 2000, 2005 or 2008".  The second prompt asked me what instance I wanted to upgrade.  Sure enough, INSTANCE1 was listed, so I upgraded it.  It completed successfully.  

I had previously tried SQL '08 Express (non R2) and gone through the same prompts but the spot where I could select an instance to upgrade was greyed out.  Not sure why it was available in the R2 installer but not regular '08.

Now I just have to wait until tomorrow morning to see if the client's software can still connect to the database successfully.  Thanks everyone!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question