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?

Bill the Lizard
  • 352
  • 1
  • 7
  • 15
  • 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. –  Aug 27 '10 at 17:54

2 Answers2

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.

Philip Kelley
  • 253
  • 2
  • 9
  • 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](http://www.google.com/search?aq=f&sourceid=chrome&ie=UTF-8&q=%22not+a+primary+database%22) –  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. –  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](http://technet.microsoft.com/en-us/library/ms188031.aspx) or [alter database...set offline](http://msdn.microsoft.com/en-us/library/bb522682.aspx) before moving/copying the MDF file. –  Aug 27 '10 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'
Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22