SINC_dmack
asked on
How to upgrade SQL database from '08 to '08 R2?
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!
Aneesh
If you have the installation media, you can use in place upgrade; before you do that make sure that the backups are taken
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/SQL
Pinal Dave (SQL authority.com) uses the Copy Database method
See this blog - http://www.karaszi.com/SQL
Pinal Dave (SQL authority.com) uses the Copy Database method
Hi,
There are some ways to help you migrate a database from old version to newer version
1- Backup/ Restore
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,
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-- 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_USER2- 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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.