RE: Moving SQL Server 2000 to a new machine

From: Scott Yu (ScottYu_at_discussions.microsoft.com)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 10:59:02 -0700

Hello, thank you very much for taking your time to respond to my post.

Currently I do have a test domain which is isolated from our production
domain. The 2003 servers running SQL Server 2000 will be name the same as
their old NT 4.0 SQL Server 2000 counter parts. The new servers will also
mirror the drive letters and directory structure. Currently all system
databases and selected user databases on the production servers are being
backup on a daily basis by a maintanence job.

After reading your reply I came up with few questions.

> 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.
#########################################
Microsoft states
(http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp)
"Windows Server 2003 requires SQL Server 2000 Service Pack 3 or later to be
appliced". Being the case, is it still possible to do what you mentioned in
step 2? I was thinking updating NT4.0 with SP3 first to resolve this issue.
Do you know of any issues NT4.0 might have with SQL Server 2000 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).
##########################################
Instead of disattaching the .mdf and .ldf files, can I just restore it from
a current complete backup?

> 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.
#########################################
If I name my test machine the same name as my old production servers in the
test domain, can I skip step 6?

Once again, thank you very much for your time and knowledge.
Sincerely
Scott Yu

Relevant Pages

  • Re: Developer design Best Practices
    ... multiple smaller databases that were created adhoc on those servers for the ... SQL on these machines and than installed their application and pointed it ... production machines. ... Many of the objects in these databases are owned by not ...
    (microsoft.public.sqlserver.server)
  • ora 9.i asp.net cannot connect
    ... On all servers we have an website that connects to 4 ... different databases. ... When I use "test2" it doesn't work on any of the servers. ... I was able to get "Production" running after a lot of uninstalling ...
    (comp.databases.oracle.server)
  • Re: Different Domains in the Same Subnet
    ... If you have trust between domains and your users need to access also the test domain, same subnet is easy. ... But make sure that you do not have DHCP servers in both subnets which provide different DNS servers and Default gateways, then you will run in conflicts. ... We have a production and test domain, but users who are in production ...
    (microsoft.public.windows.server.active_directory)
  • Re: DC fails to authenticate when trusted DCs unavailable?
    ... They do use DNS servers from only their own domain, ... To complicate matters, our DCs are also our DNS servers, and the DNS ... e.g. no corporate DCs in the production site. ...
    (microsoft.public.windows.server.networking)
  • Re: SQL active - active cluster hardware design?
    ... if you can find TPC-C benchmarks to be better on Itanium, ... For x64 vs IA-64, as I said it depends a lot on how the system vendors make ... x64 servers may be sufficient. ... You will need to know number of users and databases. ...
    (microsoft.public.sqlserver.clustering)