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
Best Answer
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