Post reply

msdb and recovery model on SQLAGENT start

  • June 12, 2003 at 9:47 am

    #65297

    I have set msdb to Full recovery but whenever SQLAGENT Service is closed and restarted, msdb changes back to SIMPLE recovery ! How can I build a script that changes the recovery back to Full and then link it to SQLAGENT service start up ?

    Thanks,

    Mike

  • June 12, 2003 at 4:09 pm

    #461164

    ALTER DATABASE [msdb] SET RECOVERY Full

    This will change the recovery model.

  • June 12, 2003 at 4:33 pm

    #461174

    Create a batch program that calls OSQL.EXE to alter your MSDB recovery mode. Use SC.EXE to configure the batch program as a service with automaticallt starting. And the configure this new service to depend on SQL Server Agent service.

    I haven't configure a batch program as a service but I think it shouldn't be a issue.

  • June 13, 2003 at 1:01 am

    #461208

    I've solved this problem by creating a job which changes the recovery model and makes a full backup every time SQL Agent starts. Just select this option in your schedule.

    [font="Verdana"]Markus Bohse[/font]

  • June 13, 2003 at 1:27 am

    #461212

    MarkusB,

    I hadn't noticed that option on Job schedule.

    That's exactly what I wanted.

    Thanks,

    Mike

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

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