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.
| Author |
Topic |
|
Jamie18
Starting Member
4 Posts |
 Posted - 2007-06-15 : 11:10:17
|
| I'm trying to set up a personal development SQL server on my computer that needs to be identical to another SQL server. I'm running into problems when trying to copy the databases over, I've been using the DTS import/export wizard to accomplish this. The problem occurs when the files are copied onto the new server all the actual table data is correct but certain things change, like primary keys will no longer be listed as primary keys, or the identity of a column will switch from yes to no. I've gone through several different tutorials on how to copy the database with the export wizard but every time i end up with the same problem.Anyone ever dealt with this before? Is it as simple as selecting some different checkboxes in the wizard?JN |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-15 : 15:28:24
|
| Why don't do backup/restore? |
 |
|
|
Jamie18
Starting Member
4 Posts |
Posted - 2007-06-18 : 09:29:36
|
| Backup/restore will not work because the server i'm copying from will not allow me to copy the mdf and ldf files over to the new server after the backup has been made, it says that the files are in use. anyone have any other ideas?JN |
|
|
|
gmoule
Starting Member
2 Posts |
Posted - 2007-06-19 : 07:37:04
|
| If backup/restore won't work, how about attach/detach? |
|
|
|
Jamie18
Starting Member
4 Posts |
Posted - 2007-06-19 : 09:09:37
|
| I'm not entirely sure how attach/detach works.The process would be, detach from source and than attach to destination right? but when i detach it from the source will the source be modified? do i have to attach it once again? is there a time period where the database isn't attached to the source? I just get that feeling from the word detach. I'll search around and see what I can dig up on it anyways. Thanks for the help.JN |
|
|
|
gmoule
Starting Member
2 Posts |
Posted - 2007-06-19 : 09:42:54
|
| You can use the stored procedures sp_attach_db/sp_detach_db or with Enterprise Manager right-click the db. Once the db is detached, move the files however you can to the new server, then attach there. BOL explains it all very well.HTH, glenn |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-19 : 11:17:12
|
| Don't have to copy .mdf nor .ldf file with backup/restore, but have to copy them with detach/attach. You should backup the db with sql backup statement, not backup db files. Then you can copy backup file to new server. |
|
|
|
Jamie18
Starting Member
4 Posts |
Posted - 2007-06-19 : 11:27:28
|
| Well, I ended up getting it done with the attach/detach method, someone might have gotten angry that they couldn't use the database during the time it took to copy, but I'm all set up now so that's what counts. thanks for your help thoughJN |
|
|
|
|
|
|
|
|
Database export errors