RE: Moving SQL Server 2000 to a new machine

From: dwh2200 (dwh2200_at_discussions.microsoft.com)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 14:03:03 -0700

In addition to all the CYA advise, you CAN move the system databases. Take
these measure to heart:

1. Make sure the physical paths for your system databases is the same for
both servers. Whatever they are on the old box, mimic those paths exactly on
the new one when you're installing SQL Server.
2. When building the new box, only upgrade to SP2 until you've migrated
your databases. Only AFTER my databases came online would I be continue to
upgrade to SP3.
3. Once you've got the new server at SQL2K, SP2, and the physical paths to
your system files match exactly on both servers, you can take both sql
servers offline, do a flatfile copy of your old master .MDF and .LDF files
from the old box over the files on your new box (after you've protected them
by copying them to a safe, unrelated location, please - we're professionals).
 
4. Restart the service on the new box. (You may notice some suspect
databases pop up in Enterprise Manager. It's ok.)
5. Assuming everything comes up ok (see Jason's submission), you can mimic
these steps with the other databases, taking flatfile copies from one box to
the new server with all physical paths to the flatfiles matching.
6. You'll have to address two issues you create when doing this. One is
that the master database is out of synch with what should be the default
instance name (assuming your using default instance names - it's a problem
regardless). You'll need to run sp_drop_server and sp_add_server (@local =
'local') to get the right server listed as the local server (see Books
Online). You'll also need to update the [originating_server] fiels in the
sysjobs table in MSDB. Update the field to match the local instance name you
used with the sp_add_server.

7. Once your databases (at the very least, the system databases) have come
online, then I'd backup up everything and complete the upgrade to SP3, and
backup everything again.

"Scott Yu" wrote:

> Hello all,
>
> Recently I was tasked to move our entire SQL Server 2000 to a new server. I
> work as a programmer so I have limited knowledge working with SQL Server, but
> not enough to handle the task I been given without direction and assistance.
> This is where you people come in. THANKS IN ADVANCE!!!!
>
> Currently our SQL Server 2000 SP2 is running on a NT 4.0 box. The target
> server will be running 2003 and we'll need to install SQL Server 2000 service
> pack 3. I was wondering if anyone experience the same task and if so, how
> you went about moving the entire SQL Server 2000 to the new server? What
> problems did you encounter and how you resolved it? Will it be possible to
> copy over the system databases from the source SQL Server to the target SQL
> Server under a new operating system and service pack and still work? Or will
> I have to script out all the system level components and rebuild it on the
> target SQL Server and then restore the user databases?
>
> I have to make sure all of our DTS packages, management services, database
> maintenance plans, replication and link servers are counted for and in
> working order. God help me.
>
> Any help or links would be greatly appreciated.
>
> Thank you very much.
>

Relevant Pages

  • Re: How to move system databases to new SAN drive in cluster serve
    ... I have moved model, msdb, and tempdb on a cluster ... Moving SQL Server databases to a new location with Detach/Attach ...
    (microsoft.public.sqlserver.clustering)
  • Re: Migrate stand alone sql server to a cluster without downtime
    ... Use backup and restore to create the databases on the new system. ... Run a log backup WITH STANDBY on each database on the old server. ... I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.clustering)
  • Re: SPS 2003 Portal change SQL server
    ... select portal site to delete and choose to NOT delete the databases ... server farm, you can just copy them over. ... attach the databases to the new sql server. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)
  • Re: Moving databases to a new server
    ... Moving Databases between Servers ... Disaster Recovery Articles for SQL Server ... I am leaning toward the detach and> attach method but I want to make sure there are not any hidden problems with> this method. ...
    (microsoft.public.sqlserver.setup)