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


Quickly Moving Databases


Quickly Moving Databases

Author
Message
John Kanagaraj
John Kanagaraj
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

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


growl
growl
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 586

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
Mike  Metcalf
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

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


Al Heckers
Al Heckers
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

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


cgunner
cgunner
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

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





Dominique DUCHEMIN
Dominique DUCHEMIN
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 20

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...


Paul Mak-346751
Paul Mak-346751
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 37
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.
RichB
RichB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1617 Visits: 1058
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?



Chris_P
Chris_P
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 369
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.
cgunner
cgunner
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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!!! w00t



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