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/
"altrust" <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...
>
>|||altrust 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 back
ups 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...
> altrust wrote:
> 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:
> You could create another maintenance plan that only did transaction log ba
ckups and schedule it to run very frequently
> between 02:00 and 04:00 on weekends only.
>

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/
"altrust" <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...
>
>
|||altrust 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...
> altrust wrote:
> 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:
> 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.
>

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.
>

Script to enumerate Recovery mode

Hi, I search a script that report me a list of all my several DB on my SQL
Servers, with which Recovery model they have (simple,full or bulked). Did
somebody have something? ThanksMV
select name,databasepropertyex(name,'recovery') from master..sysdatabases
"MV" <MV@.MV.MV> wrote in message
news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi, I search a script that report me a list of all my several DB on my SQL
> Servers, with which Recovery model they have (simple,full or bulked). Did
> somebody have something? Thanks
>|||Hello,
YOu could also use:-
sp_helpdb
THanks
Hari
"MV" <MV@.MV.MV> wrote in message
news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi, I search a script that report me a list of all my several DB on my SQL
> Servers, with which Recovery model they have (simple,full or bulked). Did
> somebody have something? Thanks
>|||I you need precise information like only recoverymodel then use Uri's query
statement if you need complete details Hari's SP_HELPDB will be useful.
"Hari Prasad" wrote:
> Hello,
> YOu could also use:-
> sp_helpdb
> THanks
> Hari
> "MV" <MV@.MV.MV> wrote in message
> news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
> > Hi, I search a script that report me a list of all my several DB on my SQL
> > Servers, with which Recovery model they have (simple,full or bulked). Did
> > somebody have something? Thanks
> >
>
>

Script to enumerate Recovery mode

Hi, I search a script that report me a list of all my several DB on my SQL
Servers, with which Recovery model they have (simple,full or bulked). Did
somebody have something? ThanksMV
select name,databasepropertyex(name,'recovery')
from master..sysdatabases
"MV" <MV@.MV.MV> wrote in message
news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi, I search a script that report me a list of all my several DB on my SQL
> Servers, with which Recovery model they have (simple,full or bulked). Did
> somebody have something? Thanks
>|||Hello,
YOu could also use:-
sp_helpdb
THanks
Hari
"MV" <MV@.MV.MV> wrote in message
news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi, I search a script that report me a list of all my several DB on my SQL
> Servers, with which Recovery model they have (simple,full or bulked). Did
> somebody have something? Thanks
>|||I you need precise information like only recoverymodel then use Uri's query
statement if you need complete details Hari's SP_HELPDB will be useful.
"Hari Prasad" wrote:

> Hello,
> YOu could also use:-
> sp_helpdb
> THanks
> Hari
> "MV" <MV@.MV.MV> wrote in message
> news:uKu5FP7PHHA.4824@.TK2MSFTNGP02.phx.gbl...
>
>