locked
SQL Database is corrupted RRS feed

  • Question

  •  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)
    Wednesday, December 24, 2014 1:53 AM

Answers

  • not a primary database file. (Microsoft SQL Server, Error: 5171) ? 

    The aforementioned error message conveys that the database is corrupt and is, therefore, not accessible. 

    Cause: 

    The root cause of this error message is that the database file is corrupt. It may be possible because of various reasons such as power surges, human errors, virus infections, etc. 

    Resolution: 

    To address this issue, there are few things that you can perform. First of all, take a backup of this corrupted database lest it is further damaged. Then, you should try to check the database consistency using the DBCC CHECKDB utility. For this, you should run the utility without any clause. The command is executed and the reason for corruption is provided. Then, run the utility using the mentioned clause. However, if you are still not able to repair sql, then you should use a third-party ms sql repair tool for mdf repair. Such tools recover corrupted SQL database components after all kinds of logical crash scenarios. 

    SQL Recovery software is a robust tool that is used to recover corrupt databases created in MS SQL Server 2000, 2005, and 2008. It is able to repair sql components such as tables, defaults, stored procedures, triggers, views and rules. It can also repair database constraints such as primary key, foreign key, unique key, check, etc. It is a highly interactive tool that does not overwrite the original database while scanning it. It is compatible with Window 7, Vista, 2003 Server, XP, 2000, and NT.
    • Marked as answer by CarlosVelaZen Friday, December 26, 2014 1:34 AM
    Wednesday, December 24, 2014 2:01 AM

All replies

  • not a primary database file. (Microsoft SQL Server, Error: 5171) ? 

    The aforementioned error message conveys that the database is corrupt and is, therefore, not accessible. 

    Cause: 

    The root cause of this error message is that the database file is corrupt. It may be possible because of various reasons such as power surges, human errors, virus infections, etc. 

    Resolution: 

    To address this issue, there are few things that you can perform. First of all, take a backup of this corrupted database lest it is further damaged. Then, you should try to check the database consistency using the DBCC CHECKDB utility. For this, you should run the utility without any clause. The command is executed and the reason for corruption is provided. Then, run the utility using the mentioned clause. However, if you are still not able to repair sql, then you should use a third-party ms sql repair tool for mdf repair. Such tools recover corrupted SQL database components after all kinds of logical crash scenarios. 

    SQL Recovery software is a robust tool that is used to recover corrupt databases created in MS SQL Server 2000, 2005, and 2008. It is able to repair sql components such as tables, defaults, stored procedures, triggers, views and rules. It can also repair database constraints such as primary key, foreign key, unique key, check, etc. It is a highly interactive tool that does not overwrite the original database while scanning it. It is compatible with Window 7, Vista, 2003 Server, XP, 2000, and NT.
    • Marked as answer by CarlosVelaZen Friday, December 26, 2014 1:34 AM
    Wednesday, December 24, 2014 2:01 AM
  • Hi,

    I hope you have latest backup in place, and the best way to recover your database is to restore it from latest backup.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Wednesday, December 24, 2014 3:13 AM
  • May be  the MDF file has got damaged due to file header corruption or wrong information in file header.All such situations lead to the inaccessibility of MDF file and need to repair and restore the database. You can use SQL recovery software which helpful in each and every case of MDF corruption and thus allow you to have perfect MDF recovery.Stellar Phoenix SQL Database Recovery is recovery software which allows you to have effective and successful MDF recovery. This MDF repair software can extract all of the data from it. This SQL repair software can restore all of the MDF objects including tables, reports, forms, macros, database constraints, stored procedures, triggers etc.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed as answer by Markwillium Tuesday, December 30, 2014 4:39 AM
    Wednesday, December 24, 2014 11:13 PM
  • Thursday, December 25, 2014 6:55 AM
  • myfile.mdf is not a primary database file. (SQL server , error 5171)/hardware damaged

    It means that the .mdf is corrupted you need to restore it from a full backup file.

    Or you can use a recovered data tool to do it, I recommend you this link http://www.repairsqlserver.net/

    -----------------------------------------------

    Hope above help !

    Doanh

    Thursday, December 25, 2014 9:08 AM
  • As others have said, if you have a good backup, it's time to restore it.

    Else, well, your prospects are not very good. People have pointed to several recovery tools, and while they may promise both this and that, every corruption is unique, and they may not be able to handle exactly your corruption. And, supposedly, they come with quite a price tag.

    The error message is certainly not promising, as SQL Server is not even able to recognize the file as a database file. Or, well, I tried to attach a database from a completely random file, and that failed with a different message number. So maybe it is only a bit in the header that has been mutilated, so that the file now looks like a secondary data file. But to change that bit requires good knowledge of the internal file format, so it is defintiely not for the faint of heart.

    In the end, it's all a matter of much it will cost you to lose the database altogether. I hope you have a backup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 25, 2014 7:47 PM
  • Manually you may try repairing SQL database by Using DBCC CHECKDB or by rebuilding the database, it works most of the time. If that doesn't help, it means that there is a major corruption issue and you need to employ professional recovery techniques.

    You may also try and see if your database files are still accessible using this tool i found here, though i didn't try it on a corrupt SQL server, but it works fine for tho one that's not corrupt

    https://gallery.technet.microsoft.com/SQL-Database-Recovery-1ca3c125
    Wednesday, July 12, 2017 4:53 AM
  • To repair MDF file, open SSMS and execute DBCC statements (checkdb and dbrepair) as a new query. This SQL query scans the corrupt database file and repairs it. It also recovers objects of corrupt database. This recovery is possible is there are minor corruptions.

    For proper solution, follow these process:


    1. Dbcc checkdb ('database_name') with no_infomsgs
    You do not need to repair file if the above statement is executed without any errors. Before executing the repair operation, you need to set the database in single user mode by executing:

    2. Alter database 'database_name' set SINGLE_USER
    You can now start repairing process by using Repair_Rebuild model by executing:

    3. Dbcc checkdb('database_name', REPAIR_REBUILD)
    If this statement executes successfully, then execute 5.5 else execute:

    4. Dbcc checkdb('database_name', REPAIR_ALLOW_DATA_LOSS)
    This statement may lead to data loss. If it is executed properly then execute 5.5 else execute:


    5. 5.1 Alter database 'database_name' set EMERGENCY
    5.2 Alter database 'database_name' set SINGLE_USER
    5.3 Dbcc checkdb ('database_name', REPAIR_ALLOW_DATA_LOSS) with no_infomsgs, all_errormsgs
    5.4 Alter database 'database_name' set ONLINE
    5.5 Alter database 'database_name' set MULTI_USER

    Different database tools use different techniques for SQL repair. The most popular and common SQL Database recovery software are offered by Stellar, Kernel and Systools. These are the brands on which most DBA trust for SQL recovery. The general method for corrupt SQL file recovery

    Here are few good SQL Repair Tools

    1. Stellar Repair for SQL
    2. Kernel SQL Database Recovery
    3. SysTools SQL Repair


    • Edited by Dexter Data Saturday, August 3, 2019 9:40 AM
    Saturday, August 3, 2019 9:37 AM
  • To repair MDF file, open SSMS and execute DBCC statements (checkdb and dbrepair) as a new query. This SQL query scans the corrupt database file and repairs it. It also recovers objects of corrupt database. This recovery is possible is there are minor corruptions.

    For proper solution, follow these process:


    1. Dbcc checkdb ('database_name') with no_infomsgs
    You do not need to repair file if the above statement is executed without any errors. Before executing the repair operation, you need to set the database in single user mode by executing:

    2. Alter database 'database_name' set SINGLE_USER
    You can now start repairing process by using Repair_Rebuild model by executing:

    3. Dbcc checkdb('database_name', REPAIR_REBUILD)
    If this statement executes successfully, then execute 5.5 else execute:

    4. Dbcc checkdb('database_name', REPAIR_ALLOW_DATA_LOSS)
    This statement may lead to data loss. If it is executed properly then execute 5.5 else execute:


    5. 5.1 Alter database 'database_name' set EMERGENCY
    5.2 Alter database 'database_name' set SINGLE_USER
    5.3 Dbcc checkdb ('database_name', REPAIR_ALLOW_DATA_LOSS) with no_infomsgs, all_errormsgs
    5.4 Alter database 'database_name' set ONLINE
    5.5 Alter database 'database_name' set MULTI_USER

    Different database tools use different techniques for SQL repair. The most popular and common SQL Database recovery software are offered by Stellar, Kernel and Systools. These are the brands on which most DBA trust for SQL recovery. The general method for corrupt SQL file recovery

    Here are few good SQL Repair Tools

    1. Stellar Repair for SQL
    2. Kernel SQL Database Recovery
    3. SysTools SQL Repair


    Instead of copying the complete answer, you can share this link: https://social.technet.microsoft.com/Forums/sqlserver/en-US/c2244297-33ca-487f-9780-4e6cce2359e4/repair-corrupted-sql-server-mdf-file?forum=sqltools

    One more thing, you are spamming here which is not allowed. Please focus on the problem instead of promotion. 

    Friday, September 20, 2019 10:09 AM
  • Hi.

    If your SQL data got corrupted and you want to repair it instantly, then you are at very right place because i'm here to recommend you this brilliant and effective SQL Recovery Software which is very effective and reliable software which can easily repair your damaged and corrupted sql database. With this software you can also repair both the MDF and NDF SQL database files because the mdf files also called as master data files which are primary data files store in SQL database. So as MDF files are the base file of all primary data. It is fully capable to recover all the SQL file objects such as tables, views, triggers, stored procedures, constraints, default constraints, indexes, rules, user defined functions & user defined data types.

    Friday, July 10, 2020 11:19 AM