repair suspect database SQL Server

Question

How to repair suspect database on SQL Server ?

On my TEST SQL Server one of the database is in Suspect Mode and is not accessible to any user.

Any help would be appreciated
Thanks

Answer 1

Before you run this script
 •Kill all connections and stop SQL Server
 •Perform a Checkdisk with repair option
 •Perform a Defragmentation
 •Now copy the files necessary (MDF, LDF and Backups)
 •Perform a backup
 •Restore the backup
 •Repair the Database (DBCC CHECKDB)
 •Backup the repaired DB
 
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
 
Or
 
Take Care From this Script becouse it will make repaire and may be some Data will loss
 
EXEC sp_resetstatus ‘yourDBname’;
 ALTER DATABASE yourDBname SET EMERGENCY
 DBCC checkdb(‘yourDBname’)
 ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
 ALTER DATABASE yourDBname SET MULTI_USER

Answer 2

Recommended actions for corrupt or suspect databases by Tibor Karaszi

http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

Related blog is available @
http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html

Answer 3

You need to software-based resolution. I should advise following application

http://www.sqlserver.fixtoolbox.com

tool allows you to recover sql files of all available SQL Server fomats

Comments

MDF file must be corrupted. You need to professional recovery software. Try to use next one http://www.recoverytoolbox.com/sql.html

Application will restore your mdf file and save its original structure before extracting

---

Stellar Phoenix has a tool to repair corrupt SQL server data files and recovers tables, stored procedures, triggers, views, indexes, keys, & many more. You can have this tool by visiting here: http://www.stellarsqldatabaserecovery.com/