asked
Maintenance Plan
We have an EHR vendor that creates their own maintenance plans. They have a job that starts at Midnight that essentially flips the switch from Full to simple recovery model Then completes a full backup of the database. Just curious if this is a reasonable practice? Outside of the obvious with loosing granular recovery is there the potential for corruption? We had an issue where the server BSOD during the maintenance plan and ended up corrupting the the DB beyond what DBCC was able to fix without data lost. We are still trying to pinpoint the cause of the BSOD but know that this sql maintenance was running at the time.
IMHO, that's not a good way to do that. Not only does that process break the log chain, assuming that the database is flipped back after the backup you have to take yet another backup to restart that log chain. If point in time recovery is critical to your recovery strategy, I'd stop doing that immediately. Backups are a page by page copy process so I've never seen corruption occur simply because of a backup. Corruption will usually occur when a process is writing something to a page and then an error occurs (IE: the disk disappears) thus leaving the page & database in an inconsistent state. I would suspect that something other than the MP caused the BSOD and the corruption is the result of the BSOD not the MP. I suppose that it would be possible if the process was in the middle of altering the database recovery model to SIMPLE and the BSOD occurred, that might be a cause of corruption. However, I think we'd need to see the output of a CHECKDB run to further determine where the corruption lies. Personally, I'd trash their maintenance plan and roll with something like Ola Hallengren's solution.
https://ola.hallengren.com/ It's very stable and widely used. Hope that helps!
No definitely I agree with Ola. We use those plans everywhere. The EHR Vendor just says that is part of the way they do it. I dont agree with it which is why I posed the question here. They do flip the mode, then backup the server and flip it back to full. They create log backups every hour and repeat process the next night. It just seems like a poor practice. Have to see if I can run another DBCC against the corrupted DB. At this point we went ahead and restored from that night with our Veeam Backups. Still trying to get more info on the BSOD but unfortuantely it was only a mini dump not full kernel dump so the results are inconclusive as of now.
Wait. They backup the server itself or do you mean the database? If they don't create a full backup after flipping it back to the FULL recovery model, those log backups should be failing as the log chain hasn't been set yet, which in turn your log file is just going to grow as the log backup won't be running. You might want to look into that.
18 People are following this question.
