March 08, 2011

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

22 Responses to "Repair SQL Server Database marked as Suspect or Corrupted"
  1. Anonymous said...
    March 9, 2011 at 8:55 PM

    :hugs: thank you thank you thank you Madhivanan

    -_- Nina

  2. Madhivanan said...
    March 10, 2011 at 4:07 AM

    Thanks Nina for the feedback

  3. Musab said...
    March 12, 2011 at 4:04 AM

    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 ,

  4. Musab said...
    March 12, 2011 at 4:05 AM
    This comment has been removed by the author.
  5. Madhivanan said...
    March 13, 2011 at 11:51 PM

    Musab,

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

  6. Anonymous said...
    April 4, 2011 at 5:28 PM

    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

  7. SQL Recovery said...
    April 8, 2011 at 12:43 AM

    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

  8. Anonymous said...
    May 4, 2012 at 6:25 AM

    Excellent post....

    Thank you!!

  9. prashant agrawal said...
    February 12, 2013 at 7:40 AM

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

  10. prashant agrawal said...
    February 12, 2013 at 7:40 AM

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

  11. Anonymous said...
    March 20, 2013 at 12:07 PM

    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

  12. Anonymous said...
    March 20, 2013 at 12:17 PM

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

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

  13. Fadric Smith said...
    May 4, 2013 at 12:21 AM

    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.

  14. Zahid Rahman said...
    August 11, 2013 at 9:50 PM


    Thanks for post your valuable opinion. I think it will be valuable for all kinds of Users.
    Please continue .
    Emergency Exchange Support

  15. Emran Tonmoy said...
    August 16, 2013 at 1:59 AM

    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

  16. Anonymous said...
    November 27, 2013 at 11:47 PM

    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.

  17. Passer said...
    November 27, 2013 at 11:52 PM

    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.

  18. kudalumping said...
    December 8, 2013 at 6:43 PM

    excellent post,thank you!

  19. Stanley D. Middleton said...
    February 18, 2014 at 3:20 AM

    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

  20. bumbu pecel bali said...
    April 15, 2014 at 10:10 AM

    this is good post...

    i like this...

    please can you visit here..

    http://bantalsilikon01.blogspot.com/
    http://bantalkekabumurah.blogspot.com/
    http://bantalkekabumurah.blogspot.com/
    http://bantalkekabumurah.blogspot.com/
    http://bantalsilikon01.blogdetik.com/bantal-silikon-original085-635-945-40/
    http://bumbupecel1.blogspot.com/
    http://bumbupecel1.blogspot.com/2014/04/bumbu-pecel-enak.html
    http://bumbupecel1.blogspot.com/2014/04/kami-menjual-bumbu-pecel-cabe-merah-dan.html

    http://marinirseo.blogspot.com/
    http://marinirseo.blogspot.com/
    http://marinirseo.blogspot.com/


    tengs very much...

  21. Công ty TNHH KTĐ Hoàng Phát said...
    May 17, 2014 at 7:16 PM

    very useful for me. Thank you very much

  22. Ellis White said...
    May 19, 2014 at 11:29 PM

    There is one more tool which helps you to repair database from suspect mode. http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions