Sunday, May 22, 2011

How to resolve error: “mdf is not a primary database file”

I want to share my personal experience on SQL Server Database. One day I was trying to set my SQL database online with command:

ALTER DATABASE mydb SET online

& suddenly I got an error message:

Msg 5171, Level 16, State 1, Line 1
E:\Data\mydb_log.ldf is not a primary database file.
Msg 5171, Level 16, State 2, Line 1
E:\Data\mydb.mdf is not a primary database file.
File activation failure. The physical file name "E:\Data\mydb.mdf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'mydb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After that I do research on this error & I found this error occurs in many situations like :

1) When you are trying to attach your SQL database files with windows then also you get same error message. Mostly this error occurs in SQL Server 2005.

2) When you are trying to set your database online then also you get same error message.

Cause: 

The main cause of this error is that your mdf file get corrupt.

Solution: 

There is four solutions to resolve this error:

1. First you should check the database by this command “DBCC CheckDB”. It shows the details of the database then you can find the problem & easily resolve it.

2. You can tried to add the file using the command sp_attach_single_file_db. 

3. Try to restore database from a bak file.

4. It is possible that the database may have been corrupted when it was downloaded & “DBCC CheckDB” reflects damages in SQL server MDF file. You also don't have updated bak file. In this case you should try SQL Recovery Software. 

Note: If data get overwrite no one can repair it. 
  • When you find that your mdf file has been corrupt, don't perform any activity on the file. If there is 80% chances to resolve this error it will becomes 10%. because may be your data get overwrite from your activities & you will never get back your data. 
  • Before purchasing any SQL recovery software, first download its demo version & check the preview of your recovered database after that purchase it.

No comments:

Post a Comment