migrating database with error 5173: cannot associate files with different databases

I want to migrate my database to another server
here is my steps:

detach database
copy the mdf file to the new server (without copy the ldf file)
create a new db in the new server with the same name of my original db
detach new db
replace the new mdf file with the original mdf file
but I got the message: error 5173: cannot associate files with different databases
So many thanks for your help
andreni78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Why don't you attach the copied mdf file as a new database?

Steps
Detach database
copy mdf file - you don't explain why you are not copying the ldf file
attach mdf file as new database

HTH
  David
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:

>detach database
ok

>copy the mdf file to the new server (without copy the ldf file)
why without the .ldf file. that one should go along also!

>create a new db in the new server with the same name of my original db
not needed
>detach new db
hence not needed

>replace the new mdf file with the original mdf file
that is now the problem, as the .ldf and the .mdf are not of the same database, hence the error


correct procedure:

detach db
copy mdf (and ldf) file
attach db

during the attach db, you can specify only the .mdf file if you only copied the .mdf file, and it will recreate a new .ldf file for you, but eventually at a location you don't want it to be...
0
Hemantgiri S. GoswamiModerator - MSDN ForumsCommented:
Hi,

refer http://msdn2.microsoft.com/en-us/library/ms174385.aspx for sp_attach_single_file_db

but yes of course why you don't copy ldf file too!!! Do you have any issue with your ldf file!!!

Regards
Hemantgiri S. Goswami
MS SQL Server MVP
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

andreni78Author Commented:
yes, the log file is huge, about 19GB
0
Hemantgiri S. GoswamiModerator - MSDN ForumsCommented:
Hi,
what is the recovery model of the database?
Why don't you shrink the log file and then copy it over the network using robocopy or xcopy!!

Regards
0
andreni78Author Commented:
I migrate from one between hosting server, in different countries, the internet speed is about 30KB/s so it will take a very long time to move log file
0
andreni78Author Commented:
is there any way  to attach mdf file without the ldf file
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, and it has been told already above several times.
just do NOT create a database first, and attach using the sp_attach_single_file_db procedure
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.