i logged into a client's server today(which had log shipping for all of its db's), and found that someone changed all the databases recovery models to simple. so now i need to reconfigure the log shipping. my client says he didn't touch the server in the error log i can see it was changed. can it be some kind of a bug? the server wasn't restarted im lost... can the recovery model be changed by itself or by an application? thanks
Recovery model is changed by a specific ALTER DATABASE statement, and as far as I've seen, there's never been a bug that would change it.
Some common sources of that statement include:
- Backup software - some 3rd party backup software will put the database in simple recovery model, especially virtualization backup tools that just do snapshot backups rather than log backups.
- Data warehouse load scripts - I've seen ETL processes that would flip the database into simple recovery model to eliminate the need for transaction log backups. (Not saying that's a good idea, just saying that I've seen it.)
- Log space reduction scripts - sometimes folks try to shrink their transaction logs, and run into difficulty, so they flip the database into simple and then flip it back.
so yeah it turnes out my client's system admin thought he could save some space if he wont backup the transaction log and didnt think he should have asked anyone beforehand..... so no magic recovery model settings... thanks everyone