Database in suspect mode

Question

Database in suspect mode

apat

SSCertifiable

Points: 5929
More actions
May 4, 2010 at 2:31 pm

#218540

Experts,
My database is in suspect mode, I do have a full backup available for yesterday night. How do I recover my database? Can I now take backup log with no_truncate?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You won't be able to run a backup with the database suspect.

Check the SQL error log, there will be some message in there saying why the database is suspect. Post any errors that you find.

Restoring from last night's backup is certainly an option, may be the best one, will very likely be the fastest. You should still investigate what caused the DB to go suspect, as it may happen again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik -450760 Ten Centuries Points: 1362 More actions · Answer 2

apat (5/4/2010)
Experts,
My database is in suspect mode, I do have a full backup available for yesterday night. How do I recover my database? Can I now take backup log with no_truncate?

Hi,

Trying using DBCC Checkdb in the database..

Thanks,

Karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

karthik -450760 (5/4/2010)
Trying using DBCC Checkdb in the database..

It won't be possible to run checkDB while the database is suspect. That can only be run if the database is online/emergency.

Whether or not the database needs to be put into emergency mode or not depends on the reasons why the database is suspect in the first place. That's why we first need the errors from the error log before deciding on any further course of events.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sivark1 SSCrazy Points: 2654 More actions · Answer 4

Hi,

I tried this got sucess please try run below quries

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

Regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sivark1 (5/10/2010)
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

No! No! No!

That is terrible advice, it is not appropriate for all circumstances and it may very well cause data loss. The above commands are a last resort, not the first thing that should be recommended or tried. It's for when there's no backup and no other way to bring the database back.

The first thing that should be done with a DB in suspect state is to identify why it is suspect. Once that has been ascertained, then an appropriate strategy can be formulated for fixing the problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

khursheed.siddiqui Old Hand Points: 312 More actions · Answer 6

apat (5/4/2010)
Experts,
My database is in suspect mode, I do have a full backup available for yesterday night. How do I recover my database? Can I now take backup log with no_truncate?

Coach is right. You should investigate why the database got put into suspect status by reviewing Logs. If that fails you should move on the other option.