Here’s some quick logic to change the recovery models of all databases (excluding system databases). This question comes up quite a bit and there are a number of ways to automate so thought I would throw it out there for anyone that might be interesting in doing it.
use master; set nocount on select name, recovery_model_desc from sys.databases where database_id > 4 order by name asc -- check recovery model for all databases. declare @set_full_recovery_all_databases varchar(max) set @set_full_recovery_all_databases = '' select @set_full_recovery_all_databases = @set_full_recovery_all_databases + 'alter database [' + name + '] set recoverty full;' + char(10) from sys.databases where database_id > 4 and state_desc = 'online' exec (@set_full_recovery_all_databases) -- change all databases to full recovery. select name, recovery_model_desc from sys.databases where database_id > 4 order by name asc -- check recovery model again to confirm.
Categories: SQL Stuff