2

Recovery mode is set to FULL, and FULL backup is taken on alternate days. No log/differential backups. Log file size is growing too big. I've shrank log file (googled) a week back and was fine. Now the file is growing again.

What if we set Recovery mode to SIMPLE and take FULL backup on alternate days (okay with losing 1 or 2 days data if any crash happens).

Will it be fine? Will I be able to restore the Last FULL backup without any issues?

Or keep the Mode to FULL only take FULL backup once a week and take log backups on alternate days? please suggest.

PS: I'm a desktop support tech, apologies if any mistakes in the question

1
  • 4
    Why aren’t you doing transaction log backups? Oct 31, 2021 at 11:39

1 Answer 1

3

What if we set Recovery mode to simple and take full backup alternate days(okay with losing 1 or 2 days data if any crash happens). Will it be fine? I will be able to restore the Last full backup with out any issues?.

If you are OK with losing 1 or 2 days of data if any crash happens, then there should be no problem in switching Recovery model to Simple. And yes you will be able to restore the Last full backup without any issues

Or keep the Mode to full only take full backup once a week and take log backup on alternate days? please suggest

Full recovery model is needed when you want to have ability to recover to a point in time (for example to ~1 minute before crash happened, if log backups are taken every 1 min). In fact, Full recovery model is recommended for most if not all Production environments.

In Full recovery model you need to schedule regular log backups, and if nothing delays log truncation, log files should not grow constantly. But you will also have to account for log backup size growth, and truncate it from time to time (backup log with INIT/FORMAT)

So, as you see Full Recovery model have some administrative overhead, and if you are OK with losing 1-2 days of data, you can switch to Simple and take full backups every other day

There are still gotchas, so you may want to read below (or may not want :) )

Beware that in Simple model, transaction log is still used, it is just truncated after every transaction (Checkpoint happens). If your transactions (inserts/updates/deletes/etc.) are not too big - you won't see any more transaction log file growth (if you have pre-grown log file to adequate size for your workload). But if you run some big transactions at some point (huge index rebuilds, import data etc.), these have possibility to grow log again. Log file will be truncated after transaction, so inside the file it will be empty, but the file will stay big

3
  • 2
    Checkpoint does not happen after each transaction, just periodically, see learn.microsoft.com/en-us/sql/relational-databases/logs/… Good answer anyway Oct 31, 2021 at 10:39
  • Rhe answers given are to the point. Just to phrase it differently. If you want to do log backups, then use full revovery model. If not, then use simple. But make sure that your organization are ok with losing one day worth of work (or whatever your full backup s hedule). Very sure. Very very sure. Oct 31, 2021 at 18:12
  • @TiborKaraszi yes sure about losing data of one or two days
    – Baig
    Oct 31, 2021 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.