Migration from SQL Server 2005 to SQL Server 2008 R2 question

Question

Migration from SQL Server 2005 to SQL Server 2008 R2 question

APA0876

Hall of Fame

Points: 3853
More actions
November 21, 2012 at 3:14 pm

#285079

Hi,
We will be migrating from SQL Server 2005 to SQL Server 2008 R2 (different physical server), and I'm wondering if I have to migrate the databases via Restore or if I can detach from SQL 2005 copy the Database files to the new server and later Attach the files to the SQL Server 2008 R2 server. The reason I want to do this is because we are planning to install the SQL instance on the new server with the same name of the current instance.
So the plan is:
1- Detach the database on current instance SQL Server 2005,
2- copy the files to the new Server
3- Remove the current server from the DNS entry
4- Install the SQL with the same original name on the new server.
5- Attach Databases on the newly installed SQL Server 2008 R2
6- Update DB compatibility level to 100
Thanks for any contribution to this matter.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

APA0876 (11/21/2012)
Hi,
We will be migrating from SQL Server 2005 to SQL Server 2008 R2 (different physical server), and I'm wondering if I have to migrate the databases via Restore or if I can detach from SQL 2005 copy the Database files to the new server and later Attach the files to the SQL Server 2008 R2 server. The reason I want to do this is because we are planning to install the SQL instance on the new server with the same name of the current instance.
So the plan is:
1- Detach the database on current instance SQL Server 2005,

my recommendation would be definitely back and restore, instead of detach/reattach; especially when migrating to a new server, for example if the sector size on the new server is 4096,and the old server is 512, you get an error and cannot re-attach; but you can always restore in that case.

2- copy the files to the new Server

don't forget to Export usernames/hashed passwords using sp_help_revlogin, mail settings and profile sin msdb, and linked servers in the master database, maybe jobs too. SSIS/DTS packages?

Also any database keys, certificates if they exist.

3- Remove the current server from the DNS entry
4- Install the SQL with the same original name on the new server.
5- Attach Databases on the newly installed SQL Server 2008 R2
6- Update DB compatibility level to 100
Thanks for any contribution to this matter.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APA0876 Hall of Fame Points: 3853 More actions · Answer 2

Hi

Thanks for your prompt response , considering it is the same size is there any other inconvenience regards attach vs restore

Yes I will be considering all the rest you mentioned

APA0876 Hall of Fame Points: 3853 More actions · Answer 3

[/quote]

my recommendation would be definitely back and restore, instead of detach/reattach; especially when migrating to a new server, for example if the sector size on the new server is 4096,and the old server is 512, you get an error and cannot rereattachbut you can always restore in that case.

Thanks, for your response,

If the cluster size is the same, is there any other inconvenience for copy and attach vs Restore on this type of migration?

Thanks again