Scripting a change in the recovery model

Hi,
Is there a way that I can automatically switch my database recoverymodel
from Simple to Full and vice versa so that I can plan this swict through
Scheduled tasks?Hi
Execute a T-SQL task in a agent job.
Just be aware, changing the recovery mode affects your ability to do point
in time restores, so after you set the mode back to full, do a full DB
backup otherwise your subsequent transaction log backups are worthless.
Why would you want to do such a thing as it does not affect how SQL Server
uses the log? Everything is still logged, you just throw away
recoverability.
Full recovery:
ALTER DATABASE <dbname> SET RECOVERY FULL
and back to simple:
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"altruïst" <altrust@.discussions.microsoft.com> wrote in message
news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> Hi,
> Is there a way that I can automatically switch my database recoverymodel
> from Simple to Full and vice versa so that I can plan this swict through
> Scheduled tasks?|||Thx Mike
I want to change it because every weekend between 02:00 and 04:00 the
customer sends a huge job that completely eats out my transaction log disk
(60GB)
I will try your solution.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Execute a T-SQL task in a agent job.
> Just be aware, changing the recovery mode affects your ability to do point
> in time restores, so after you set the mode back to full, do a full DB
> backup otherwise your subsequent transaction log backups are worthless.
> Why would you want to do such a thing as it does not affect how SQL Server
> uses the log? Everything is still logged, you just throw away
> recoverability.
> Full recovery:
> ALTER DATABASE <dbname> SET RECOVERY FULL
> and back to simple:
> ALTER DATABASE <dbname> SET RECOVERY SIMPLE
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "altruïst" <altrust@.discussions.microsoft.com> wrote in message
> news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> > Hi,
> >
> > Is there a way that I can automatically switch my database recoverymodel
> > from Simple to Full and vice versa so that I can plan this swict through
> > Scheduled tasks?
>
>|||altruïst wrote:
> Thx Mike
> I want to change it because every weekend between 02:00 and 04:00 the
> customer sends a huge job that completely eats out my transaction log disk
> (60GB)
>
You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
between 02:00 and 04:00 on weekends only.|||Hi
If it is one job and one batch, it might be one big transaction, and in that
case, until the transaction is committed or rolled back, the log info stays
in the log and can't be released. Check the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:haepf.39012$L7.9595@.fe12.lga...
> altruïst wrote:
>> Thx Mike
>> I want to change it because every weekend between 02:00 and 04:00 the
>> customer sends a huge job that completely eats out my transaction log
>> disk (60GB)
> You could create another maintenance plan that only did transaction log
> backups and schedule it to run very frequently between 02:00 and 04:00 on
> weekends only.
>|||I tried this option but it didn't work...
It filled up anyway.
"Ed Enstrom" wrote:
> altruïst wrote:
> > Thx Mike
> >
> > I want to change it because every weekend between 02:00 and 04:00 the
> > customer sends a huge job that completely eats out my transaction log disk
> > (60GB)
> >
> You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
> between 02:00 and 04:00 on weekends only.
>