SQLServerCentral / Discuss Content Posted by Chris Kempster / Article Discussions / Article Discussions by Author / Quickly Moving Databases / Latest Posts

RE: Quickly Moving Databases

arnold-491793 — Tue, 12 Jan 2010 23:57:14 GMT

Is there any risk in using sp_detach in production database? I would like to move some of my data file in different array partition to have more disk space. I'm just worried about the uncommitted transaction in my production database. If i have a downtime schedule let say tomorrow at 8:00 am then i detach my database at 8:30 am. What will happen to the transactions? Is the sp_detach will not delete the uncomitted transcation then this will comit after sp_attach? i know the transaction is in logs, I just want to make sure that i will not do anything wrong in my data.Thanks for the expert advice.

RE: Quickly Moving Databases

Steve Jones - SSC Editor — Fri, 08 Feb 2008 07:25:22 GMT

Is that more than 16 data files? Or is it with log files?sp_attach_db has hardcoded 16 file limit because of the parameters. Look at the code. If you're doing more than that, someone must have modified your procedure.

RE: Quickly Moving Databases

RichB — Fri, 08 Feb 2008 06:49:40 GMT

BOL 2000"sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause."

RE: Quickly Moving Databases

cgunner — Fri, 08 Feb 2008 06:25:58 GMT

[b]THIS STATEMENT IS NOT TRUE:[/b] "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:

RE: Quickly Moving Databases

Chris_P — Fri, 08 Feb 2008 05:37:12 GMT

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.

RE: Quickly Moving Databases

RichB — Fri, 08 Feb 2008 03:42:32 GMT

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?

RE: Quickly Moving Databases

Paul Mak-346751 — Fri, 08 Feb 2008 00:16:34 GMT

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.

RE: Quickly Moving Databases

Dominique DUCHEMIN — Fri, 08 Sep 2006 13:58:00 GMT

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

RE: Quickly Moving Databases

cgunner — Mon, 31 Jul 2006 07:23:00 GMT

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.

RE: Quickly Moving Databases

Al Heckers — Mon, 01 Aug 2005 10:57:00 GMT

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

RE: Quickly Moving Databases

Mike Metcalf-160221 — Mon, 01 Aug 2005 03:52:00 GMT

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.

RE: Quickly Moving Databases

growl — Sun, 31 Jul 2005 22:15:00 GMT

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?

RE: Quickly Moving Databases

John Kanagaraj — Fri, 29 Jul 2005 12:03:00 GMT

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)

RE: Quickly Moving Databases

JStiney — Fri, 29 Jul 2005 09:44:00 GMT

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?

RE: Quickly Moving Databases

Merrill Aldrich — Fri, 29 Jul 2005 09:27:00 GMT

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

RE: Quickly Moving Databases

Joe Burdette — Wed, 18 May 2005 09:41:00 GMT

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

RE: Quickly Moving Databases

JStiney — Wed, 18 May 2005 08:01:00 GMT

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.

RE: Quickly Moving Databases

David Lu — Wed, 19 May 2004 10:41:00 GMT

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

RE: Quickly Moving Databases

Derek Robinson — Wed, 19 May 2004 05:22:00 GMT

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.

RE: Quickly Moving Databases

ckempste — Thu, 23 May 2002 18:03:00 GMT

Hi thereFrom 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 identicalb) 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.CheersChris

RE: Quickly Moving Databases

Jorge Cordero — Thu, 23 May 2002 17:32:00 GMT

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.

RE: Quickly Moving Databases

Strommy — Mon, 20 May 2002 18:00:00 GMT

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.

Quickly Moving Databases

ckempste — Sat, 23 Mar 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp