If you develop SharePoint Solutions and have SharePoint installed in your development environment then this script will come in real handy to you.
This script uses a cursor and sets all non system databases to simple recovery. This will help you in terms of performance as it reduces the number of disk writes by SQL along with that you will not face the problem of SQL logs filling your hard drive.
USE MASTER declare @cmd varchar(2000), @dbname varchar(64), @logfile varchar(128) declare c1 cursor for SELECT d.name, mf.name as logfile FROM sys.master_files mf inner join sys.databases d on mf.database_id = d.database_id where recovery_model_desc <> 'SIMPLE' and d.name not in ('master','model','msdb','tempdb') and mf.type_desc = 'LOG' open c1 fetch next from c1 into @dbname, @logfile While @@fetch_status <> -1 begin select @cmd = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE' exec(@cmd) select @cmd='USE [' + @dbname + '] checkpoint' exec(@cmd) select @cmd='USE [' + @dbname + '] DBCC SHRINKFILE ([' + @logfile + '], 1)' exec(@cmd) fetch next from c1 into @dbname, @logfile end close c1 deallocate c1
You can also download the script here: SETDBSIMPLE