Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 whats the best way to copy a database ?

Author  Topic 

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-02-13 : 09:21:51
I have a sql 2005 database on a server and I want to have a copy of that database on my workstation (running sql 2005 developer). What is the easiest way to do this ? We have DBGhost also.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-13 : 09:29:25
1. Backup and restore
2. SSIS/BCP


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 09:47:27
3. (if not production server) take DB offline - copy MDF / LDF - attach DB


KH

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-13 : 10:52:50
i never go with attach/detach.
why?
because all sort of thigs can go wrong and you end up with lost database.

if you have do attacht detach then it means you don't have proper backups because if you did you'd restore from them.
so for me attach/detach for moving a db is a big NO NO.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 11:03:21
attach/detach is fine provided you have a backup ... errmmmm ... in which case use that instead

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Backup,Restore,attach,Reattach

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-13 : 11:19:13
quote:
Originally posted by spirit1

i never go with attach/detach.
why?
because all sort of thigs can go wrong and you end up with lost database.

if you have do attacht detach then it means you don't have proper backups because if you did you'd restore from them.
so for me attach/detach for moving a db is a big NO NO.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp



Huh?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-13 : 11:30:15
Always use backup and restore.

Detach/copy/attach should only be used if you have a shortage of trouble in your life.





CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 11:40:17
"shortage of trouble in your life"

I'm going to steal that phrase!
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-13 : 12:46:25
huh brett?




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-13 : 13:34:21
This is the first time I've ever heard anyone say that this not good. Why do you say that? I've never seen a problem with it...plus it's faster



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-13 : 14:31:36
Detach/attach is the fastest method to move a database. But it does take the source databases offline, so if you are copying prod to test or similar then you should use BACKUP/RESTORE instead.

I'm not sure what people are referring to when they mention problems with detach/attach. Please elaborate.

Tara Kizer
Go to Top of Page

raclede
Posting Yak Master

180 Posts

Posted - 2007-02-13 : 20:12:59
In my personal experience Detach/Attach is faster than Backup/Restore provided that you know what you are doing..
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-14 : 05:02:49
backup/restore is a more safe procedure.

if you detach the db you don't have it online anymore and there's always risk of not being able to attach it back on.
seen it happen... and for no obvious reason.
and if that happens you have your work cut out for you...

i'm not saying it's bad. if there was no reason for it then it wouldn't be available, now would it?
it just think it's not worth the nerves when you're detaching it and attaching it.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-14 : 10:21:54
this might also be of interest:
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/02/14/how-to-move-databases-using-detach-attach-functions.aspx



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-14 : 11:22:23
I think it is a lot easier to get in trouble using detach/copy/attach.

I assume that anyone who is asking here what to do does not have that much experience, so it is better to tell them to use backup/restore.

I almost always use backup/restore myself. I can't remember the last time I detached a database.





CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 12:07:41
If someone is using detach/attach to move a database, then that's typically because they don't need the database on the source server anymore, so it doesn't matter that you can't attach it there. If it ends up not being able to attach it to the destination server too, then you just restore from backups. So you haven't lost anything.

I've never had any problems attaching a database that was properly detached or the file was copied when the service was down. I've used the detach/attach countless times to quickly move all databases (including system ones) to a new server. BACKUP/RESTORE is so much slower for a new server. The only thing constraining detach/attach is the network copy speed. BACKUP/RESTORE has that plus the time it takes to backup and the time it takes to restore. Then perhaps you've got to unorphan the accounts, move DTS packages, create logins, etc... You don't need to do any of that with detach/attach of all databases when all paths are the same on both servers.

When time is of the essence, use detach/attach to move from one server to the next (when all paths will be the same for the database files on both servers).

Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 20:49:20
It seems that the "issue" or "unsafe" action is on the detach.

How about not detaching the database but just shutdown the SQL Server or take the database Offline ? This is normally what i do. Take offline rather than detach it.


KH

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 20:59:28
That's normally what I do as well. I've never had any issues with detach or attach though. The problems with attach are usually as a result of some inexperienced person copying the files with the service running. That's a big no no.

Tara Kizer
Go to Top of Page

ekb18c
Starting Member

18 Posts

Posted - 2007-02-15 : 00:03:09
Back up/restore is the easiest.

www.sqlnerd.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 06:03:15
"Back up/restore is the easiest"

I wonder ....

... I think the real issues are that you should deal with the logical Names and the Logins. Both of these are likely to be overlooked by a "novice".

For me the Logical Names are "more likely to be overlooked" with Attach than Restore - but that's probably because I have scripts that assist me with Restore, but nothing similar for Attach.

In terms of "which is better" I think that for a "Novice" that Attach is easier. Detach, Copy the file, Attach. Very fast too - none of the elapsed time for creating and "zero-ing" the file before the restore can actually start.

Again, for a Novice, Backup & Restore would probably require:

Make a Backup - that takes time, and fiddling around in the GUI

If a Backup already exists will they know which one to copy? Yes the filenames usually contain a timestamp, but a .BAK file might be Full, Diff or Tlog ... or even multiple backups!

Copy the Backup file - same deal as Copying the Detached database, except that the Backup will be smaller, so should be quicker and maybe even easier (e.g. big database being copied via removable storage)

Restore the backup - again, needs some mastery of the GUI. The GUI wants, first and foremost, to enable recovery of a pre-existing database using the History of backups stored in MSDB. For a new database from an "unknown" .BAK file there are several steps required, all probably non-obvious to a Novice. And the GUI is pretty slow too - whilst all the lists of available resources are compiled and so on.

The "non-obvious" (IMHO) steps required in the GUI include:

Change Database name
Change to use DEVICE
Have to SELECT Device
Have to ADD Device
Have to use Drive Letters as they are known to the Server, not the Workstation!
Have to adjust the Physical File locations
May have to use "Force restore over exiting database" option

I reckon all this lot are pretty daunting for a Novice.

Of course with scripting all these options are easily taken care of - and, if you need it, there is a discussion of syntax here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

but I don't think this is really in Novice territory

For seasoned Professionals scripting the Restore is Meat&Veg, so not going to be hard ... but they won't be asking this question, will they?!!

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-15 : 12:54:43
quote:
Originally posted by ekb18c

Back up/restore is the easiest.

www.sqlnerd.com



Detach/attach is the easiest! You don't have to worry about jobs, logins, DTS packages, etc...

I typically am copying prod to dev though, so I have to use BACKUP/RESTORE.

Tara Kizer
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -