?
Solved

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

Posted on 2007-07-31
8
Medium Priority
?
3,567 Views
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
0
Comment
Question by:andreni78
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 19606199
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19606308

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

Assisted Solution

by:Hemantgiri S. Goswami
Hemantgiri S. Goswami earned 800 total points
ID: 19606595
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:andreni78
ID: 19606774
yes, the log file is huge, about 19GB
0
 
LVL 7

Expert Comment

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

Author Comment

by:andreni78
ID: 19606981
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
 

Author Comment

by:andreni78
ID: 19607121
is there any way  to attach mdf file without the ldf file
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 19607181
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 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