Restoring SQL Server 2005 Suspect Database MSDB

Restoring SQL Server 2005 Suspect Database MSDB

I as able to restore my MSDB jobs without creating a new MSDB.
Here is what I have done, probably help them lying in the dark struggling with this kind of issue:

Problem :

My SQL 2005 MSDB database was put into SUSPECT, bcoz of abrupt server boot, that has many Sprocs and Jobs. I have no backup of MSDB, hence there is no way of restoring the backup to re-create MSDB.

Solution:

1. Stop the SQL service > Copy the corrupt MSDB MDF data file (only MDF ) from its location onto a different drive/ location > Start the SQL service > Execute the below SQL query to create a User DB with this available MDF file

CREATE DATABASE User_MSDB ON
(NAME=DFT_MSDB , FILENAME='D:\NonSQLDefaultLocation\msdbdata.mdf') FOR ATTACH_REBUILD_LOG

This will create a User database with all the database files @ 'D:\NonSQLDefaultLocation\'

2. Now, take a backup of this User_MSDB database
BACKUP DATABASE User_MSDB TO DISK='D:\NonSQLDefaultLocation\User_MSDB.Bak'

3. Restore the MSDB database with the backup of User_MSDB , the one we have taken in the 2nd step.
RESTORE DATABASE MSDB

FROM DISK='D:\NonSQLDefaultLocation\DFT_MSDB.BAK'
WITH REPLACE

Whoa...you are back with your MSDB data.
Restoring SQL Server 2005 Suspect Database

I had a SQL Server 2005 database in suspect mode,so i couldn't work on transaction until i repaired,while i am searching on the internet to resolve this problem i found an new Database status called Emergency introduced in SQL Server 2005.

This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.

Please follow the following steps to change the database status from suspect to emergency than to it's normal state:

EXEC sp_resetstatus 'DBname'
ALTER DATABASE DBname SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DBname SET MULTI_USER

I hope that will help you to get your database working

Ex:
ALTER DATABASE "WSS_Content_0dc82740ef0841f5853eec5e8aeffdae" SET EMERGENCY
DBCC checkdb('WSS_Content_0dc82740ef0841f5853eec5e8aeffdae')
ALTER DATABASE "WSS_Content_0dc82740ef0841f5853eec5e8aeffdae" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('WSS_Content_0dc82740ef0841f5853eec5e8aeffdae', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE "WSS_Content_0dc82740ef0841f5853eec5e8aeffdae" SET MULTI_USER