Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: What is the best way to move a SQL Server 2000 Instance?

    If one of you smarties can guide me I'd be grateful. Thank you in advance.

    Question: Will a straight windows copy of mdf/ldf files work without doing detatch/attach if the entire instance is copied while SQL service is stopped?

    Old Server:
    Windows 2000 running SQL Server 2000 SP4.

    New Server:
    Windows 2003 running fresh install of SQL Server 2000 SP4. Presently this server is non-production so I don't care about preservation ramifications. It's a fresh install of SQL Server.

    The directory structures on the two machines are identical for the directories containing .mdf, .ldf, and scheduled backups and log changes.

    This is a non-distributed installation (no replication).

    I Googled this write-up fully describing the detatch / re-attach process that includes the system database. I'd prefer a method that doesn't involve production downtime. Simpler would be nice too.

    Thanks!
    Last edited by vich; 08-07-09 at 23:13.

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I used a normal backup/restore and ran these scripts provided by Microsoft Support to generate login-migration scripts. I moved my SQL Agent scripts one at a time using EM Script Generation.

    EDIT: Even running this script, the logins were orphaned. This microsoft publication talks about the issue.

    I only have about 6 logins so I just manually reassigned all the DB access and everything worked fine after that.

    I'm still curious whether a straight copy would be safe but for today's problem it's moot.
    Last edited by vich; 08-07-09 at 23:11.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Still no takers?

    I'm trying to understand my disaster recovery options.

    This migration is taken care of - I"m following the Microsoft publications linked above.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •