DBA - How To Change Recovery Mode To Simple For All Databases on SQL Server

Scenario: 

This can be scenario on Development machine where you have given permissions to developers to work on different databases and you really don't need to take the transaction log backups for these databases. By setting the Recovery mode to Simple for all the databases on Development machine can save you some space (Storage) as you don't have Backup jobs set up for Transaction log backups.

Solution:

The below Code can be used to Set the Recover Mode to Simple for all the databases for which it is not set to Simple recovery mode already. You can change below script if you want to change Recovery mode to Full as well by simply changing <>'FULL' and SET RECOVERY FULL.

USE MASTER
GO
DECLARE @DBName VARCHAR(200)

DECLARE DB_Cursor CURSOR FOR
  SELECT name
  FROM   sys.databases
  WHERE  name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
      IF ( (SELECT recovery_model_desc
            FROM   sys.databases
            WHERE  name = @DBName) <> 'SIMPLE' )
        BEGIN
            DECLARE @SQL NVARCHAR(500)

            SET @SQL='ALTER DATABASE [' + @DBName
                     + '] SET RECOVERY SIMPLE'

            PRINT @SQL

            EXEC ( @SQL)
        END

      FETCH NEXT FROM DB_Cursor INTO @DBName
  END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor