How to Change Recovery Model of Databases to Bulk_Logged in SQL Server - SQL Server Scripts

Scenario:

You are working as SQL Server DBA or developer, You need to write script that should change the database recovery model to Bulk-Logged for selected databases.

Below script can be used to change the recovery model for database or databases to Bulk-Logged. you can change the select query to include or exclude the database for which you want to change the recovery model to Bulk-Logged.

USE master 
go 
DECLARE @DBName VARCHAR(200) 
DECLARE db_cursor CURSOR FOR 
  --Make changes to Query to Include or Exclude database 
  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) <> 'Bulk_Logged' ) 
        BEGIN 
            DECLARE @SQL NVARCHAR(500) 

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

            PRINT @SQL 

            EXEC ( @SQL) 
        END 

      FETCH next FROM db_cursor INTO @DBName 
  END 

CLOSE db_cursor 

DEALLOCATE db_cursor 

--Check recovery model for all databases 
SELECT NAME AS DBName, 
       recovery_model_desc 
FROM   sys.databases