January 3, 2002 at 6:58 pm
Hi,
We have just ordered a new server for our SQL Server to go on. What I was wondering was: what's the best way to migrate all of our databases from the existing server to the new server quickly and with the minimum of fuss and downtime?
The way I see it we can do one of several things:
1. Use DTS import/export to manually move the databases (this can't be a good way to do it...)
2. Backup the databases and restore them into the new SQL Server on the other machine (sounds better)
3. Some other way I haven't thought of?
Any suggestions would be greatly appreciated.
PS. We're using SQL Server 2000 on both old and new machines. Old machine has NT4 SP6, new one has Win2K SP2.
Owen West
Programmer,
Hunter Area Health Service
Owen West
Programmer,
Hunter New England Health
January 3, 2002 at 7:47 pm
We have upgraded servers a number of times and the quickest way I know is to backup a database and restore it on the new one. Using DTS or the import/export wizard takes longer.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 4, 2002 at 4:38 am
I documented a shortcut I use - I think you'll like it better than backup/restore!
http://www.sqlservercentral.com/columnists/awarren/20010425205439_1.asp
Andy
January 4, 2002 at 6:18 am
Cool! I like your way to move to a migrate from one server to another. It also reminded me of why we didn't try anything like that the last time we migrated from one server to another. We migrated from a non clustered SQL Server to a clustered SQL Server and since we were all new to that the DBA wanted to get the server up and running to ensure we could make a clustered environment work before moving all our databases there and so we had SQL Server up and running before moving our databases there and we simply moved the backups over a period of two or three days. Plus we didn't use the same server name.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 4, 2002 at 10:46 am
I like Andy's method. In the past, when I haven't wanted to keep the same name, I have used BCP out/in which worked amazingly well (used scripts).
My method has usually been backup and restore, but I have detached/attached as well and that worked well.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply