Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to upgrade SQL database from '08 to '08 R2?

Posted on 2014-12-10
5
Medium Priority
?
73 Views
Last Modified: 2014-12-16
A client has a Windows '03 Server with SQL '05, '08 and '08 R2 installed on it   All are the Express version.  I've got a database instance in '08 and I'd like to migrate it to '08 R2.  I feel like this is something that's pretty easy and straightforward but I can't remember how to do it (if it was hard, I would have documented it the last time I did it) and Googling returns a bunch of "how to upgrade SQL '08" responses but nothing on how to migrate an instance to a newer version of SQL on the same server.  Thanks!
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
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40492808
If you have the installation  media, you can use in place upgrade; before you do that make sure that the backups are taken
0
 
LVL 18

Expert Comment

by:Simon
ID: 40492836
I normally use backup then restore (to the new instance), then transfer any necessary logins that don't exist on the new instance.

See this blog - http://www.karaszi.com/SQLServer/info_moving_database.asp for further details.

Pinal Dave (SQL authority.com) uses the Copy Database method
0
 
LVL 8

Expert Comment

by:Dung Dinh
ID: 40493298
Hi,

There are some ways to help you migrate a database from old version to newer version
1- Backup/ Restore
BACKUP DATABASE Test TO DISK='D:Backup\Test.bak' WITH COMPRESSION
GO

Open in new window

After you backup the database successfully, you could restore to newer instance on your server
-- List of files
RESTORE FILELISTONLY FROM DISK='D:\Backup\Test.bak'
GO
RESTORE DATABASE Test FROM DISK='D:\Backup\Test.bak' WITH
MOVE 'Test' TO 'D:\Data\TestNew.mdf',
MOVE 'Test_log' TO  'D:\Log\TestNew_log.ldf'
GO
ALTER DATABASE Test SET MULTI_USER

Open in new window


2- Use Copy database feature as above link Pinal Dave

Beside, you should make sure that Users of security model are added to newer instance.


Thanks,
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40493923
If you only want to migrate that single database and being in the same server of the SQL Server 2008R2 instance you just need to detach it from SQL Server 2008 and attach it in the SQL Server 2008R2 instance. You don't even need to change the compatibility level. Simple as that.
0
 

Author Closing Comment

by:SINC_dmack
ID: 40502638
Vitor's answer sparked my memory to what must have been the way I've done it in the past--creating a new instance in the desired version of SQL, detaching the database from its existing instance, and then attaching it to the new database.  (The other answers will presumably work too but they aren't the simple solution that Vitor's is.)

I used this document to create a new instance.  http://dharmendrablogs.blogspot.com/2013/02/to-create-new-sql-server-instance-in.html(Basically re-running the SQL '08 Express R2 client, adding features, and then telling it to create a new instance controlled by Network Service.  The document has a some steps that were not needed in Express which are presumably required in the full version of SQL.)  

I haven't actually done the migration yet as the new instance has a different name than the original instance and I'll have to log into about 15 different workstations to update their client software to connect to the new name.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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 Backup & Restore 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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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