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!