Set Development Databases to Simple Recovery

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