Re: Change server, maintain the users.....

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/07/04 

Date: Mon, 8 Mar 2004 02:16:49 +0530

Hi,

Best and Fast approach to do this is ,

1. Create the same directory structure (for mdf and ldf) in the new server
(Same as old server)
2. Stop sql server in both servers.
3. Copy all the MDF files and LDF files (Including System databases) to new
server
4. Start the new SQL Server and login to Query analyzer (SA password will be
same as old server)
5. Execute the below script

    sp_dropserver <OLD_SQLSERVER>
    go
   sp_addserver <NEW_SQLSERVER>,LOCAL

6. Stop and start SQL server service

Now you will have all the databases online with all the Logins as same.

Note:

Before doing the above steps ensure that service packs are same in both
servers (Ideally 3 a)

Thanks
Hari
MCDBA

"Daniel W" <daniel.wilen@donot_imano_spam.se> wrote in message
news:D7F2E2BF-3481-44F2-BCB5-91CD6B7FDF38@microsoft.com...
> Hi,
> We just baught a new server with more power.
> The old one has SQL 2000 and the new one will have it to. All databases
and jobs will be transfered to the new server. We will load the databases
from last dump.
> My concern is about the database logins, users and groups. How can I be
sure that all the objects will work with the correct pid? I will not load
the old masterdatabase into the new server.
>
> Someone?
>
> Regards,
> /Daniel

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: Moving SQL Server 2000 to a new machine
    ... In addition to all the CYA advise, you CAN move the system databases. ... Once you've got the new server at SQL2K, SP2, and the physical paths to ... > Recently I was tasked to move our entire SQL Server 2000 to a new server. ...
    (microsoft.public.sqlserver.server)
  • 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)