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 Friday, July 29, 2005 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 3, 2009 5:41 PM
Points: 10, Visits: 12

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)

Post #205752
Posted Sunday, July 31, 2005 10:15 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, July 10, 2014 10:06 PM
Points: 423, Visits: 534

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?




Post #205948
Posted Monday, August 1, 2005 3:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53

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.

Post #206008
Posted Monday, August 1, 2005 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 23, 2010 8:50 PM
Points: 33, Visits: 2

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

Post #206200
Posted Monday, July 31, 2006 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 12:56 PM
Points: 20, Visits: 50

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.




Post #298348
Posted Friday, September 8, 2006 1:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 9:12 AM
Points: 14, Visits: 19

I tried this but it does not work... any ideas?

exec sp_attach_single_file_db N'MyxxDb' , N'E:\SQLServerData\MSSQL\Data\xx_Data.MDF'

I got the error and the log file is not created...

I tried also:

EXEC sp_attach_single_file_db @dbname = N'Altiris',
   @physname = N'E:\AeXNS_Data.mdf'

EXEC sp_attach_db @dbname = N'AeXNS_Data',
   @filename1 = N'E:\AeXNS_Data.mdf',
   @Filename2 = N'F:\AeXNS_Log.LDF'

nothing is working...

 

Post #307385
Posted Friday, February 8, 2008 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:07 PM
Points: 7, Visits: 35
I wonder if the same methods can be applied to move an OLAP database. I have a task to move an OLAP database from the "processing" server to the "production" server. If so what is the Sql code to move the OLAP database. Thanks.
Post #453086
Posted Friday, February 8, 2008 3:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:51 AM
Points: 1,070, Visits: 912
Try looking at create database for attach.

You cant restore a database with the same name as an existing database - but you can restore it with a different name, apply the transaction logs, stop connections to the original, backup its tlog and apply that to the new one, then rename both. Which if you get right would only be a couple of seconds.

One caveat to detaching databases to move them - Logshipped databases dont seem to like this - if you attach a standby database it seems to get fully recovered. Less than ideal. Anyone know a way around that?



Post #453139
Posted Friday, February 8, 2008 5:37 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:23 PM
Points: 111, Visits: 348

Good article but not practical for many environments. Like some other posters mentioned I think backup/restore should have been mentioned but with a slight twist. We use Litespeed to speed up and compress backups (faster copying) if your employer won't pony up the bucks , I think you can grab a free copy of SQL Safe from Idera which will give the same result. (more or less)
For larger databases working with a compressed backup can be faster than any of the methods mentioned above.

Cheers.

Chris.


Chris.
Post #453163
Posted Friday, February 8, 2008 6:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 12:56 PM
Points: 20, Visits: 50
THIS STATEMENT IS NOT TRUE: "You should note that you cannot re-attach more than 16 files for a single database"

I regularly attach our databases that are 4TB in size and have 25 files!!!



Post #453187
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse