I have a Problem in My Database it's showing suspect i havetried by usng readonly mechanisum but data cannto be retreived guys is there any tools or any other redgate tools availble for me to recover data

Hi Charith,

There are many reasons for a database to become suspect. It is difficult to think of all reasons.  Please check on SQL Server error log and Windows Event Viewer for any possible clues.  They will carry some information. Some of the common reasons are

1.  Missing files. Check whether all the files related to that database are available?

2. SQL Server is unable to access the files.  Any permission changed to the drives where the data files are loaded?

3. The database is in the middle of a recovery process. Did you shutdown your server in the middle of a big transaction?  

If you can't find any reason, If your data and mdf files are available you can shut down the server (I eman SQL Server service; not the computer) and copy the files to another directory and try.

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

I pressed the send key too fast.  :)

If all the above options fail,  you can try this:

 USE master

GO

ALTER DATABASE <DBName> SET EMERGENCY

It allows you to read the database.

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

hi Preethiviraj,
thanks for u r comments , i have enabled the emrgency mode it's in readonly mode as i said i can recover some data in some of the tables but icoudnt read from Item master file and supplier master file and also invoice table,but when i open item master in enterprise manager i can read few record .but not all. 
charith

Hi Charith, 

You have not specified whether you are using multiple files the in file group. I believe you have only one file.  It is now clear that your data file (or one of the files) is corrupted. 

Do you have any old backup with Item master and supplier master data?  As these tables do not get data much on a daily basis (you may update balances which you can do anyway) you may be able to recover most of the data.  Can you write any select statements that may read data only from indexes? These techniques may help you to read some data from all rows.

If all optional Fail, and you cant do anything, try these commands. I suggest this as last option only. As you are already in Emergency mode, if these steps fail you have no other option. 

 

I added <tablename> which you need to replace with ItemMaster and SupplierMaster

DBCC CHECKTABLE <TableName>

DBCC CHECKTABLE <tablename>, REPAIR_REBUILD

DBCC CHECKTABLE <tablename>, REPAIR_ALLOW_DATA_LOSS --  this is the last option.  the database may repair what ever possible,

After each command try to read data and see whether you can get data.

BTW,  On one of my last posts I have mentioned that, this can occur when database is in recovery process.  I am sorry, it is not correct. If the database is in recovery mode, you may see either RECOVERING and RECOVERY_PENDING modes if the database tries to recover. USPECT means something to do with data or log files.  At least one of them is missing, unable to access, Corrupted and so on.

 

This raises another question: As your data file is possibly corrupted, how reliable the disk is?  You may need to check the disk and checking for a reliable solution.

Hope this answer helps.  In case of any clarification on DBCC commands check on books online as well.

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!