SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quickly Moving Databases


Quickly Moving Databases

Author
Message
ckempste
ckempste
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5339 Visits: 1
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
Strommy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 351
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
Jorge Cordero
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 13
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
ckempste
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5339 Visits: 1
Hi there

From memory here, I didnt "title" the article so the SSC crew gave it one Smile
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
Derek Robinson
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 116

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
David Lu
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 135

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
JStiney
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 446

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
Joe Burdette
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 494

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
Merrill Aldrich
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 137
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
JStiney
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 446

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?





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search