Change the recovery model of all the databases

If you ever find yourself in a situation where you have to edit the recovery model for multiple databases, here is a script to do that easily. The below script detects all the dbs that are in ‘simple’ recovery model and alters that setting to ‘full’ recovery model. You can modify the script to ‘bulk logged’ recovery model or alter the order of recovery models if required.

declare @name sysname,

         @recovery_model_desc nvarchar(120),

         @script nvarchar(500),

         @full_backup_count int

 

 select @full_backup_count = count(*) from sys.databases where recovery_model_desc = 'simple'

 print 'There are ' + cast(@full_backup_count as varchar) + ' with FULL backup set'

 

 declare db cursor

     for select name, recovery_model_desc from sys.databases where recovery_model_desc = 'simple'and  database_id>4

     for read only

 

 open db

 fetch next from db into @name, @recovery_model_desc

 while @@fetch_status = 0

 begin

     set @script = 'alter database [' + @name + '] set recovery FULL'

     exec sp_executesql @script

     print 'Done with ' + @name

     fetch next from db into @name, @recovery_model_desc

 end

 

 close db

 deallocate db