0

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?

1
  • 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, 2010 at 17:54

2 Answers 2

3

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.

5
  • 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, 2010 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. Aug 27, 2010 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, 2010 at 19:37
  • How do you improperly detach a database? Aug 27, 2010 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, 2010 at 21:34
3

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'
2
  • +1 Good thinking. I hope it is that simple for the OP's sake.
    – Joe Stefanelli
    Aug 27, 2010 at 19:50
  • @Joe Stefanelli: I just did this yesterday, is fresh in my cache ;) Aug 27, 2010 at 19:58

You must log in to answer this question.