Repair/Recover Suspect Database in SQL 2000/2005/2008

steps to follow in repairing the suspect database if the culprit was an I/O error:

While take a backup LDF or MDF it shows error like

So follow below steps to recover MDF/LDF

• Kill all connections and stop SQL Server

• Perform a Checkdisk with repair option for  C:& corrupted DB drive

• Perform a Defragmentation corrupted database drive

• Now copy the files necessary (MDF, LDF and Backups)
• Perform a backup

BACKUP DATABASE <dbname>TO DISK='X:\YourFolder\YourFileNameDB.bak'

• Restore the backup
• Repair the Database (DBCC CHECKDB)

USE [master]

GO

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE <dbname> SET SINGLE_USER

GO

DBCC CHECKDB <dbname>, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS

GO

ALTER DATABASE <dbname> SET MULTI_USER

GO

• Backup the repaired DB

To download query for recover SQL Database: