From full to simple mode

• tosscrosby

  Hall of Fame

  Points: 3140

  More actions

  May 31, 2006 at 2:43 pm

  #97081

  Is there a way to tell when and who changed a database setting from full mode to simple? I have a production database that was in full at 2:00 p.m. today as my log backed up at that time. My 3:00 p.m. job failed so I went looking and found it was now set to simple. There isn't anything in the SQL logs indicating a change but it was definitely done within a one hour span.

  
  Terry

• Prakash Sawant

  SSCrazy

  Points: 2173

  More actions

  June 1, 2006 at 3:14 am

  #641116

  If trace is enabled on the server then  you can find from that.

   

  ------------
  Prakash Sawant

  http://psawant.blogspot.com

• Tejwant Kaur-333539

  SSCommitted

  Points: 1647

  More actions

  June 1, 2006 at 6:45 pm

  #641336

  I've had a similar thing happen to me. At the time there was a shrink database job that ran and then next day the recovery mode was changed to simple. I am not sure if it does that because it needs exclusive access in some way, but yes, it has certainly happened to me as well. I tried to find if there was a script I could use to change the setting back, but could not get an answer.

  However, at that time I did not know about SQL server central, so I have some hope of getting the answer for that. Does anyone know how to change it back through scripting?

  Regards,

  TK

• gcp777

  Ten Centuries

  Points: 1079

  More actions

  June 1, 2006 at 7:42 pm

  #641342

  Easy:

  ALTER DATABASE Mydatabase SET RECOVERY FULL

  ALTER DATABASE Mydatabase SET RECOVERY SIMPLE

  ALTER DATABASE Mydatabase SET RECOVERY BULK_LOGGED

• Tejwant Kaur-333539

  SSCommitted

  Points: 1647

  More actions

  June 4, 2006 at 5:46 pm

  #641594

  Thanks. That was very helpful.