Quickly Moving Databases

  • ckempste

    SSCoach

    Points: 17983

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Strommy

    SSCrazy

    Points: 2276

    I did like the article in general, although if I wanted a really fast method to transfer databases > 2 GB, I would not use this method. sp_attach is a good way to transfer databases when downtime isn't the primary concern, although in the environment I work in, we would not be able to use it. When we need to move a database from one server to the other with as little downtime as possible, we replicate as often as possible before the transfer. Then we take the old server down after one last replication and restore it to the other server. The setup for this process takes a long time, although we can get away with downtime less than 5 minutes. With sp_attach, the file copy would take a long time.

  • Jorge Cordero

    Ten Centuries

    Points: 1144

    What kind of replication are you talking about? Sounds pretty much complicated.

    I consider the article's approach useful because of its simplicity. Not only the LAN may transfer a detached db. If the LAN is old and slow, an auxiliary removable hard disk or jazz unit also may transfer it, and the time saved would be considerable.

    quote:


    I did like the article in general, although if I wanted a really fast method to transfer databases > 2 GB, I would not use this method. sp_attach is a good way to transfer databases when downtime isn't the primary concern, although in the environment I work in, we would not be able to use it. When we need to move a database from one server to the other with as little downtime as possible, we replicate as often as possible before the transfer. Then we take the old server down after one last replication and restore it to the other server. The setup for this process takes a long time, although we can get away with downtime less than 5 minutes. With sp_attach, the file copy would take a long time.


  • ckempste

    SSCoach

    Points: 17983

    Hi there

    From memory here, I didnt "title" the article so the SSC crew gave it one 🙂

    Fair comment though, I have a few client sites that required 24x7, and attaching and de-attaching is not suitable. To get around this we could:

    a) replicate (can be complicated and you need to be 100% sure the schema is identical

    b) log ship (very fast)

    c) script schema, read-only prod, and populate new server via linked server routines (no keys)

    the swap as need be at the business layer.

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Derek Robinson

    Mr or Mrs. 500

    Points: 594

    Nice article. I'd like to see more practical ones such as this.

    One small point - timestamp. If you're moving tables between servers then you may get duplicate timestamp data when attaching. Timestamps are meant to be unique to a server.

  • David Lu

    SSC Eights!

    Points: 882

    great article from Chris Kempster!

    I like his approach of divide into 3 methods with t/sql code.

    It is quite useful for my environment, since most files are < 2 gb due archiving periodically.

    I did not know that there is a way to take a database off-line with [exec sp_dboption N'mydb', N'offline', N'true'].

    thanks

    -D

  • JStiney

    Say Hey Kid

    Points: 671

    I would like to suggest that the article should have included you basic backup and restore as your first choice for moving databases.

    There is no down time on your source system and I'm not sure that a detach, copy, and attach is that much faster than a backup and restore.

     

  • Joe Burdette

    SSChasing Mays

    Points: 657

    nice article... I like the comment of "articles I write will focus on the 'command line' (namely Query Analyser) to provide DBA’s with a reference point to turn back to when the GUI is not available for whatever reason."

    I'm old enuff to remember when this was Sybase, and it was ALL command line


    Regards,

    Joe Burdette
    hanesbrands.com

  • Merrill Aldrich

    SSCrazy

    Points: 2142

    Great article! Very informative. Thanks so much for taking the time to write this up. As I am about to move a database myself, it's quite timely.

    One minor, even picky point: the term is actually "detach." There is no such word as "de-attach."

    http://dictionary.reference.com/search?q=detach

  • JStiney

    Say Hey Kid

    Points: 671

    What am I missing here???

    I don't see that these techniques as good a simple backup and restore which has no downtime?

    Where is the evidence that they are any faster?

  • John Kanagaraj

    Old Hand

    Points: 354

    Great article Chris! (shows your attachment to the command line from an earlier life as an Oracle DBA, Mate!)

    John Kanagaraj

    (your friend from way back to '98 in Oracle-l)

  • growl

    Hall of Fame

    Points: 3066

    Good article but I have to agree with most of the folks out there

    backup and restore is the way to go.

    By the way how do you attach a database with more than one log file?

  • Mike Metcalf

    SSCrazy Eights

    Points: 8763

    Not a bad article for what it covers, however, I've had experience with this sort of thing in the past and whilst I agree it's fine for small databases it is not the way to go with anything medium to large.

    Also, if you have full-text indexing working against the database you should be aware that detaching it breaks the cataloging process, this applies even if you leave everything in situ and re-attach.  Again, whilst this isn't a concern for some smaller databases you will need to factor in the time required to do a full population after attaching it again.

  • Al Heckers

    Right there with Babe

    Points: 745

    OK, I"m no SQL expert, but I've been tapped by my company to move our CRM SQL database from a primary to a secondary partition on the same server. I've read about detach and attach, which looks pretty simple, and I've successfully done it with a test database. Now I'm reading the posts about backup and restore to a new location, which I've also tried, however when I try to do the restore I get an error message about the database being in use and I have to take it offline in order to complete the restore. So it appears to me that the backup and restore method DOES require down-time. Can anyone clarify and honestly tell me what the best method is?

    -Al

  • cgunner

    Ten Centuries

    Points: 1068

    This statement is in correct:

    "You should note that you cannot re-attach more than 16 files for a single database."

    I routinely re-attach databases with 25 files.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply