Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Quickly Moving Databases Expand / Collapse
Author
Message
Posted Saturday, March 23, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, 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"
Post #3193
Posted Monday, May 20, 2002 6:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 20, 2012 12:51 PM
Points: 322, 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.




Post #30453
Posted Thursday, May 23, 2002 5:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:38 PM
Points: 86, Visits: 8
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.








Post #30454
Posted Thursday, May 23, 2002 6:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
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"
Post #30455
Posted Wednesday, May 19, 2004 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:19 AM
Points: 45, Visits: 110

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.

Post #116579
Posted Wednesday, May 19, 2004 10:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:47 PM
Points: 192, Visits: 128

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




Post #116657
Posted Wednesday, May 18, 2005 8:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:05 AM
Points: 75, 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.

 




Post #183352
Posted Wednesday, May 18, 2005 9:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:44 AM
Points: 437, Visits: 403

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
Post #183413
Posted Friday, July 29, 2005 9:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 23, 2014 12:58 PM
Points: 126, Visits: 136
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
Post #205704
Posted Friday, July 29, 2005 9:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:05 AM
Points: 75, 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?




Post #205714
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse