Post reply

From full to simple mode

  • 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

  • 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

  • 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

  • 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

  • June 4, 2006 at 5:46 pm

    #641594

    Thanks. That was very helpful.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply