Transfer SQL Server 2008 to new server

A

AMD_Gamer

Fully [H]
Joined
Jan 20, 2002
Messages
18,287
I have a Server 2003 server running SQL Server 2008 and need to transfer it to a new server running Server 2008. What is the easiest way to do this? Can you just export the whole database and copy it to the new machine running another instance of SQL Server 2008?
 
Detatch then move. Or use the copy database function if you can to copy it to the new server. But detatch works also
 
k1pp3r said:
Detatch then move. Or use the copy database function if you can to copy it to the new server. But detatch works also
Click to expand...

^^

yep, literally can detach, then copy and paste to new location.
 
What do you guys think about doing a P2V conversion into a VM and then upgrading to Server 2008 from 2003 instead of doing a clean install? All we have running on this box is Server 2003 and SQL Server 2008 with the databases. I would just do a P2V conversion and call it a day but my boss wants everything upgraded to Server 2008.

Btw...either way the plan is to have this running on a VM with ESXi and a iSCSI Datastore. I read that with large environments it is not advised to run SQL Server in a VM but this is not used very much, only by a few people in the office so i think it will be ok.
 
If this server you wish to upgrade is SQL server, I'd want to move to 64 bit. I'm assuming your current 2003 server is 32 bit? If so, no upgrade path, must be clean install.
 
I spent my day at work researching the SQL Server 2008 installation process and that seems simple enough.

Now for the migration to a new server , I learned that you can do either the backup/restore method or the detach and move. The detach and move method takes the database offline so I cannot do that during work hours so it looks like a backup/restore. I will still make sure nobody is accessing the DB when i do this.

Now is there anything else I need to do to make sure that the clients that access the SQL server will still be able to access everything running on the new server as if we never moved it? What about the name of the server? does that have to remain the same as it is now? All i was going to do is change the IP address on the new server to what the old one is but my boss told me to be careful because some stuff in SQL links to the netbios name of the server? I saw some talk from searching on google that you need to do some things to make sure the paths are the same? this is where I am lost and none of the backup/restore tutorials i looked over cover this for a migration to a new machine?

Also there seems to be about 10 or so different DB's on this server. Is there a way to backup/restore all of them at once instead of doing it for each DB?

So as of now my plan is this:

1) Setup new VM with Server 2008 R2 64bit and make the virtual disk large enough to hold the database with room to grow.
2) Install SQL Server 2008 to this VM. Simple stuff
3) Do the backup of each DB on the current SQL Server box and store it some place.
4) Conduct the restore of these DB's on the new SQL server
5) shutdown old SQL Server.

Any help would be greatly appreciated.
 
Last edited:
I always do the backup restore but just be aware that it will still need to be "offline" as any changes to the database after the backup will not be moved to the new server.
 
AMD_Gamer said:
Now for the migration to a new server , I learned that you can do either the backup/restore method or the detach and move. The detach and move method takes the database offline so I cannot do that during work hours so it looks like a backup/restore. I will still make sure nobody is accessing the DB when i do this..
Click to expand...

Backup/Restore will still take it down. You'll shut down the database..so nobody will be accessing it...because it will shut off. Their client will error soon as you shut her down.
 
YeOldeStonecat said:
Backup/Restore will still take it down. You'll shut down the database..so nobody will be accessing it...because it will shut off. Their client will error soon as you shut her down.
Click to expand...

yup, check how the ODBC links are setup I expect they are using hostname so you will need to replicate that host name on the new server or go around and change all the ODBC links ( we use GPO to setup our ODBC so you may want to check that as well)

You can't do an SQL migration without having down time, you really don't want users entering new data onto the old server after you have moved the backup or you will in effect roll them back when you online the new server.

This is why time limited SQL migration are so stressful, you are given 24 hours to migrate the databases and if you don't do it in time you have to start again from scratch.
 
Jay_oasis said:
yup, check how the ODBC links are setup I expect they are using hostname so you will need to replicate that host name on the new server or go around and change all the ODBC links ( we use GPO to setup our ODBC so you may want to check that as well)

You can't do an SQL migration without having down time, you really don't want users entering new data onto the old server after you have moved the backup or you will in effect roll them back when you online the new server.

This is why time limited SQL migration are so stressful, you are given 24 hours to migrate the databases and if you don't do it in time you have to start again from scratch.
Click to expand...

is it ok to use the same hostname on a new server? I know not to do it with a Domain Controller.
 
Jay_oasis said:
yes as long as you remove the old server before you do it.
Click to expand...

So with the same hostname. IP address, and file structure (from installation) I should be ok and not affect the clients that connect to the sql server?

Is that even the best practice for doing this? The tutorials and guides i find just say to do the backup/restore to the new server and no other problems you could run into like the name problem you mentioned?
 
NetJunkie said:
I don't know anything about your config or database workload...so no idea. :)
Click to expand...

Just SQL server 2008 running on Server 2003. We have a few people in the office that connect to this using access and some other program. Nothing major or too complicated. I need to move it to a Server 2008 VM running on ESXi. See my post above for questions about this.
 
Last edited:
You must log in or register to reply here.
Back
Top