sql database suspect

Did you try restarting the sql server service ?

Restart the SQL server. You can do it from MS by right clicking on the server name and then Stop follow by Start. I would prefer that to a Restart.

This was attempted with no success

You don't need, or want, to restart SQL Server in that situation.  That's a very bad idea that could cause lost data or corrupt the db beyond repair.

You need to check the error logs to get more details on what specifically happened to corrupt the db.  Hopefully you haven't restarted SQL so many times that the original log is no longer available.

Can't give you any more specific direction until we know WHY the db is corrupted, because different types of corruption have different fixes (or can't be fixed, depending on the problem).

check if you mdf ldf corrupted DB files are in the locations where they were allocated
in some cases Suspected when ldf is missed \corrupted  (bad drive; transaction interrupted by unexpected reboot, etc)

You should follow the advice of ScottPletcher, check the error logs to find out why SQL Server marked the database suspect.

If you post the parts of the log that show this, we can help you figure it out. If you restart SQL, SQL Server will just find the same thing wrong and mark it suspect, but may do more damage than good.

Always find out "why" SQL marked it suspect and go from there, you are much safer that way.

you may try this statement:

RESTORE DATABASE yourdatabase WITH RECOVERY; 

Select allOpen in new window

if that does not help, only a full restore will help
you may want to show the error(s) you get in the error log about this db

Hi Everyone,

Thank you for all the submissions.

I tried a number of he suggestions was unable to get any information regarding the cause or to resolve the 'suspect' flag.

Hence, we have resorted to restoring the database using BrightStor ARCServe 11.5 but we got this error message after a 2 hour period:
Failed to end restore. (DBNAME=restorebgdbms10 , EC ILLUMINA-AooD6F:[SQL Server] szSqlState = S1000 ,fNativeError = 823, errMSG = [Microsoft][ODBC SQL Servr Driver][SQL Server] I/O error (bad page ID) detected during read at offset 0x00000000012000 in file 'D:\SQL\bgdbms_20130122\\PrimaryGroup_01.mdf.')

Can anyone assist?

This would mean that you have corruption in your backup. You can use this option to help you get it restored but you will need to fix your database afterwards.
http://msdn.microsoft.com/en-us/library/ms190952(v=SQL.105).aspx

I was going off the phrase "bad page id". If there is corruption in the database and it is backed up, that corruption can exhibit itself just like this. But I suppose it could be a disk problem too.

start looking for a good backup file..  and you did not post your sql server version,...

I would still try the CONTINUE_AFTER_ERROR on the restore to see if you can determine if the bad page id is in an index that can be dropped and recreated.  This would prevent you from some data loss.  It could be that there is corruption and it may be table based, but it is still worth a shot.

:) it can be old sql server  version

sql version 2003

Please let me know how i can set the restore option continue_after_error? Reminder that i'm using arcserve 11.5

If you can do a SELECT @@version and post that, it will help us more.

As far as the CONTINUE_AFTER_ERROR, I am not sure how you would do that in Arcserve.

sql version 2003
You may want to double check that.  It is either 2000, 2005, 2008, 2008-R2 or 2012.

Have you tried to restore the backup on different server?

Sql Server 2003 may refer to the SQL server coming with SBS server 2003

correction after double check - sql version 2000

The server with the current sql database is installed on a drive with compression turned on. If i uncheck compression for that drive what are the implications for the existing database? Will it affect it? Do i need to have sql services turned off while the decompression is in progress?

As a general question can i have live transactions occuring on the sl database during a decompression?

Ah, good point.

That is correct.  SQL Server does not support compressed folders.  Let me know if you need documentation to back that up.