Can I switch recovery model from full to simple and back to full

Here is my scenerio: A developer wants to switch recovery modes from Full
to Simple and then back to Full after his nightly batch cycle for two
databases. Will I have to take two backups a Full at the start of the
nightly batch cycle and then another backup after switching to Simple, right?
Is this possible and will I be able to recover successfully? and what are
the pros and cons? Does anyone have a better idea?
Thanks,
Katherine
The recovery mode will be switched during the STARS/TMS Batch Cycle from
“Full” to “Simple” and then back to “Full” after the successful completion of
the batch processes. Changing the database mode will require that there are
two daily back-up so that in a disaster the transaction logs can be applied.
If you switch to simple and then back to full, then SQL Server would have truncated the log while in
simple. This mean that if you do log backups when back in full, you cannot use those log backups.
You first have to do a db backup (after going back to full) and then the log backups you do after
that will be usable. In essence, you have lost point in time restore possibility for the time from
the last log backup before switching to simple until the first log db backup after going back to
full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple, right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> “Full” to “Simple” and then back to “Full” after the successful completion of
> the batch processes. Changing the database mode will require that there are
> two daily back-up so that in a disaster the transaction logs can be applied.
|||The full database backup before the recovery model change is redundant. One
method to address recovery in your scenario is:
- backup log
- change recovery model to SIMPLE
- execute batch process
- change recovery model back to FULL
- backup database
This will allow you to recover to any point in time from the last full
backup until the change to SIMPLE recovery. Your potential data loss are
changes during the batch process (between the SIMPLE recovery model change
and the database backup).
Hope this helps.
Dan Guzman
SQL Server MVP
"Katherine" <Katherine@.discussions.microsoft.com> wrote in message
news:A342DF5F-8A78-4667-9A6F-28C6FCE9057C@.microsoft.com...
> Here is my scenerio: A developer wants to switch recovery modes from
> Full
> to Simple and then back to Full after his nightly batch cycle for two
> databases. Will I have to take two backups a Full at the start of the
> nightly batch cycle and then another backup after switching to Simple,
> right?
> Is this possible and will I be able to recover successfully? and what are
> the pros and cons? Does anyone have a better idea?
> Thanks,
> Katherine
> The recovery mode will be switched during the STARS/TMS Batch Cycle from
> "Full" to "Simple" and then back to "Full" after the successful completion
> of
> the batch processes. Changing the database mode will require that there
> are
> two daily back-up so that in a disaster the transaction logs can be
> applied.