How to set Database Recover Mode to Full for all databases?

Using a command similar to:

ALTER DATABASE test0402er2Y8 SET RECOVERY FULL
GO

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Thanks

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Below T-SQL will help you. It will check for ONLINE databases with SIMPLE recovery model and will print TSQL to change it into FULL Recovery mode. Run below code in TEXT Mode -- SSMS CTRL+T. Once you are happy, run the output generated from another session :

set nocount on
go

if exists (
        select 1
        from sys.databases
        where recovery_model_desc = 'SIMPLE'
            and state_desc = 'ONLINE'
        )
begin
    print '-- You are setting up database to FULL recovery mode. '
    print '-- Make sure you take first full backup and then schedule LOG BACKUPS for proper transaction log maintenance !'

    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET RECOVERY FULL with ROLLBACK IMMEDIATE;'
    from sys.databases
    where recovery_model_desc = 'SIMPLE' -- since you only want SIMPLE recovery model databases to get changed to FULL recovery.
            and state_desc = 'ONLINE'
end
else
    select 'ALL the databases are in FULL RECOVERY MODE - Make sure you take proper LOG BACKUPS !!'

NOTE : For any databases that are newly created, its better to change the recovery model of model database to FULL, so that the new databases that are created inherit the FULL recovery setting from model database.

Worth mentioning : Why Does the Transaction Log Keep Growing or Run Out of Space?, since you are going to switch to FULL recovery and if you dont take care of your transaction log, then you will end up with a bloated transaction log. Read the answer linked for more details.

StackExchange Database Administrators Q#116397, answer score: 9