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

Improve this question
1
  • Why is it "unfortunately very necessary"?
    – Aaron Bertrand
    Commented Feb 7, 2017 at 13:45

2 Answers 2

Reset to default
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

Improve this answer
2
  • Have done that now @Scotthodgin - apologies for not doing so sooner I didn't realise that was a feature. My thinking is to do either of the following: 1: Adding a differential backup immediately after this job to retain the chain 2: Running this job immediately before the full backup at 1am, limiting the gap between the break in the chain and a full backup.
    – squizz
    Commented Feb 7, 2017 at 13:46
  • Adding a DIFF backup after changing back to FULL will re-establish the log chain for 'future' restores of T-logs, but you still will not be able to restore to a point in time BETWEEN after altering to SIMPLE and taking the DIFF. I would seriously question the reason for changing to SIMPLE as this opens to door to data loss. Also, depending on the activity in the database, I'd increase the frequency of T-log backups
    – Scott Hodgin - Retired
    Commented Feb 7, 2017 at 13:50
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?

Improve this answer
4
  • 1
    Actually, you don't need a FULL backup to re-establish the log-chain. According to Paul Randal, (sqlskills.com/blogs/paul/…) You can restart the log backup chain with either a full or differential backup – anything that bridges the LSN gap from the point at which the log backup chain was broken.
    – Scott Hodgin - Retired
    Commented Feb 7, 2017 at 12:49
  • @ScottHodgin thanks, edited answer to reflect. Wasn't sure on diffs so put them in with a proviso.
    – Gareth Lyons
    Commented Feb 7, 2017 at 12:50
  • 3
    @GarethLyons - The system has an inbuilt archiving/purging function which generates thousands of transactions in a very short space of time, whilst it's running it was recommended by the vendor to put the DB into simple recovery (we don't necessarily agree with it and have raised several questions but they maintain it is necessary - the power of the vendor in all it's hideous glory). Thank you for your comments, appreciate the assistance.
    – squizz
    Commented Feb 7, 2017 at 13:49
  • 1
    @squizz oh, how we love vendor recommendations :) The bulk-logged recovery model was designed with that kind of thing in mind technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx but equally may not help depending on what exactly is happening during that period. No problem & good luck!
    – Gareth Lyons
    Commented Feb 7, 2017 at 14:37

Your Answer

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

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