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

Posted on 2007-07-31
Last Modified: 2011-10-03
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
Question by:andreni78
    LVL 35

    Expert Comment

    by:David Todd

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

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

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]

    >detach database

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

    Assisted Solution

    by:Hemantgiri S. Goswami

    refer 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!!!

    Hemantgiri S. Goswami
    MS SQL Server MVP

    Author Comment

    yes, the log file is huge, about 19GB
    LVL 7

    Expert Comment

    by:Hemantgiri S. Goswami
    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!!


    Author Comment

    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

    Author Comment

    is there any way  to attach mdf file without the ldf file
    LVL 142

    Accepted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how the fundamental information of how to create a table.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now