SQL Recovery Mode Adjustment

Often when new Development boxes are handed over to my group, the databases are set to Recovery Mode = FULL because the setting match production recovery modes. Unfortunately, since the Dev boxes rarely have any backups running, eventually the transaction logs fill up the drive. When that happens, the databases can no longer accept new transactions and we are left with a (temporarily) non-functional box.


Run this script on a Dev box to set the recovery to SIMPLE for all databases to avoid the above scenario. It works on SQL 2000, 2005 and 2008 SQL servers.


NOTE: it is recommended that PRODUCTION servers use FULL recovery mode rather than SIMPLE.


use master

go


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE DatabaseCursor CURSOR FOR


SELECT name FROM master.dbo.sysdatabases
where Name not in ('tempdb') -- cannot set recovery for Tempdb.
ORDER BY 1


OPEN DatabaseCursor


FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN


     set @cmd = 'ALTER DATABASE ' + @database +'
     SET RECOVERY SIMPLE'


     EXECUTE sp_executesql @statement=@cmd


     FETCH NEXT FROM DatabaseCursor INTO @Database


END


CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


For your homework, you can substitute a WHILE loop for the cursor.  
If your environment is running mostly SQL 2008 (or higher), please check out SQLChicken's article on setting up Policy Based management to handle ensuring that the dev boxes are all set to Simple Recovery Mode.