How to Recover Suspected Database in SQL Server 2000

If a database is marked suspect it cannot be accessed and hence nothing can be performed in the database. A database can be marked suspect for one of the following reasons,

a.If one or more database files are not available.
b.If the entire database is not available.
c.If one or more database files are corrupted.
d.If a database resource is being held by the operating system.

In order to recover the suspected database, sysdatabases status is modified in master database.Before you do any changes with system table run the following command to enable adhoc changes to the system tables.

use master

go

sp_configure allow, 1

go

reconfigure with override

go

sp_resetstatus ‘DatabaseName’

go

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

sp_configure 'allow updates', 0

GO

RECONFIGURE WITH OVERRIDE

GO

Then Stop and Restart ALL SQL Server Services.

If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into Emergency Mode. If you do this, extract the data/objects out with DTS and rebuild the database.

The following command to put the database into emergency mode

USE master

GO

sp_configure 'allow updates', 1

GO

RECONFIGURE WITH OVERRIDE

GO

UPDATE master..sysdatabases SET status = 32768 WHERE name = 'DatabaseName'

GO

sp_configure allow, 0

go

Stop and Restart ALL SQL Server Services.

We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS using EnterprisesManager.

These are the steps necessary to export data out of the corrupt database into the new:

a. Create a new production DB, or a temp DB, to have a place to export whatever data we can out of the corrupt db.

b. Start a DTS operation by going into EM and drilling down to “Data Transformation Services” … “Local Packages”.

c. Open a NEW DTS package by right-mouse clicking …

d. When DTS opens, choose “Copy SQL Server Objects Task” from the Connection Icons. Enter in a description like “Export Corrupt Data”. Enter in the SA/pass combination as well as the CORRUPT database from the drop-down.

e. Select the “Destination” Tab. ”. Enter in the SA/pass combination as well as the PRODUCTION database from the drop-down.

f. Select the “Copy” Tab. UNCHECK the “Create destination objects” box. UNCHECK the “Copy all objects” box and then Click on the “Select Objects” Button. This brings up the “Select Objects” screen.

g. CHECK ONLY “Show all tables” like shown above. Then check each table that needs to be exported. If ALL tables need to be export, Click on the “Select All” button. Click OK.

h. Click OK again and we are done creating this task. Now we execute the package by Clicking the green arrow on the menu bar.

REF : http://www.tek-tips.com/