Setting All Databases to SIMPLE Recovery Mode

Leave a comment

November 7, 2011 by Mike Hillwig

I’m cleaning up some stuff in my dev environment today, and I have some pretty big transaction log files. These have gotten big even though I do regular full and transaction log backups. In order to do some maintenance work, I wrote this little nugget this morning. It’s anther script that generates a script.

Again, this is for my DEV environment. I’d never advise someone to run all databases in SIMPLE recovery mode in a production environment unless there was a very specific need to do that.

set nocount on
go
select 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE'
from sys.databases
where database_id > 6
and recovery_model_desc = 'FULL'

Leave a Reply

Your email address will not be published. Required fields are marked *