Urgent,pls tell how to recover database,databases is showing SUSPECT database

Hi,
I m working on sql server 2005,till ysterdy my database was ok but today when i saw in the morning the database is not getting open. I have 4 databases out of four my main database has got currupted, Its showing SUSPECT e.g my database is Accounts,in sql server in databases its showing Accounts(SUSPECT)
I have 500 MB data.
Is there any way to recover data??????
pls tell.

Thanks In Advance.

Mehul

hi mehul....

check this out

Follow these steps to resolve the problem:

Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server. With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.

sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.

Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.

Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:

USE master

GO sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO

After the procedure is created, immediately disable updates to the system tables:

sp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO

sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

The syntax is:

sp_resetstatus database_name

This example turns off the suspect flag on the PRODUCTION database.

sp_resetstatus PRODUCTION

Here is the result set:

Database 'PRODUCTION' status reset!WARNING: You must reboot SQL Server prior to accessing this database!

thanx

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Brij19y ago
hi,

another solution

Run recovery by executing DBCC DBRECOVER (database).

Add a log file to the suspect database and run recovery on the database by executing sp_add_log_file_recover_suspect_db.

thanx
0
Brij19y ago
hi mehul....

check this out

Follow these steps to resolve the problem:
- Execute sp_resetstatus.
- Use ALTER DATABASE to add a data file or log file to the database.
- Stop and restart SQL Server. With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
- Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.

Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.

Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:

USE master

GO sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO

After the procedure is created, immediately disable updates to the system tables:

sp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO

sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

The syntax is:

sp_resetstatus database_name

This example turns off the suspect flag on the PRODUCTION database.

sp_resetstatus PRODUCTION

Here is the result set:

Database 'PRODUCTION' status reset!WARNING: You must reboot SQL Server prior to accessing this database!

thanx
0