locked
how to trigger database in SUSPECT mode and get it out from SUSPECT mode. RRS feed

  • Question

  • hello all,

         i am nre person to SQL server and want to learn about it.

        can any one tell me how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.

       i am using SQL server 2008 R2

    thank you.

       

        

    Monday, July 27, 2015 3:42 AM

Answers

  • Hi,

    If you go ahead and search Paul Randal articles you can find lot of sample corrupt database.

    Corruption Demo databases and scripts

    Sample corrupt database to play with

    *DO ON YOUR OWN RISK*

    If you want to create corrupt database

    1. Stop SQL Server service

    2. Locate database mdf file and open it in notepad

    3 Add some words or character in notepad randomly or delete some character

    4. Save it as original mdf file

    5. Now start SQL Server you would see suspect before database name in SSMS


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by pituachMVP Monday, July 27, 2015 8:32 AM
    • Marked as answer by Eric__Zhang Tuesday, August 4, 2015 12:48 AM
    Monday, July 27, 2015 4:24 AM
  • What we have done without Paul Randal :-)
    He is the father of "DBCC PAGE" I think

    I think that he also explained how to create corrupt database using dbcc WRITEPAGE. I think that this is better solution then using the Notepad, since you know exactly what you have change (you can change specific value in table for example), But I have to mentioned that ALL THESE OPTIONS SHOULD NEVER BE DONE ON PRODUCTION SERVER!

    UPDATE: here is Paul S. Randal's article on DBCC PAGEWRITE, which is great tool to create corrupt database and control the corruption (actually it is meant to be tool to fix corrupt database): 
    http://www.sqlskills.com/blogs/paul/dbcc-writepage/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • Edited by pituachMVP Monday, July 27, 2015 8:54 AM
    • Proposed as answer by Naomi N Monday, July 27, 2015 12:42 PM
    • Marked as answer by Eric__Zhang Tuesday, August 4, 2015 12:48 AM
    Monday, July 27, 2015 8:39 AM

All replies

  • if you want to do some repairs etc you need to change the database status to emergency not suspect.

    If some thing wrong identified by your sql engine then it will set the status to suspect. to do some admin or repair tasks you need to set the database to emergency.

    ALTER DATABASE dbName SET Emergency

    Monday, July 27, 2015 3:54 AM
  • hello all,

         i am nre person to SQL server and want to learn about it.

        can any one tell me how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.

       i am using SQL server 2008 R2

    thank you.

       

        

    advice extreme caution and do not do this on prod server. 

    refer this video and blog post by jes

    http://www.brentozar.com/archive/2014/11/corrupt-database-purpose-video/


    Hope it Helps!!

    Monday, July 27, 2015 4:00 AM
  • Hi,

    If you go ahead and search Paul Randal articles you can find lot of sample corrupt database.

    Corruption Demo databases and scripts

    Sample corrupt database to play with

    *DO ON YOUR OWN RISK*

    If you want to create corrupt database

    1. Stop SQL Server service

    2. Locate database mdf file and open it in notepad

    3 Add some words or character in notepad randomly or delete some character

    4. Save it as original mdf file

    5. Now start SQL Server you would see suspect before database name in SSMS


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by pituachMVP Monday, July 27, 2015 8:32 AM
    • Marked as answer by Eric__Zhang Tuesday, August 4, 2015 12:48 AM
    Monday, July 27, 2015 4:24 AM
  • What we have done without Paul Randal :-)
    He is the father of "DBCC PAGE" I think

    I think that he also explained how to create corrupt database using dbcc WRITEPAGE. I think that this is better solution then using the Notepad, since you know exactly what you have change (you can change specific value in table for example), But I have to mentioned that ALL THESE OPTIONS SHOULD NEVER BE DONE ON PRODUCTION SERVER!

    UPDATE: here is Paul S. Randal's article on DBCC PAGEWRITE, which is great tool to create corrupt database and control the corruption (actually it is meant to be tool to fix corrupt database): 
    http://www.sqlskills.com/blogs/paul/dbcc-writepage/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    • Edited by pituachMVP Monday, July 27, 2015 8:54 AM
    • Proposed as answer by Naomi N Monday, July 27, 2015 12:42 PM
    • Marked as answer by Eric__Zhang Tuesday, August 4, 2015 12:48 AM
    Monday, July 27, 2015 8:39 AM