5

I have recently inherited all of our company's SQL databases without much warning or experience, and was hoping to make a good impression and score a few quick wins by completely reviewing backups across the board.

I've implemented various full and t-log backups but have come across another job on this box that sets the DB to simple recovery mode prior to running, then back to full afterwards, which is unfortunately very necessary.

My question is, if a full backup is taken at 1am, and t-log backups are taken at the top of every hour after that, does this other job setting simple recovery mode at 5:30am ruin the "timeline" as such, between the full backup and the next t-log backup at 6am?

It's SQL Enterprise 2012 for this particular machine.

Appreciate any insight you guys have

Ian_H
  • 1,674
  • 10
  • 17
squizz
  • 53
  • 5

2 Answers2

7

Merely setting the recovery back to FULL is not enough - you'd need to take at least a differential after setting back to FULL to bridge the gap between the time you changed to SIMPLE and the time you changed back to FULL - then your T-logs should be ok at 6am

Keep in mind that you will not be able to restore to a point in time after the change to SIMPLE and before the differential is taken.

Also, you say that you are taking T-log backups at the top of every hour. So, you are basically saying that it's ok to lose up to 1 hour of data should a disaster occur. Does this actually adhere to your company Recovery Point Objective (RPO)? Please take a look at The 9 Letters That Get DBAs Fired.

And then take a look at Back Up Transaction Logs Every Minute. Yes, Really

Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52
2

"does this other job setting simple recovery mode at 5:30am ruin the "timeline" as such, between the full backup and the next t-log backup at 6am?"

Yes.

Also, when moving from simple to full model, the database effectively runs in pseudo-full mode (= the tran log behaves as if it was still in simple recovery) until a full or differential backup is taken. Any log backups taken between then and the next full or differential backup will be essentially worthless. In fact, they can be detrimental if they're giving you a false feeling that your backups are functional.

"another job on this box that sets the DB to simple recovery mode prior to running, then back to full afterwards, which is unfortunately very necessary"

Got to ask, why?

Gareth Lyons
  • 1,158
  • 8
  • 13