cant attach mdf file because of suspect mode

Question

Please I need Help

i cant attach Mdf Database , As last time it was suspect , i made detach and i cant now attache it again .
and i recieve this message

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

E:\خاص بالبرنامج وممنوع الحذف\database\Sadiek.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

Answer 1

Unfortunately, your database is corrupted to the extent that SQL Server marked it as suspect, and will not let you reattach it.

I would strongly suggest you just restore from backups (you do have these right?). Trying to repair such a heavily corrupted database can be a major exercise in frustration.

---

From this point, let's assume you do not have backups, and do not want to pay a data recovery specialist nor for specialist software to do this.

The general steps you need to follow are these, you may need to investigate various results you receive at each step.

• Create a new database with different file names than the original, where the file and filegroup layouts match.
• Shutdown SQL Server service.
• Delete the new database files and replace with copies of the originals .mdf and .log files. Do not work with the original files.
• Start SQL Server
• Resume attempts to repair the database:
  • Put the database in emergency mode to attempt a repair. ALTER DATABASE YourDbCopy SET EMERGENCY;
  • Set it into single user mode. ALTER DATABASE YourDbCopy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run a check to see what the problems are. DBCC CHECKDB (YourDbCopy) WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, TABLOCK;
  • If you wish to go ahead with a repair, use the repair level recommended by CHECKDB.
  • Note that REPAIR_ALLOW_DATA_LOSS can and often does cause serious loss of data. You may wish to inspect the raw pages of the tables with issues, and attempt to recover them manually before repairing. Use DBCC PAGE etc for this.
  • Multiple rounds of DBCC CHECKDB... REPAIR_ALLOW_DATA_LOSS may be necessary. If there are still remaining issues, you may need to repair them manually by deleting rows or dropping objects, or in a hex editor assuming you know what you are doing.
  • Run USE YourDbCopy; DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, NO_INFOMSGS; If there are constraint violations, the problematic rows must be deleted, you may want to recover these also.
  • Hopefully at this point you should be able to bring it back online. ALTER DATABASE YourDbCopy SET ONLINE;
  • Set the database to multi-user ALTER DATABASE YourDbCopy SET MULTI_USER;
  • Assuming that all worked, the new database should be ready to go. Set the name back to the original. ALTER DATABASE YourDbCopy MODIFY NAME = YourDb;

Answer 2

Error - 5171 Dear friend, Is it a common error that occurs due to the SQL file corruotion beucase database base corruption in SQl can afect the main file and its work process.

Why this error encounter, Once the header page or other page is not recognized by the SQL Server, then MDF file is not considered as a valid primary database file. This situation becomes the cause of SQL database error 5171

We can have two cases to solve this eroror. Case One -Corruption in Mirrored Databases Solution - Step 1. At first, set principal database. Step 2. Then, modify MDF file information by using “ALTER DATABASE MODIFY FILE” command. Step 3. Now, stop the currently running SQL server instance by clicking on the Stop option. Step 4. After that, copy the MDF and LDF files to a different directory. Step 5. Finally, restart the SQL Server and attach MDF and LDF files.

Case Two -Improper Upgradation of SQL Server

Solution - Run command sp_attach_db for attaching the database.

I hope it, it will help you.

Thanks