question

Ron 3 avatar image
Ron 3 asked

Moving SQL 2005 to another Server running SQL 2005

WARNING: I am an EXTREME NEWBIE to MS SQL.

I have been assigned a project to move the entire SQL Server over to a new server that will have the same computer name as the current SQL Server.

I found a couple different sites that instruct you to simply stop services and copy over data(Folder's with security/shares+permissions) to the new server. Then take the old server off of the domain, and rename the new server to the name of the old server. Check the name of the new server in SQL using the @@Server command and whola!

Will this work in my situation? Is it really that easy?

This is the environment:

Old Server: Windows Server 2003 32-bit MS SQL 2005 SP3

New Server: Windows Server 2008 64-bit MS SQL 2005 SP3

I have gone through SQL Server Configuration Manager and SQL Server Surface Area Configuration Manager and matched up all of the settings. I have also matched the physical drives to match the existing server, except they are larger on the new server.

What kind of surprises may I see during this move? Should I avoid this plan and use something else like detaching/attaching? Are all of the logins going to work?

Again... I am very new to administering SQL... so excuse my lack of knowledge while writing this question.

sql-server-2005server64-bitwindows-2008
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

Before you do anything else, backup your existing databases and put these backups in a safe location, not on either server. You can't possibly be too paranoid about this. If everything goes south, this is safety valve.

The one area where you are looking at trouble is this step from your description:

copy over data(Folder's with security/shares+permissions) to the new server

It is just not that easy. If I were doing this, what I'd suggest, and this isn't simple, is to copy the data & log files for all the databases, including the master, resource, tempdb & msdb system databases, to the new server after the old one is offline, as your step describes. But then, well, I'd refer to this document from Microsoft on how to do it. It's going to hit a lot more of the details than I will. There is also this one, covering a lot of the same material.

I've only had to do this type of thing personally a couple of times in my career, so I refer back to documentation all I can.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

I presume you are going to move all of the system databases too? master, model, msdb

Not sure what difference you might get moving from 32 to 64-bit, someone else might come in on that one.

The one advantage of detach/attach, or even backup/restore, is that you are taking a closed, finalised version of the database, rather than just a file copy. I know you said you where going to stop the services, but if something isn't quite right with the database file(s), you won't have the option of attaching them, or restoring them onto another server elsewhere.

The other thing to consider is possibly changing the IP of the new server to be the IP of the old server (after having changed the IP of the old server to something else!)

When I've done this in the past, I have planned it down to a step by step approach, complete with a escape plan at every stage!

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered

There are various ways you can do this. But first you need a back-out plan in case things go as not intended. First step is as Grant said Take a backup of your User and System databases.

Personally instead of the attached/detach method I suggest you use backup/restore method. Take a copy of the backup of your servers and restor it on the new server. That way there is no down time and if something goes wrong you still have the old system up and running. Once you restored the databases check all the logins, jobs, linked servers...etc are on the new environment and then run DBCC CHECKDB on all databases and take the backup of the new server and finally when you become comfortable stop the old server and connect your application against the new database.

Good Luck

8 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I agree with you, normally, when migrating servers. But because they're trying to copy the server, not simply migrate it, I go with detach/attach. That's the only reason I advocated it.
0 Likes 0 ·
Ron 3 avatar image Ron 3 commented ·
I think that the backup/restore option is a good option. I will have a pretty large window to work on this move, but I like the idea of being able to bring up the old server if things don't go as planned. The MS Article here http://support.microsoft.com/kb/314546 doesn't go into much detail about the backup/restore method. I will look around on the internet.
0 Likes 0 ·
Ron 3 avatar image Ron 3 commented ·
I found this MSDN article, it looks good. http://msdn.microsoft.com/en-us/library/ms187048.aspx
0 Likes 0 ·
Ron 3 avatar image Ron 3 commented ·
Why would detaching/attaching be better than the backup/restore method?
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
I dont think one is always better than the other. It depends. But it might be fast and easy to detach your data and log file and attach them to the new environment.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.