Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: COPYING a DB from one server to another

    I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.

    Attach/Detach is out since I can only move not copy a DB using this method.
    export/import, bcp and DTS take too long and are not indented for this anyways.

    so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.

    1. Take DB1 that's running on server1 OFFLINE
    2. Manually copy all datafiles and logfiles from Server1 to server2.
    3. Attach DB1 on server2.

    It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...

    Is this supported?
    Any chance of corruption on either of the servers?
    If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
    Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?

    PS: Are there any other ways to do this?

    Thanks so much.

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Just detach DB copy ( COPY do not MOVE) log & data to new server.
    Attach to old server.
    Attach to new server.

    I have just stopped the server and copied the files, but I am afraid that sometimes it may not stop the DBs as nicely as if detaching them.

    Tim S
    Last edited by TimS; 02-15-05 at 14:27.

  3. #3
    Join Date
    Dec 2001
    Posts
    40
    thanks. That makes sense.

  4. #4
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    I don't think these methods are currently supported by MS. But if db is cleanly closed (i.e. taken offline or server orderly stopped), db files copied and attached to another server, according to several books I've read (for example Inside SQL Server) everything should be OK. I've done that many times myself without slightest problems. mojza

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Detaching is preferred simply because taking a db off-line already prevents any access to it, but leads to issues in other activities. Example could be you transaction log dumps based on maintenance plans, which will start failing if "All user databases" option is selected. Contrary, if you detach it, it gets removed from sysdatabases and everything else goes well.

    EDITED: Why are you saying that these methods are not supported by MS? Which book says that?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    Kalen Delaney-Inside SQL Server 2000, in section on attaching, detaching dbs. I haven't got it here, so I cannot tell you exact page number. It was something like you're not supposed to do that but it works fine anyway. mojza

Posting Permissions

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