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


Quickly Moving Databases


Quickly Moving Databases

Author
Message
ckempste
ckempste
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4339 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
SSC Eights!
SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)

Group: General Forum Members
Points: 908 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
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4339 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 (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 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 (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 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
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 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 (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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