Repair SQL Server Database marked as Suspect or Corrupted

There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

  1. Reset the suspect flag
  2. Set the database to emergency mode so that it becomes read only and not accessible to others
  3. Check the integrity among all the objects
  4. Set the database to single user mode
  5. Repair the errors
  6. Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

repairdatabase

Here’s the same code for you to try out

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

25 comments:

Anonymous said...

:hugs: thank you thank you thank you Madhivanan

-_- Nina

Madhivanan said...

Thanks Nina for the feedback

Unknown said...

thanks dear , actually i facing this issue .. i wondering before do any action , is there any risk for data when i executing this solution ?
and should the SQL agent be stoped ?

message : Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)

SQL Server 2008

wating for ur reply ?
Best Regareds ,

Unknown said...
This comment has been removed by the author.
Madhivanan said...

Musab,

You need to repair it before using the database. Follow the article

Anonymous said...

my problem is with MS-SQL 2005 and MSDB is marked corrupted and backup is missing at failure hard disk.
its posible recovery msdb?

Joe

joebanbino@yahoo.com

SQL Recovery said...

Hello Dear,

Well written, i m a product reviewer and i also write a article on the same topic. if you interested then check here

http://sql-server-recovery.blogspot.com/2010/10/repair-restore-sql-server-database-from.html

Anonymous said...

Excellent post....

Thank you!!

Prashant Agrawal said...

hi after executing this query i m getting an error "Option 'EMERGENCY' cannot be set in database 'msdb'"
plz help

Prashant Agrawal said...

Option 'EMERGENCY' cannot be set in database 'msdb'
i m getting this error after executing this query

Anonymous said...

Hello, I have a sql 2000.

Usually I have 10 files in my Data Tranformation Services, Local Packages. I have lost those files. and I'm having this message when i tried to restore.

Restore Database:

Error 926: Database MSDB cannot be opened. it has been marked suspect by recovery. See the SQL Server errorlog for more informaiton

Anonymous said...

I have just notice that I have msdb (suspect).

What this mean, can this erase or remove my dts services files.

Unknown said...

Repair SQL server database form corrupted SQL server 2000, 2005 and 2008 with help of SQL server recovery software which is especially designed for recovery of SQL server.

Unknown said...

Great article. I am fully impressed.
LG Networks, Inc. is a Microsoft Certified and a leading Microsoft Exchange Server Consulting firm.
Our Emergency Exchange Support consultants can help you attain the maximum benefit and stability from
Microsoft Exchange Server with administrative, technical and onsite or remote troubleshooting support.
Waoou... What a tutorial you made? I think any body will be clear after seen this. Just not only looking nice , also
more helpful.
Emergency Exchange Support

Anonymous said...

It's work well for me.

1. Stop SQL Server service.
2. Rename or remove old files named MSDBData.mdf, MSDBLog.ldf in directory [SQL Server Instance name]\MSSQL\DATA (exsample C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL\DATA).
3. Copy files named MSDBData.mdf, MSDBLog.ldf in [SQL Server Instance name]\MSSQL\Template Data\ (exsample C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL\Template Data).
4. Restart SQL Server Service.

Passer said...

It's work well for me (Fixed)
1. Stop SQL Server service.
2. Rename or remove old files named MSDBData.mdf, MSDBLog.ldf in directory [SQL Server Instance name]\MSSQL\DATA (exsample C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL\DATA).
3. Copy files named MSDBData.mdf, MSDBLog.ldf in [SQL Server Instance name]\MSSQL\Template Data\ (exsample C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008\MSSQL\Template Data)
to [SQL Server Instance name]\MSSQL\DATA.
4. Restart SQL Server Service.

kudalumping said...

excellent post,thank you!

Unknown said...

One more software which you can use at free of cost to see preview of recovered sql data. Here is the link to download the software and use the free trial version.
http://www.recoverfilesdata.com/sql-database-mdf-recovery.html

Công ty TNHH KTĐ Hoàng Phát said...

very useful for me. Thank you very much

Unknown said...

It works thank you

Unknown said...

superb very helpfull to me.thanks....

Deep said...

perfect post.. very helpful. Thanks alot.

Unknown said...

Excelente.

mayore said...

Thank you for post. It help me.

amit said...

perfect solution .. 100/100 for the author. This worked for me like charm