Moving everything on an SQL instance to new hardware

Question

Moving everything on an SQL instance to new hardware

Jeff Moden

SSC Guru

Points: 1004409
More actions
May 21, 2013 at 11:50 am

#293552

We're setting up to move everything from a 32 bit machine to a 64 bit machine. I'm still researching the Web to do this but not much luck yet. I have questions like "Can I just copy the MSDB.MDF/LDF and MASTER.MDF/LDF databases from the old machine to the new machine and everything will be wonderful?"
Of course, that question presumes that I'm also going to detach all user databases before trying to copy the MSDB and MASTER MDF/LDF files. After that, I'd copy MDF/LDF files for the user databases to the new machine an attach them.
If someone has moved an entire instance from an old machine to a new machine, I'd sure appreciate any tips you might have to keep things simple.
Thanks, folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Jeff I've only file copied system files a couple of times when building backup machines; i know that the new server has to be the exact same version, ie you can copy if 2008 RTM to 2008 RTM, but you cannot file copy if the new server has the latest patches(Not sure on cumulative updates) , but the original server has a different set, or is behind on a service pack update or anything;

other than that, when things like the model database got mangled by a developer running scripts a couple of times on the Dev Sandbox, i had to search for another machine on the same service pack and copied it in that situation a couple of times.

I've built and brought up a lot more servers by simply installing patching, and then migrating via restores, sp_helprev_login,and scripting linked servers and database mail;

we sometimes script out the scheduled jobs, but it's usually just as easy to build a new plan with the scripts from ola.hallengren.com

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!

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

Lowell (5/21/2013)
Jeff I've only file copied system files a couple of times when building backup machines; i know that the new server has to be the exact same version, ie you can copy if 2008 RTM to 2008 RTM, but you cannot file copy if the new server has the latest patches(Not sure on cumulative updates) , but the original server has a different set, or is behind on a service pack update or anything;
other than that, when things like the model database got mangled by a developer running scripts a couple of times on the Dev Sandbox, i had to search for another machine on the same service pack and copied it in that situation a couple of times.
I've built and brought up a lot more servers by simply installing patching, and then migrating via restores, sp_helprev_login,and scripting linked servers and database mail;
we sometimes script out the scheduled jobs, but it's usually just as easy to build a new plan with the scripts from ola.hallengren.com

Thanks, Lowell.

Yep. Understood on the "exact version" thing but I sure do appreciate the reminder. Do you suppose copying the MSDB and MASTER MDF/LDF files would work for jobs/privs, etc? And, yeah... we even have the same drive/path names for those.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Jeff Moden (5/21/2013)
Lowell (5/21/2013)
Jeff I've only file copied system files a couple of times when building backup machines; i know that the new server has to be the exact same version, ie you can copy if 2008 RTM to 2008 RTM, but you cannot file copy if the new server has the latest patches(Not sure on cumulative updates) , but the original server has a different set, or is behind on a service pack update or anything;
other than that, when things like the model database got mangled by a developer running scripts a couple of times on the Dev Sandbox, i had to search for another machine on the same service pack and copied it in that situation a couple of times.
I've built and brought up a lot more servers by simply installing patching, and then migrating via restores, sp_helprev_login,and scripting linked servers and database mail;
we sometimes script out the scheduled jobs, but it's usually just as easy to build a new plan with the scripts from ola.hallengren.com
Thanks, Lowell.
Yep. Understood on the "exact version" thing but I sure do appreciate the reminder. Do you suppose copying the MSDB and MASTER MDF/LDF files would work for jobs/privs, etc? And, yeah... we even have the same drive/path names for those.

Install SQL Server, make sure it's the exact same patch level and system databases are the exact same path, turn off SQL Server, copy over your files, including user DB files and system DB files, reboot.

Test first on a dev box.

I've done this a few times. It can get tricky, but usually isn't. One time, I had to edit the registry to get it all to work. I don't remember what needed to be done there, but it was no big deal and obvious that it needed to be done when error messages popped.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

GSquared (5/21/2013)
Jeff Moden (5/21/2013)
Lowell (5/21/2013)
Jeff I've only file copied system files a couple of times when building backup machines; i know that the new server has to be the exact same version, ie you can copy if 2008 RTM to 2008 RTM, but you cannot file copy if the new server has the latest patches(Not sure on cumulative updates) , but the original server has a different set, or is behind on a service pack update or anything;
other than that, when things like the model database got mangled by a developer running scripts a couple of times on the Dev Sandbox, i had to search for another machine on the same service pack and copied it in that situation a couple of times.
I've built and brought up a lot more servers by simply installing patching, and then migrating via restores, sp_helprev_login,and scripting linked servers and database mail;
we sometimes script out the scheduled jobs, but it's usually just as easy to build a new plan with the scripts from ola.hallengren.com
Thanks, Lowell.
Yep. Understood on the "exact version" thing but I sure do appreciate the reminder. Do you suppose copying the MSDB and MASTER MDF/LDF files would work for jobs/privs, etc? And, yeah... we even have the same drive/path names for those.
Install SQL Server, make sure it's the exact same patch level and system databases are the exact same path, turn off SQL Server, copy over your files, including user DB files and system DB files, reboot.
Test first on a dev box.
I've done this a few times. It can get tricky, but usually isn't. One time, I had to edit the registry to get it all to work. I don't remember what needed to be done there, but it was no big deal and obvious that it needed to be done when error messages popped.

Very cool. Confirms pretty much what we're doing. We also have extra steps concerning IP addresses but those weren't a concern.

Thank you both!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 5

the file format is the same for 32bit and 64bit so you can copy from one to the other.

Is the new machine name going to be the same? I always prefer to script out system objects and recreate them that way.

Maintenance plans definitely do not transfer to a new server name, they will still point to the old server, there are other issues like SSIS depending how the packages are written and service broker enabling for msdb.

If you do copy all files over, as long as all file paths are the same you don't need to detach the user databases first, copy the database files over, when you restart sql with the copied over system database files the user databases will 'magically' appear.

---------------------------------------------------------------------

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

If the server name is different you will need to drop and add the new server name.

Even if it is the same server name run select @@servername after you do this to make sure it matches the real server name running SQL Server.