In SQL Server 2005 when I try to attach my database (MDF) it shows 'not a primary database', but this database has one MDF and one LDF only. How can I fix it?

migrated from stackoverflow.com Aug 29 '10 at 14:19

This question came from our site for professional and enthusiast programmers.

  • 1
    Where did the MDF file come from? Was it properly detached from its previous location? If not, there's a good chance it's now corrupted. – Joe Stefanelli Aug 27 '10 at 17:54

A search on the term "Primary Database" in Books Online lists entries for log shipping and database mirroring. These are pretty advanced topics. It sounds very much like you need to find out a lot more about where the database you're working with is coming from, i.e. was it part of a log shipping or database mirroring setups. Which this knowledge, you'll know what it is you need configure on your new (?) system.

  • 1
    I disagree about narrowing the focus to log shipping and mirroring. I believe that "not a primary database" is a fairly common error any time an attach operation fails. See this Google search – Joe Stefanelli Aug 27 '10 at 19:02
  • Fair enough--SQL 2005 doesn't have error messages and causes in BOL. A Goolge check implies that another common cause would just be corrupt files, in which case you'd just need to copy the files again. – Philip Kelley Aug 27 '10 at 19:33
  • As in my comment on the original question, I still suspect the file is corrupted because it was not properly detached from its previous location, in which case recopying will not help. – Joe Stefanelli Aug 27 '10 at 19:37
  • How do you improperly detach a database? – Philip Kelley Aug 27 '10 at 20:40
  • By failing to use sp_detach_db or alter database...set offline before moving/copying the MDF file. – Joe Stefanelli Aug 27 '10 at 21:34

Can you show us exactly how do you try to attach the database? A common mistake is to run sp_attach_db w/o specifying the database name:

exec sp_attach_db 'c:\mypath\mydb.mdf', 'c:\mypath\mydb.ldf'

Because the database mane is missing, the first paramter passed in (the path to the MDF) is actually the database name and the MDF is missing, resulting exactly in the error you mention. The correct way is:

exec sp_attach_db 'mydb', 'c:\mypath\mydb.mdf', 'c:\mypath\mydb.ldf'
  • +1 Good thinking. I hope it is that simple for the OP's sake. – Joe Stefanelli Aug 27 '10 at 19:50
  • @Joe Stefanelli: I just did this yesterday, is fresh in my cache ;) – Remus Rusanu Aug 27 '10 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy