SQL Migration

[qsac]

I have an old SQL server that I am migrating to a new server. There are about 100 databases with lots of users and tasks.

So i am thinking of doing the following, tell me if anyone has done this? Or if there are any risks?

Exact same installed version of SQL on both servers. Exact same installation paths. Exact same partitions.

Once that is done, I plan on stopping SQL on both machines than copying all datafiles, including system databases, to the new machine. Than start SQL up.

I have done this in a test environment, with a lot less DB's and users, and it works fine.

Any commnets on this is appreciated.

Thanks,
Q

 

[ptheriault]

Will the new server have the same name as the old server?
If so that is a good approach. If not you are going to run into some problems.


- Paul
- Database performance looks fine, it must be the Network!

 

[mrdenny]

Your solution will work fine. I've done it in production several times.

If you will be renaming the new machine to the same name as the old machine you are done at that point.

If you are not going to rename the machine there are a couple of things to do.

1. Use sp_dropserver and sp_addserver to tell SQL about it's new name.

2. Update the sysjobs table with the new server name in the originating_server field otherwise the jobs won't run.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com