Repair SQL database that has been marked suspect

Question

 

I woke up this morning to find my company's SQL database inaccessible and marked suspect.

 

I have used various t-sql commands to bring it into Emergency mode.

 

Now it is labelled Loading\Offline\Read-Only\Emergency Mode.

 

I'm using SQL Server 2000

 

I do not have  a backup and I need to get the database up and running as soon as yesterday.

 

Help

Answer 1

first of all what version of SQL Server are u using?

 

Second ..There can be many reason for database being suspect.Please see in to the SQL Server error log and send if you see something unusual/error .It is very importent to look in to error log before anything can be done

 

run this on master

select status from sysdatabases and send it.

 

Thanks

Answer 2

Recommended actions for corrupt or suspect databases

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

 

 

AMB

Answer 3

it is very tough to get a suspect db back. try this link http://www.myitforum.com/articles/18/view.asp?id=7381

 

Madhu

Answer 4

As Madhu K Nair says "it is very tough to get a suspect db back". but even though there is one way to get your data back. Try third party SQL recovery and repair tools. These are powerful tool  that helps to recover and repair corrupted data.

Answer 5

The main cause of the above error message is that the SQL database has been suspected. This situation occurs under following conditions:
• When the error log file does not match with the database file
• SQL database corruption

When the user views the SQL Server application event log, he finds the following entry:

“The log scan number (152:284:1) passed to log scan in database 'msdb' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.”

Once the above error message appears on the screen, SQL database becomes inaccessible. To resolve the above problem, the user can use an inbuilt tool named sp_resetstatus. This tool enables the user to change the SQL database status and access the SQL database again. But in this case, this inbuilt tool will not be able to resolve the issue. This tool will only allow the user to connect to the SQL Server instances and will let you access other databases. To resolve the above error message and perform complete sql database recovery .

---

David Poul http://www.buy-online-software.com/database-recovery.html

Answer 6

Hello,

Check the following article How to repair a Suspect Database in SQL Server to repair a SUSPECT database.

Cheers,
Ashish

Answer 7

Check out the following page for SQL Server on this topic:

http://sqlusa.com/bestpractices2005/markedsuspect/

Periodic backups offer the "ultimate" protection:

Introduction to Backup and Restore Strategies in SQL Server

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 8

Yes, it's certainly true that regular backups are the best practice...but it seems to me that if somebody already has a database flagged as "Suspect" that it's a little late for that particular solution.

 

---

-B-
http://www.officeforlawyers.com
Author: The Lawyer's Guide to Microsoft Outlook

Answer 9

Check Out this article http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html to repair suspect SQL database.

---

Read my blog http://sql-server-recovery.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

Answer 10

There can be reasons that the database becoming suspect which leads to corruption in the user database and master database present in the SQL server. mdf file and .ldf file which is corrupted can be recovered by SQL database repair process and retreive MDF file and LDF files from suspect condition. Visit: http://www.repairsqldatabase.org/repairsqldatabase-fromsuspectmode

Answer 11

I recommended SQL database repair application, you can use this program which destructs all corruption issues and repair SQL database from suspect mode.