none
SQL Database is corrupted....... plz help. RRS feed

  • Question

  • Respected Sir,

     

    unfortunately hard disk of my system is damaged, now i recovered my database. but it cannot be attached again to SQL management studio 2005.

    the error is myfile.mdf is not a primary database file. (SQL server , error 5171)

     

    plz sir help me...

    Thursday, August 21, 2014 10:15 PM

Answers

  • Corruption in SQL server database can happen due to any uncertain like virus attack, power failure, hardware issue, OS malfunction, sudden system shutdown, so on. When a SQL server data file (.mdf) is corrupt then DBAs can try several methods to repair and recover data from it. 

    • Run DBCC CHECKDB: It checks & reports all the error message in errorlog, if there is any problem with the database. Try to analyze & understand the error message logged in the errorlog. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file. 

    Detailed information about DBCC CHECKDB is available here: http://www.techrepublic.com/forums/discussions/sql-database-recovery-from-corrupt-database-file/  

    • Restore from backup: If above command fails to fix the error message the try to restore the database from clean backup. 
    • 3rd party tool: Try 3rd party Recovery Toolbox for SQL Server to recover data from corrupt mdf file. Most of the software have demo version that shows the preview of corrupt SQL server data file. 

    http://www.sql.recoverytoolbox.com/

    • Marked as answer by MiShort Saturday, August 23, 2014 6:06 PM
    Friday, August 22, 2014 11:56 AM

All replies

  • It seems database file is damaged and this is my initial assumption based on your error message.

    Do you have any old or recent backup of the same database ? If so , restore from the backup device.

    Otherwise there is no other much chance left here, even if the backup is corrupted then we cant try to restore after skipping the error, but if there is no backup and database files are corrupted then its very hard to get the database and recover.

    If there is no Backup and if your DB files are demaged then there is no other best way to recovery your Database, best backup plan will help us lot in this situation or DR setup also might help, since this is Laptop then you should have you backup...

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e952a15-90cb-47a5-8372-1cf854a4cacb/mdf-is-not-a-primary-database-file-microsoft-sql-server-error-5171?forum=sqldataaccess


    Raju Rasagounder Sr MSSQL DBA

    Thursday, August 21, 2014 11:29 PM
  • Hello

    Have a look to these replies. Could be helpful for your problem

    http://www.codeproject.com/Questions/589041/UnableplustoplusattachplusmdfplusfilesplusinplusSQ


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, August 22, 2014 1:56 AM
  • Corruption in SQL server database can happen due to any uncertain like virus attack, power failure, hardware issue, OS malfunction, sudden system shutdown, so on. When a SQL server data file (.mdf) is corrupt then DBAs can try several methods to repair and recover data from it. 

    • Run DBCC CHECKDB: It checks & reports all the error message in errorlog, if there is any problem with the database. Try to analyze & understand the error message logged in the errorlog. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file. 

    Detailed information about DBCC CHECKDB is available here: http://www.techrepublic.com/forums/discussions/sql-database-recovery-from-corrupt-database-file/  

    • Restore from backup: If above command fails to fix the error message the try to restore the database from clean backup. 
    • 3rd party tool: Try 3rd party Recovery Toolbox for SQL Server to recover data from corrupt mdf file. Most of the software have demo version that shows the preview of corrupt SQL server data file. 

    http://www.sql.recoverytoolbox.com/

    • Marked as answer by MiShort Saturday, August 23, 2014 6:06 PM
    Friday, August 22, 2014 11:56 AM
  • When SQL Server MDF file corruptions occurs, then the possible solutions are based on the situation:

    1. If the corruption is not severe, then you can use the SQL Server build-in command DBCC CheckDB to correct the errors. See http://technet.microsoft.com/en-us/library/ms176064.aspx

    2. If the corruption is severe, then you may try some third party tools. The one I have tried successfully in the past is DataNumen SQL Recovery at http://www.datanumen.com/sql-recovery/ . You can use it to scan and recover the data from your corrupted MDF and NDF database files.

    Good luck!

    Friday, August 22, 2014 3:10 PM
  • Hi

    Here are some implementations which are beneficial for eliminating SQL error 5171 and easily attach MDF File must read it: - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195847&#755502

     

    Saturday, August 23, 2014 11:50 AM
  • To restore sql database from .mdf file use following:

    1
    Open your SQL Server Management Studio console. This application shortcut is available in the SQL Server directory in the Windows Start button.

    2
    Enter the system administrator user name and password. SQL Server's administrator user name is "sa." This account is required for privileges to restore the database. If your restoring on a host provider server, use the administrator user name and password they supplied for your account.

    3
    Right-click your database name and select "Attach." In the new window that opens, click the "Add" button to open a dialog box.

    4
    Select your MDF file and press the "Ok" button. It may take several minutes to restore the database if it is a large file. Once the process is finished, browse your tables to verify the data. The database is now restored.
    Tuesday, October 21, 2014 5:22 PM
  • This tool efficiently recovers entire MDF files including triggers, tables, keys, procedures, indexes. It automatically rebuilds a new database MDF file making it the best tool among many others in the market.

    You Can Also Try:- http://www.pcrecoveryutility.com/sql-database-recovery.html

    Friday, January 2, 2015 6:32 AM
  • I have found an informative video that shows how to fix SQL Server Error 5171: https://www.youtube.com/watch?v=yt_DeOOErmk

    Monday, March 14, 2016 5:05 AM